Tablespace & Datafile Management

1)          To create Dictionary managed tablespace (System tablespace must be DICTIONARY managed)

The following creates sales tablespace of dictionary managed of size 10m containing initial    extents 10k and next extents 20k

SQL>create tablespace sales datafile ‘/u01/app/oradata/test/sales01.dbf’ size 10m extent management dictionary default storage(initial 10k next 20k);

2)          To create Locally managed tablespace

The following creates a tablespace named sales of locally managed, size of 10mb and    AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.

SQL>create tablespace sales datafile ‘/u01/app/oradata/test/sales01.dbf’ size 10m extent            management local autoallocate;

The following will create a tablespace named sales of size 10m with uniform 128K extents and    AUTOALLOCATE is of uniform.

SQL>create tablespace sales datafile ‘/u01/app/oradata/test/sales01.dbf’ size 10m extent    management local uniform size 128k;

3)          To resize tablespace

             The following adds sales02 datafile of size 5m to the tablespace named sales.

             SQL>alter tablespace sales add datafile ‘/u01/app/oradata/test/sales02.dbf’ size 5m;

                        The following resizes the sales01 datafile to 20m

                        SQL>alter database datafile ‘/u01/app/oradata/test/sales01.dbf’ resize 20m;

                        The following keeps autoextend on for sales01.dbf from 10mb to maximum of 30mb 

SQL> alter database datafile ‘/u01/app/oradata/test/sales01.dbf’ autoextend on next 10m maxsize   30m;

4)          To rename tablespace

             The following renames tablespace named sales to newsales

             SQL>alter tablespace sales rename to newsales;

5)          To keep tablespace in offline mode

            SQL>alter tablespace newsales offline;

6)          To keep tablespace in online mode

SQL>alter tablespace newsales online;

7)         To keep tablespace in Read only mode

  SQL>alter tablespace newsales read only;

8)         To keep tablespace in Read write mode

  SQL>alter tablespace newsales read write;

9)        To rename and relocate datafiles

The following datafile named sales is renamed to newsales and relocated to u02 from u01 using Alter tablespace

 SQL>alter tablespace newsales offline;

 $mv /u01/app/oradata/test/sales01.dbf /u02/app/oradata/test/newsales01.dbf  

SQL>alter tablespace newsales rename datafile ‘/u01/app/oradata/test/sales01.dbf’ to          ‘/u02/app/oradata/test/newsales01.dbf’;

SQL>alter tablespace newsales online;

The following datafile named sales is renamed to newsales and relocated to u02 from u01 using Alter database

SQL>shutdown

$ mv  ‘/u01/app/oradata/test/sales01.dbf’  ‘/u02/app/oradata/test/newsales01.dbf’ 

SQL>startup mount

SQL>alter database rename file ‘/u01/app/oradata/test/sales01.dbf’ to ‘/u02/app/oradata/test/newsales01.dbf’;

SQL>alter database open;

10)    To drop empty tablespace

  SQL>drop tablespace newsales;

11)    To drop tablespace that containing contents

  SQL>drop tablespace newsales including contents;

12)    To drop tablespace and its associated data files

  SQL>drop tablespace newsales including contents and datafiles;

13)    To create tablespace of non standard block size

        The following creates tablespace named hutch of non standard block size 4k

  SQL>alter system set db_4k_cache_size=20m scope=both;

  SQL>create tablespace hutch datafile ‘/u01/app/oradata/test/hutch01.dbf’ size 10m blocksize 4k;

14)    To create  Big file tablespace

  The following creates tablespace named hr of big in size of 50gb

  SQL>create bigfile tablespace hr datafile ‘/u01/app/oradata/test/hr01.dbf’ size 50g;