Table space Management

 

ORACLE DBA

DBA SCRIPTS

POSTGRES SCRIPTS

R-STUDIO

Search.....

HOME / DATABASE / TABLESPACE MANAGEMENT IN ORACLE

Tablespace Management In Oracle

7647 views  1 min , 56 sec read  3

What is a tablespace and datafile:

Tablespace is the primary logic structure of the oracle database. It consists of one or more physical datafiles. Datafiles physical stores database data in storage.(DISKS) . So when we create a datafile of 30G in database, it will eat 30G of space from defined storage. All the table or index segment is created in tablespace only.

 Create a NEW tablespace:

 

 

      Create tablespace DATA datafile '/u01/dbaclass/oradata/data01.dbf' size 5G autoextend on next 500M;

 

Here, 500M of extent will be added to the datafile automatically, when the space is required.

For creating a tablespace on ASM disk group:

– With OMF file system:

 

 

Create tablespace DATA datafile '+DATAG' size 5G autoextend on next 500M;

 

--- Else we can give the complete path also:

 

Create tablespace DATA datafile '+DATAG/oradata/datafile/data01.dbf' size 5G autoextend on next 500M;

 

Adding a datafile: 

 

alter tablespace DATA add datafile '/u01/dbaclass/oradata/data02.dbf' size 2G;

 

Resize a datafile: ( To increase or decrease the datafile)

 

alter database datafile '/u01/dbaclass/oradata/data02.dbf' resize 3G;

 

 

Change default tablespace:

Every database will have one default tablespace. If any user is created without mentioning any tablespace_name, then that schema objects will be created under default tablespace. – Get the current default tablespace:

 

SELECT PROPERTY_VALUE

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

 

 

ALTER DATABASE DEFAULT TABLESPACE DATATBS;

 

Rename a tablespace:

 

 

set pagesize 200

set lines 200

col file_name for a57

 

SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;

 

   FILE_ID FILE_NAME     TABLESPACE_NAME

---------- -------------------------------------------------------- ------------------------------

 37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf     TESTING

 

 

--- Rename the tablespace_name from TESTING to PRODUCING;

 

SQL❯ alter tablespace TESTING rename to PRODUCING;

 

Tablespace altered.

 

 

SQL❯ select file_id,file_name,tablespace_name from dba_data_files where file_id=37;

 

   FILE_ID FILE_NAME     TABLESPACE_NAME

---------- -------------------------------------------------------- ------------------------------

 37 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf     PRODUCING

 

NOTE: SYSTEM and SYSAUX tablespace cannot be renamed.

Drop a tablespace:

 

-- Drop a tablespace without removing the physical database files.

 

SQL❯ drop tablespace TESTING;

 

Tablespace dropped.

 

SQL❯ select file_name from dba_data_files where tablespace_name='TESTING';

 

no rows selected

 

 

<i>-- Drop tablespace including the physical datafiles.</i>

 

 

SQL❯ drop tablespace TESTING including contents and datafiles;

 

Tablespace dropped.

 

Make tablespace Read only:

 

alter tablespace DATA2 READ ONLY;

 

alter tablespace DATA2 read write;

 

 Take tablespace offline:

DBAs usually make the tablespace offline, while restoring or recovering any underlying corrupt datafile.

 

-- To make a tablespace offline

 

ALTER TABLESPACE DATATS OFFLINE;

 

 

-- To make it online

 

ALTER TABLESPACE DATATS ONLINE;

 

Create a temporary tablespace

 

CREATE TEMPORARY TABLESPACE TEMP1 '/u01/dbaclass/tempfile/temp01.dbf' size 1G autoextend on next 200M;

 

Adding a tempfile:

 

alter tablespace TEMP1 add tempfile '/u01/dbaclass/tempfile/temp02.dbf' size 1G autoextend on next 200M;

 

Resize tempfile:

 

 

alter database tempfile '/u01/dbaclass/tempfile/temp02.dbf' resize 2G;

 

Drop tempfile:

It will drop and remove the tempfile from physical location.

 

 

 ALTER DATABASE TEMPFILE '/u01/dbaclass/tempfile/temp02.dbf' DROP INCLUDING DATAFILES;

 

 Change default temporary tablespace:

When we create a new user, we assign a default temporary tablespace. If nothing is assigned, then it will use the default temporary tablespace set a database level.

Get the current default temporary tablespace for the database

 

 

SQL❯ SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_VALUE

--------------------------------------------------------------------------------

TEMP

  

-- Set New default temp tablespace

 

 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

  

-- Check the new default temp tablespace

 

SQL❯ SELECT PROPERTY_VALUE FROM  DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_VALUE

--------------------------------------------------------------------------------

TEMP2

 

DATAFILETABLESPACETEMPUNDO

DATABASE