Managing Tablespaces and Data files

Database storage hierarchy

 

 

Creating tablespaces

  <>

   create tablespace tablespace

         [datafile clause]

         [extent_management_clause]

         [minimum extent integer [k|m]]

         [blocksize integer [k]]

         [logging|nologging]

         [default storage_clause]

         [online|offline]

         [permanent|temporary]

 

         datafile_clause:== filename [size integer [k|m] [reuse] | reuse ]

[autoextend_clause]

 

         extent_management_clause:==[extent management [dictionary | local [autoallocate | uniform [size integer [k|m]]]]]

 

 

 

  alter tablespace tablespace_name {online | offline [normal | temporary | immediate | for recovery ]}

 

  alter tablespace tablespace_name read {only | write } 

 

  alter database rename file 'A' to 'B'

 

  alter tablespace tablespace-name      <--仅用于extent management dictionary

       [minimum extent integer [k|m]]

       [default storage_clause]

 

 

 <>

  create temporary tablespace tablespace_name

            [tempfile clause]         <--与datafile_clause类似

            [extent management clause]

 

  eg.

   

create temporary tablespace temp02

           tempfile 'd:\oracle9i\oradata\temp02.dbf' size 5m reuse

           extent management local;

 

alter database defualt temporary tablespace temp02;

 

drop tablespace temp including contents and datafiles;

 

Dropping tablespaces

 

  drop tablespace tablespace_name [including contents [and datafiles]]

 

Resizing tablespaces

  方法1:

      alter databse datafile/TEMPFILE '$ORACLE_HOME/ORADATA/u01/example.dbf' resize 100m;

 

  方法2:

      alter database datafile/TEMPFILE  '$ORACLE_HOME/ORADATA/u01/example.dbf' autoextend on next maxsize 100m;

 

  方法3:

      alter tablespace example

          add datafile/TEMPFILE  '$ORACLE_HOME/ORADATA/u01/example02.dbf' autoextend on next maxsize 50M;

 

  说明:以上方法同样适合tempfile文件类型

 

 

Moving data files: ALTER TABLESPACE /ALTER DATABASE

 

  alter tablespace tablespace_name

          rename datafile  'A'

                       to  'B';

  说明:tablespace 必须offline;目标文件必须存在;

 

  alter database 

              rename file 'A'

                       to 'B';

 

  说明:database状态必须是mount;目标文件必须存在;同样实用tempfile/logfile文件;件;