Information of ORACLE Database

1. A table can have up to 1,000 columns,Tables are most efficient

with far fewer than 1,000 columns.

2. A table can have a virtually unlimited number of rows. Although you will hit other limits that prevent this from happening. For example, a tablespace can have at most
1,022 files typically. Say you have 32 GB files, that is to say 32,704 GB per tablespace.
This would be 2,143,289,344 blocks, each of which are 16 KB in size. You might be able to fit 160 rows of between 80 to 100 bytes per block. This would give you
342,926,295,040 rows. If we partition the table though, we can easily multiply this by ten times or more. There are limits, but you?ll hit other practical limitations
before even coming close to these figures.
3. A table can have as many indexes as there are permutations of columns, taken 32 at a time (and permutations of functions on those columns), although once again
practical restrictions will limit the actual number of indexes you will create and maintain.
4. There is no limit to the number of tables you may have. Yet again, practical limits will keep this number within reasonable bounds. You will not have millions of
tables (impracticable to create and manage), but thousands of tables, yes.

Create Control File when they are lost

# Creating Controlfiles when all db files & redolog files are available

1. open database in nomount stage
2. then execute following 'create controlfile' as sysdba: (temp tablespace should be crated separately)

CREATE CONTROLFILE
reuse database "db_name"
datafile '$ORACLE_HOME/dbf/system01.dbf',
'$ORACLE_HOME/dbf/tools01.dbf',
'$ORACLE_HOME/dbf/rbs01.dbf',
'$ORACLE_HOME/dbf/indx01.dbf',
'$ORACLE_HOME/dbf/users.dbf',
logfile group 1 ('$ORACLE_HOME/log/redo01a.log','$ORACLE_HOME/log/redo01b.log') SIZE 100M,
group 2 ('$ORACLE_HOME/redo02a.log','$ORACLE_HOME/log/redo02b.log') SIZE 100M,
group 3 ('$ORACLE_HOME/log/redo03a.log','$ORACLE_HOME/log/redo03b.log') SIZE 100M
MAXDATAFILES 100
MAXLOGFILES 32
NORESETLOGS;
3. Temp datafile should be created separately using 'alter database add tempfile …' command

RENAMING A DATABASE

# Rename Database:

1. Change the db_names, global_names to the new DB name in the init parameter file
2. Change ORACLE_SID to new name
3. Create new passwordfile using orapwd utility
4. Now connect as sysdba
5. execute 'create controlfile ' command as shown below :

CREATE CONTROLFILE
SET DATABASE 'new_dbname'
datafile '$systemdbf_filepath/system.dbf' SIZE 500M,
'$toolsdbf_filepath/tools.dbf' SIZE 40M,
'$tempdbf_filepath/temp.dbf' SIZE 10M,
'$rbsdbf_filepath/rbs' SIZE 40M,
'$indxdbf_filepath/indx.dbf' SIZE 30M,
'$usersdbf_filepath/users.dbf' SIZE 30M
logfile group 1 ('$ORACLE_HOME/log/redo01a.log','$ORACLE_HOME2/log/redo01b.log') SIZE 10M,
group 2 ('$ORACLE_HOME/log/redo02a.log','$ORACLE_HOME/log/redo02b.log') SIZE 10M,
group 3 ('$ORACLE_HOME/log/redo03a.log','$ORACLE_HOME/log/redo03b.log') SIZE 10M
MAXDATAFILES 10
MAXLOGFILES 32
NORESETLOGS;

6. Edit the entry of dbname in /var/opt/oracle/oratab (for Solaris - auto start)
7. Edit the name of $ORACLE_SID to the new dbname
8. Update GLOBAL_NAME table with the new DB Name as sys user
Note: Use Noresetlogs command only when you loose all your redologs or when you want to create new redologs replacing old redologs. Using noresetlogs results in minor loss of data