DBA Tips for Oracle Tablespaces!!

Page: 1 2

Author: Kart Robinson

A tablespace basically is a logical storage unit within an Oracle database. Renaming tablespaces and moving a table to a different tablespace is amongst most widely accomplished tasks by Oracle DBAs but we stuck in some errors while doing such common tasks. Today we will give some effective tips to rename your tablespace or move your table to a new tablespace.

Renaming Tablespaces:

Oracle releases prior to 10g required a tedious way to rename the tablespaces. However Oracle 10g has made the life of database administrators easy by introducing a new feature of renaming tablespace. 

Check Tablespace Existence:

First of all we will run below command to check for the existing tablespace name in the database. In order to rename a tablespace your database compatibility parameter COMPATIBLE in init.ora should be set to at least 10.0.0.0 and the tablespace and its data files should be online.

SELECT TABLESPACE_NAME FROM USER_TABLESPACES WHERE TABLESPACE_NAME = ‘MYTBS';

TABLESPACE_NAME
——————————
MYTBS

Rename Tablespace:

Now we will run below command to rename our tablespace.

ALTER TABLESPACE MYTBS RENAME TO MYTBS2;

Remember you can rename the temporary tablespace but you can not rename the tablespace for SYSTEM and SYSAUX tablespaces. When you rename your tablespace then the tablespace is changed for the objects which belong to older tablespace. For example database updates the references in data dictionary, control file and online data file headers so that they all reference to the new name of tablespace. You can query the USER_TABLES table so as to determine if the tablespace name has been changed for the objects which belonged to the older tablespace.

Moving Table to a Different Tablespace:

In Oracle you can move a table to a different tablespace by either performing export/import or by using ALTER TABLE command with MOVE TABLESPACE clause.

Create Table:

First of all we will create a test table which we want to move to a different tablespace.

CREATE TABLE TEST_TABLE
(
MYID NUMBER(9) NOT NULL,
MYDESC VARCHAR(10),
CONSTRAINT PK_TEST_TABLE PRIMARY KEY(MYID)
)
/

CREATE INDEX MYINDEX ON TEST_TABLE (MYDESC)
/

Check Object's Tablespace:

The default tablespace in our tablespace is MYUSERS and our test table and its index will be created there. Below command helps us determine that our objects are created in MYUSERS tablespace and in valid status.

SELECT TABLE_NAME,TABLESPACE_NAME,STATUS FROM USER_TABLES WHERE TABLE_NAME = ‘TEST_TABLE'
UNION ALL
SELECT INDEX_NAME,TABLESPACE_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME = ‘TEST_TABLE'
/

TABLE_NAME TABLESPACE_NAME STATUS
————————— —————————— ———–
TEST_TABLE MYUSERS VALID
MYINDEX MYUSERS VALID
PK_TEST_TABLE MYUSERS VALID

Insert Data:

Now we will insert one record into our table.

INSERT INTO TEST_TABLE VALUES(1,'MyValue');
COMMIT; Continued...

Page: 1 2

 More Database Articles
   Database Security: Step by step guideline
   Effective Tips for Fixing Online Redo Log Corruption!!
   The Power of Oracle 11g Sequence Enhancements!
   What if your database lock gets blocked??
   Oracle Server Security, Important Concerns!!