Oracle tablespace and Data File Management

Oracle Tablespace and data file management [html] Database model Database tablespace Datafile Segment Extent Oracle Block OS block create Tablespace SQL> create tablespace user_data 2 datafile 'd: \ userdata \ userdata1.dbf 'size 100 m 3; The tablespace www.2cto.com has been created. SQL> select tablespace_name, logging, status from dba_tablespaces; TABLESPACE_NAME logging status --------- system logging online sysaux logging online UNDOTBS1 logging online temp nologging online users logging online example nologging online INDEX_TBS logging online USER_DATA logging online 8 rows have been selected. Create a local tablespace with Extent management local Uniform size 1 m; view the tablespace type SQL> select tablespace_name, contents from dba_tablespaces; TABLESPACE_NAME CONTENTS tables --------- system permanent sysaux permanent UNDOTBS1 undo temp temporary users permanent example permanent INDEX_TBS PERMANENT USER_DATA PERMANENT www.2cto.com 8 rows have been selected. Create temporary tablespace SQL> show parameter temp; name type value created ----------- ------------------------- sec_max_failed_login_attempts integer 10 SQL> create temporary tablespace user_temp 2 tempfile 'd: \ userdata \ user_temp.dbf 'size 20 m 3 extent management local 4 uniform size 1 m 5; tablespace created. Switch tablespace SQL> alter database default temporary tablespace user_temp; the database has been changed. Create a large file tablespace SQL> create bigfile tablespace bigfiletbs 2 datafile 'd: \ userdata \ bfile_tbs01.dbf' 3 size 2g; The tablespace has been created. Run; 1 * select tablespace_name, file_name, bytes/(1024*1024*1024) G from dba_data_fil es www.2cto.com TABLESPACE_NAME FILE_NAME G tablespace sizes----------- users d: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ USERS01.DBF. 004882813 UNDOTBS1 D: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ UNDOTBS01.DBF. 2734375 sysaux d: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ SYSAUX01.DBF. 714477539 system d: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ SYSTEM01.DBF. 673828125 example d: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ EXAMPLE01.DBF. 09765625 INDEX_TBS D: \ INDEX_TBS. 09765625 USER_DATA D: \ USERDATA \ USERDATA1.DBF. 09765625 bigfiletbs d: \ USERDATA \ BFILE_TBS01.DBF 2 eight rows have been selected. Is the tablespace offline online SQL> alter tablespace user_data offline; The tablespace has been changed. SQL> alter tablespace user_data online; The tablespace has been changed. Set the tablespace read-only and writable SQL> alter tablespace user_data read only; the tablespace www.2cto.com has been changed. SQL> alter tablespace user_data read write; The tablespace has been changed. Modify the tablespace size. First, set it to the auto-scaling mode. SQL> create tablespace manager_tbs1 2 datafile 'd: \ userdata \ tbs1.dbf '3 size 100 m 4 autoextend on; the tablespace has been created. Add the data file www.2cto.com SQL> alter tablespace manager_tbs1 2 add datafile 'd: \ userdata \ tbs2.dbf '3 size 20 m; modify the data file size SQL> alter database 2 datafile 'd: \ userdata \ tbs2.dbf 'resize 100 m; the database has been changed. Delete tablespace SQL> drop tablespace manager_tbs1 including contents and datafiles; tablespace deleted. Prepared by szstephen Zhou