Creating, Dropping and Altering Tablespace using SQL Commands in Oracle

You can create Tablespace using this SQL Command.


create tablespace table_space_name
datafile '/app/oracle/product/11.2.0/oradata/practiceab/table_space_test.dbf' size 100k
autoextend on next 10m maxsize 300m;


To drop any Tablespace in Oracle use this following query.


DROP TABLESPACE table_space_name
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;


And use the following one to alter Tablespace's size.


ALTER DATABASE table_space_name '/app/oracle/product/11.2.0/oradata/practiceab/table_space_test.dbf'
RESIZE mega_bytes_integer M;


To retrieve details of any Tablespace, use this query.


Select *
from dba_data_files

Thanks
A Rahim Khan

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: