Managing Oracle Storage

The following query can be used to obtain the space usage in tablespaces

SELECT a.tablespace_name, TO_CHAR (a.total_size) || 'MB' total_space,
       TO_CHAR (b.free_space) || 'MB' free_space,
       TO_CHAR ((a.total_size - b.free_space)) || 'MB' used_space,
       TO_CHAR (total_extensible_size) || 'MB' total_extensible_size
  FROM (SELECT   tablespace_name, ROUND (SUM (BYTES) / 1048576, 0) total_size,
                 ROUND (SUM (maxbytes) / 1048576, 0) total_extensible_size
            FROM (SELECT tablespace_name, BYTES, maxbytes
                    FROM dba_data_files
                  UNION ALL
                  SELECT tablespace_name, BYTES, maxbytes
                    FROM dba_temp_files)
        GROUP BY tablespace_name) a,
       (SELECT   tablespace_name, ROUND (SUM (BYTES) / 1048576, 0) free_space
            FROM dba_free_space
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+);

For some database operations like 
"alter index .. rebuild", "create table as select ...."  it is possible 
to suppress redo generation and  improve performance.
Specify the NOLOGGING clause in the CREATE TABLESPACE statement if you 
wish to suppress redo when these operations are performed for objects within the tablespace.
Redo is never generated for temporary segments or in temporary tablespaces.

NOLOGGING is supported by the following operations

DML
1) Direct-path INSERT (serial or parallel) resulting either from an INSERT or a MERGE statement. 
   NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement.
2) Direct Loader (SQL*Loader)

DDL
1) CREATE TABLE ... AS SELECT
2) CREATE TABLE ... LOB_storage_clause ... LOB_parameters ... NOCACHE | CACHE READS
3) ALTER TABLE ... LOB_storage_clause ... LOB_parameters ... NOCACHE | CACHE READS 
                              (to specify logging of newly created LOB columns)
4) ALTER TABLE ... modify_LOB_storage_clause ... modify_LOB_parameters ... 
                                    (to change logging of existing LOB columns)
5) ALTER TABLE ... MOVE
6) ALTER TABLE ... (all partition operations that involve data movement)
7) ALTER TABLE ... ADD PARTITION (hash partition only)
8) ALTER TABLE ... MERGE PARTITIONS
9) ALTER TABLE ... SPLIT PARTITION
10) ALTER TABLE ... MOVE PARTITION
11) ALTER TABLE ... MODIFY PARTITION ... ADD SUBPARTITION
12) ALTER TABLE ... MODIFY PARTITION ... COALESCE SUBPARTITION
13) CREATE INDEX
14) ALTER INDEX ... REBUILD
15) ALTER INDEX ... REBUILD [SUB]PARTITION
16) ALTER INDEX ... SPLIT PARTITION

Locally managed tablespaces are much more efficiently managed than dictionary-managed tablespaces. 
Creating locally managed tablespaces with uniform extent size is most recommended.

CREATE TABLESPACE tbs_04 DATAFILE 'file_1.f' SIZE 10M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Specifying Segment Space Management for a Tablespace


CREATE TABLESPACE auto_seg_ts DATAFILE 'file_2.f' SIZE 1M
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO;

creating locally managed tablespace with uniform extent size is better than micro managing the extents in each segment. 
One issue is when there are very large as well as small tables in the same tablespace. 
If the unform extent size is small then large tables will take more number of extents. 
Large extent size will result in over allocation of space for small tables. 
So the ideal solution is to group tables based on the size and create seperate tablespaces for different groups. 
Group tables as Very large, Large, Medium, small and very small.
Create seperate tablespace for each group.
Large tables can be placed in one tablespace and small tables can be placed in in another group. 
The follwing rules can be used for grouping tables

Tables of size >1GB 				Extent size 100MB
Tables of size between 1GB and 100MB	 	Extent size 10MB
Tables of size between 100MB and 10MB	 	Extent size 1MB
Tables of size less than 10MB	 		Extent size 256K

You can rename and relocate datafiles of one or more tablespaces using ALTER DATABASE statement with the RENAME FILE clause. The rename option in "ALTER DATABASE ... " statement changes only the pointers to the datafiles, as recorded in the database's control file. The datafile has to be physically renamed using OS commands.

To rename datafiles in multiple tablespaces, follow these steps.

Ensure that the database is mounted but closed. 
Copy the datafiles to be renamed to their new locations and new names, using the operating system. 
Use ALTER DATABASE to rename the file pointers in the database's control file. 

The new files must already exist; this statement does not create the files. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

ALTER DATABASE
    RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
                '/u02/oracle/rbdb1/user3.dbf'
             TO '/u02/oracle/rbdb1/temp01.dbf',
                '/u02/oracle/rbdb1/users03.dbf;

Moving tablespace

Identify the datafile names of interest. The following query of the data dictionary view 
DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:

SELECT FILE_NAME  FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';

/U02/ORACLE/RBDB1/USERS01.DBF
/U02/ORACLE/RBDB1/USERS02.DBF

Take the tablespace containing the datafiles offline, 
             or shut down the database and restart and mount it, leaving it closed. 
Either option closes the datafiles of the tablespace. 
Copy the datafiles to their new locations and rename them using the operating system. 

ALTER TABLESPACE users RENAME DATAFILE
 '/u02/oracle/rbdb1/users01.dbf',  '/u02/oracle/rbdb1/users02.dbf'
TO
'/u03/oracle/rbdb1/users01.dbf', '/u04/oracle/rbdb1/users02.dbf';

There is no SQL statement that specifically drops a datafile. The only means of dropping a datafile is to drop the tablespace that contains the datafile. For example, if you want to remove a datafile from a tablespace, you could do the following:

• Create a new tablespace
• Move the data from the old tablespace to the new one
• Drop the old tablespace

You can manually increase or decrease the size of a datafile using the ALTER DATABASE command. Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.

Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements. In this example, assume that the datafile FILENAME2 has extended up to 250M. However, because its tablespace now stores smaller objects, the datafile can be reduced in size. The following command decreases the size of datafile FILENAME2:

ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;

Beware that you can only decrease the size of the datafile with the space that is free between highest used block of the datafile and the last block of the file. If the tablespace is fragmented, the free spaces between extents cannot be deallocated this way. Check dba_free_space for details.

Now the question is finding the maximum space that can be released from a datafile.
What is the maximum space that can be released if we move a table or index to another tablespace.
The following SQL statements query dba_extents and dba_free_space views to get information related extent usage in datafile