Oracle Database - Managing TablespacesIn an Oracle database, a tablespace is a logical storage unit that stores all of the database's data. Tablespaces are made up of one or more physical structures called datafiles, which conform to the operating system that Oracle is running on SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX TEMP USERS UNDOTBSWhen a new database is created, Oracle automatically creates several tablespaces, including: To find datafiles associated with tablespaces and size : SQL> select tablespace_name,file_id,file_name,bytes/1024/1024 MB from dba_data_files TABLESPACE_NAME FILE_ID FILE_NAME MB ------------------------------ ---------- -------------------------------------------------- ---------- SYSTEM 1 /u01/app/oracle/oradata/dev/system01.dbf 810 SYSAUX 3 /u01/app/oracle/oradata/dev/sysaux01.dbf 530 USERS 7 /u01/app/oracle/oradata/dev/users01.dbf 5 UNDOTBS1 4 /u01/app/oracle/oradata/dev/undotbs01.dbf 65To find temp files associated with a temp tablespace : SQL> select file_name, bytes/1024/1024 MB from dba_temp_files FILE_NAME MB -------------------------------------------------- ---------- /u01/app/oracle/oradata/dev/temp01.dbf 32To create new tablespace : SQL> Create tablespace tesdb datafile '/u01/app/oracle/oradata/dev/tesdb01.dbf' size 50m; Where tesdb is the name of new tablespace /u01/app/oracle/oradata/dev/tesdb01.dbf is the location of the datafile 50m is the size of the datafileAdd Space to Tablespace : There are two ways to add space to a tablespace: Use below command to resize a datafile : SQL> Alter database datafile '/u01/app/oracle/oradata/dev/tesdb01.dbf' resize 100m; (or) SQL>Alter database datafile < file_id> resize 100m;Use below command to add new datafile to tablespace : SQL> Alter tablespace tesdb add datafile '/u01/app/oracle/oradata/dev/tesdb02.dbf' size 50m;Drop Tablespace : Below command will drop tablespace including all its contents and associated datafiles SQL> drop tablespace tesdb including contents and datafiles;To check the size of the database : SQL> select sum(bytes/1024/1024) MB from dba_data_files; MB ---------- 1410To check each tablespace size : SQL> select tablespace_name,sum(bytes/1024/1024) MB from dba_data_files group by tablespace_name; TABLESPACE_NAME MB ------------------------------ ---------- SYSAUX 530 UNDOTBS1 65 USERS 5 SYSTEM 810 TESDB 150To check free space in each tablespace : SQL> select tablespace_name,sum(bytes/1024/1024) MB from dba_free_space group by tablespace_name; TABLESPACE_NAME MB ------------------------------ ---------- SYSAUX 48.25 UNDOTBS1 29.375 USERS 3 SYSTEM 2.625To check database free space : SQL> select sum(bytes/1024/1024) MB from dba_free_space; MB ---------- 83.25To check the total used or allocated space in tablespace : SQL> select sum(bytes/1024/1024) MB from dba_segments; MB ---------- 1322.75To change tablespace into offline mode : SQL>alter tablespace tesdb offline;To change tablespace into online : SQL>alter tablespace tesdb online;Undo tablespace- (read consistency) : An undo tablespace is a logical database structure that stores information used to roll back changes to the database. Oracle saves undo data until the transaction has been committed. SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/dev/undo_01.dbf' size 500m;To change the old undo into new undo : SQL> alter system set undo_tablespace=undotbs1 scope=spfile;To drop old undo tablespace : SQL>drop tablespace undotbs including content and datafiles;Advantages Of Temporary Tablespace :
Step 1 : Creating the temp tablespace Create temporary tablespace temptbs3 tempfile '/u01/app/oracle/oradata/TES/temp03.dbf' size 100m; Tablespace created. Step 2 : Checking the newly created temp tablespace select file_id,tablespace_name,bytes/1024/1024 "Size" from dba_temp_files; FILE_ID TABLESPACE_NAME Size -------- ------------------- ------- 1 TEMP 32 2 TEMPTBS 50 3 TEMPTBS3 100Step 3 : Set the newly created temp tablespace as default temporary table space for the database SQL> alter database default temporary tablespace temptbs3; Database altered. SQL> select * from database_properties where property_name= 'DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE DESCRIPTION ------------ -------------- ------------ DEFAULT_TEMP_TABLESPACE TEMPTBS3 Name of default temporary tablespaceStep 4 : Make the temp tablespace to autoextend based on data alter database tempfile '/u01/app/oracle/oradata/TES/temp03.dbf' autoextend on; Database altered. select file_name, autoextensible from dba_temp_files; FILE_NAME AUT ------------------------------------- --- /u01/app/oracle/oradata/TES/temp01.dbf YES /u01/app/oracle/oradata/TES/temp02.dbf YES /u01/app/oracle/oradata/TES/temp03.dbf YESStep 5 : Resize the temp tablespace SQL> alter database tempfile 2 resize 80m; Database altered. SQL> select file_id,tablespace_name,bytes/1024/1024 "Size" from dba_temp_files FILE_ID TABLESPACE_NAME Size ---------- ------------------------------ ---------- 1 TEMP 32 2 TEMPTBS 80 3 TEMPTBS3 100Step 6 : Setting the tablespaceto offline SQL> alter database tempfile '/u01/app/oracle/oradata/TES/temp03.dbf' offline; Database altered. SQL> alter database rename file '/u01/app/oracle/oradata/TES/temp03.dbf' to '/u01/app/oracle/oradata/TES/temp04.dbf'; Database altered.Step 7 : Setting the tablespaceto online SQL> alter database tempfile '/u01/app/oracle/oradata/TES/temp03.dbf' online; SQL> select file_name, autoextensible from dba_temp_files; FILE_NAME AUT --------------------------------------- --- /u01/app/oracle/oradata/TES/temp01.dbf YES /u01/app/oracle/oradata/TES/temp02.dbf YES /u01/app/oracle/oradata/TES/temp04.dbf SQL> select file_id, tablespace_name, status from dba_temp_files; FILE_ID TABLESPACE_NAME STATUS ---------- --------------------- ------- 1 TEMP ONLINE 2 TEMPTBS ONLINEStep 8 : Drop the temp tablespace SQL> alter database tempfile 2 drop; SQL> select file_id, tablespace_name, status from dba_temp_files; FILE_ID TABLESPACE_NAME STATUS -------- ------------------ ------- 1 TEMP ONLINE « Previous Next Topic » ( Oracle Database - Space & Storage Parameters) |