Calculate all Datafile Size Individually

Whenever you say the size of the database it means the summation of the datafiles. An oracle database consists of data files, redo logfiles, control files, temporary files. Database's size comes actually from these datafiles. To know about the size of all datafiles:

SQL>select sum(bytes)/1024/1024 from dba_data_files;

Size of all Temp Files:
SQL>select sum(bytes)/1024/1024 "Meg" from dba_temp_files;

Size of the on-line redo-logs:
SQL>select sum(bytes)/1024/1024 "Meg" from v$log;

Size of the control files:
SQL>select sum(BLOCK_SIZE*FILE_SIZE_BLOCK/1024/1024) "Meg" from v$controlfile;

To get the total size of the database:
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;

Read More

TEMPORARY Tablespace And TEMPFILE

TEMPORARY TABLESPACE

 

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables.A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts.If you join two large tables and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT etc.
Temporary tablespaces are used to store the following:

• Intermediate sort results
• Temporary tables and temporary indexes
• Temporary LOBs
• Temporary B-trees

By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement. You can assign a temporary tablespace to each database user with the CREATE USER or ALTER USER statement.

SQL>CREATE USER NITIN DEFAULT TABLESPACE tbs TEMPORARY TABLESPACE temp;
SQL>ALTER USER NITIN TEMPORARY TABLESPACE TEMP;

Note that a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up:

TEMPFILE

Unlike normal data files, TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, or configure TEMPFILEs to be local instead of shared in a RAC environment.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example:

SQL>ALTER TABLESPACE temp ADD TEMPFILE '/opt/nitin/temp.dbf' SIZE 100M;

Read More