Oracle Study Note : Tablespace and Data Files

1 create tablespace tb_name  #create bigfile tablespace tb_name
2         datafile ‘/u01/dbfile/orcl/tb_name.dbf’
3         size 100m
4         autoextend on maxsize 1000m  #don’t recommend use the AUTOEXTEND feature. if use this feature,suggest always specify a corresponding MAXSIZE.
5 extent management local  #A locally managed tablespace uses a bitmap in the data file to efficiently determine whether an extent is in use
6         uniform size 128k  #instruct Oracle to allocate size for each extent via the UNIFORM SIZE [size] clause.the default uniform extent size is 1MB
7         segment space management auto;  #the SEGMENT SPACE MANAGEMENT AUTO clause instructs Oracle to manage the space within the block.
8         nologging;  #you can turn off the generation of redo for direct path loading.
9         default row store compress advanced;

1 SQL> alter tablespace old_name rename to new_name

1 SQL> alter tablespace tb_name nologging;

1 SQL> select tablespace_name,logging from dba_tablespaces;

1 SQL> alter tablespace tb_name read only; #you can’t make a tablespace that contains active rollback segment read-only.
2 SQL> alter tablespace tb_name read write;
3 SQL> alter table table_name read only; #modify individual tables

1 SQL> alter tablespace tb_name offline; #before droping a tablespace,it’s a good practices to first it offline.
2 SQL> drop tablespace tb_name including contents and datafiles cascade constraints;

1 SQL> alter system set db_create_file_dest=’/u01’;

1 SQL> alter tablespace tb_name default row store compress advanced;
2 SQL> alter tablespace tb_name defaule compress [basic | nocompres]

1 $ oerr ora 01653

1 SQL> alter database datafile ‘/u01/user01.dbf’ resize 1g;
2 SQL> alter tablespace users add datafile ‘/u02/users02.dbf’ size 100m;
3 SQL> alter tablespace big_data resize 1T;  #with bigfile tablespace,you have the option of using the ALTER TABLESPACE statement to resize the data file. this works because only one data file can be associated with a bigfile tablespace.

1 SQL> select name,bytes from v$tempfile;  #first query the V$TEMPFILE view to verify the current size and location of temporary data files.
2 SQL> alter database tempfile ‘u01/temp01.dbf’ resize 500m;
3 SQL> alter tablespace temp add tempfile ‘u01/temp02.bdf’ size 5000m;

1 SQL> alter tablespace tb_name offline;
2 SQL> alter database datafile 4 offline for drop  #if your database isn’t in archivelog mode,you must specify ALTER DATAFILE ... OFFLINE FOR DROP when taking a data file offline.When you use the OFFLINE FOR DROP clause, no checkpiont is taken on the data file, this means you need to perform media recovery on the data file before bringing it online.
3 SQL> recovery datafile 4;

1 SQL> alter database move datafile ‘u01/user01.dbf’ to ‘/u01/user02.dbf’;
2 SQL> alter database move datafile 4 to ‘u02/user01.dbf’ keep;  #to keep a copy of the original file.
3 SQL> alter database move datafile 4 to ‘/u01/user02.dbf’ reuse;  #specify the REUSE clause to overwrite an existing file.