Create and Alter Tablespace

Check exitsing tablespaces, their type and status -
====================================================
SQL> select tablespace_name, contents, status from dba_tablespaces;

TABLESPACE_NAME            CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                   PERMANENT ONLINE
SYSAUX                   PERMANENT ONLINE
UNDO_TBS               UNDO     ONLINE
TEMP                   TEMPORARY ONLINE
USERS                   PERMANENT ONLINE

Creating a new tablespace with two datafile of different sizes -
================================================================
SQL> create tablespace demo_tbs
  2  datafile '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_01.dbf' size 10M,
  3  '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_02.dbf' size 5M;

Tablespace created.

SQL> select tablespace_name, contents, status from dba_tablespaces;

TABLESPACE_NAME            CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                   PERMANENT ONLINE
SYSAUX                   PERMANENT ONLINE
UNDO_TBS               UNDO     ONLINE
TEMP                   TEMPORARY ONLINE
USERS                   PERMANENT ONLINE
DEMO_TBS               PERMANENT ONLINE

6 rows selected.

Renaming a tablespace -
=======================
SQL> alter tablespace demo_tbs rename to renamed_tbs;

Tablespace altered.

Renaming the datafiles -
=========================
Renaming datafiles includes two parts: rename on operating system level and renaming on database level. Rename operation on OS level needs physical rename of the datafile. For that we can use rename command on windows and mv command on linux/unix environment.

[oracle@localhost ~]$ cd $ORACLE_BASE/oradata
[oracle@localhost oradata]$ cd TOYDB/df
[oracle@localhost df]$ ls -lrt
total 938340
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:13 users_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:14 temp_01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 25 02:19 undo_tbs_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:19 sysaux_01.dbf
-rw-r-----. 1 oracle oinstall 419438592 Aug 25 02:19 systems_01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Aug 25 02:20 demo_tbs_01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Aug 25 02:20 demo_tbs_02.dbf
[oracle@localhost df]$ mv demo_tbs_01.dbf renamed_tbs_01.dbf
[oracle@localhost df]$ mv demo_tbs_02.dbf renamed_tbs_02.dbf
[oracle@localhost df]$ ls -lrt
total 938340
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:13 users_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:14 temp_01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 25 02:19 undo_tbs_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:19 sysaux_01.dbf
-rw-r-----. 1 oracle oinstall 419438592 Aug 25 02:19 systems_01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Aug 25 02:20 renamed_tbs_01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Aug 25 02:20 renamed_tbs_02.dbf
[oracle@localhost df]$ exit


Renaming the datadile on database level includes alter database command. We need to fire this once for each file. Please note that even though the datafiles are now renamed on OS level, at database level we still hold older names of these datafiles.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/systems_01.dbf
SYSTEM

/u01/app/oracle/oradata/TOYDB/df/sysaux_01.dbf
SYSAUX

/u01/app/oracle/oradata/TOYDB/df/undo_tbs_01.dbf
UNDO_TBS


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/users_01.dbf
USERS

/u01/app/oracle/oradata/TOYDB/df/demo_tbs_01.dbf
RENAMED_TBS

/u01/app/oracle/oradata/TOYDB/df/demo_tbs_02.dbf
RENAMED_TBS


6 rows selected.



After rename done on OS level we have to rename those datafiles on database levels else at runtime whenever any object belonging to these renamed datafiles
is referred, an error will be thrown. To rename datafile take tablespace offline.



SQL> alter database rename file '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_01.dbf' to '/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_02.dbf' to '/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_02.dbf';

Database altered.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/systems_01.dbf
SYSTEM

/u01/app/oracle/oradata/TOYDB/df/sysaux_01.dbf
SYSAUX

/u01/app/oracle/oradata/TOYDB/df/undo_tbs_01.dbf
UNDO_TBS


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/users_01.dbf
USERS

/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_01.dbf
RENAMED_TBS

/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_02.dbf
RENAMED_TBS


6 rows selected.


Taking tablespace online -
==========================

SQL> alter tablespace renamed_tbs online;

Tablespace altered.

SQL> select tablespace_name, contents, status from dba_tablespaces;

TABLESPACE_NAME            CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                   PERMANENT ONLINE
SYSAUX                   PERMANENT ONLINE
UNDO_TBS               UNDO     ONLINE
TEMP                   TEMPORARY ONLINE
USERS                   PERMANENT ONLINE
RENAMED_TBS               PERMANENT ONLINE

6 rows selected.

Creating Database using sql script -

#################################################################
# Setup environment
# Typically configured in .profile (Unix) or .bash_profile (Linux)
#################################################################
export ORACLE_SID=TOYDB
export ORACLE_BASE=/database/oracle
export ORACLE_HOME=/product/oracle/base_dir/version_no
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/LIB:$LD_LIBRARY_PATH

#################################################################
# Parameter File - initTOYDB.ora
# Copy and paste the below lines in seperate file and rename the file with above mentioned name
# initSID.ora - where SID=service identifier (TOYDB) in our case
#################################################################
db_name=TOYDB
compatible=11.2.0
memory_target=400M
control_files='/database/oracle/RMCLY9P1/ctl/CTL_001.ctl','/database/oracle/RMCLY9P1/ctl/CTL_002.ctl'
undo_tablespace=undo_tbs
undo_management=auto

#################################################################
# Once the file is set then startup database to nomount stage using below command
# In nomount stage, the instance is created
#################################################################
startup no_mount pfile='absolute path of the above file'


#################################################################
# Create Database script - 
# Copy and paste the below lines as a seperate sql script
# Make sure before you execute this script, the instance is created
# Without instance created we cant execute this script
# Below script will create database with the name of TOYDB and will create controlfile as specified in parameter file
# Once done with DB creation, this script will take DB to mount stage
#################################################################
create database "TOYDB"
datafile '-- path of system tablespace datafile --' size 400M
sysaux datafile '-- path of sysaux tablespace datafile --' size 200M
default temporary tablespace temp tempfile ' -- path og temp tablespace tempfile -- ' size 400M
default undo tablespace undo_tbs datafile '-- path of undo tablespace datafile --'
default tablespace users datafile '-- path of users datafile --' size 400M
logfile
group 1 ('--path of member1_01.log file --','--path of member1_02.log file --') size 20M,
group 2 ('--path of member2_01.log file --','--path of member2_02.log file --') size 20M,
group 3 ('--path of member3_01.log file --','--path of member3_02.log file --') size 20M
user sys identified by oracle
user system identified by manager;


#################################################################
# Create Data Dictionary views
# Before executing these scripts make sure the DB is in mount stage
# Also these scripts needs to be execute in the same mentioned sequence -
# catalog.sql --> creates required/mandatory data dictionary tables and view
# catproc.sql --> creates required/mandatory PLSQL procedures, functions and packages
#################################################################

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql;
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql;