Tablespace management related commands

Tablespace is a logical structure in database.Tablespace is the grouping of one or more datafiles which are physical structures that store database data.
Create tablespace:
SQL>create tablespace tsname
datafile ‘/pathofdatafile’ size datafile-size
extent management local — default.other value is dictionary
autoallocate size — other value is uniform
segment space management auto; — default is auto.Other value is manual
To check if the datafile has been created successfully use :
SQL>select extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name=’tsname’;
Remove tablespace :
SQL>drop tablespace tsname;
If the tablespace has objects like table,index when we issue the above command we get error.So use:
sql>drop tablespace tsname including contents;
To physically remove the datafile as well use:
sql>drop tablespace tsname including contents and datafiles;
To drop referential integrity constraints use:
sql>drop tablespace tsname cascade constraints;
Alter the tablespace size – increase and decrease.If tablespace is already occupied it is not possible to decrease the size of the tablespace.
sql>alter tablespace tsname add datafile ‘/datfile-path’ size datafile-size;
To change the size of a datafile:
sql>alter database datafile ‘/datafilepath’ resize new-datafilesize

Important Data Dictionary Views And Their Uses is given below :

dba_catalog – shows names and owners of all tables, indexes, views, synonyms, sequences and clusters in a database
dba_objects – shows all objects in the database, including their creation time and when they were last altered
dba_tablespaces – shows all tablespaces and provides information on tablesapce attributes like type of tablespace management, space allocation, segment space management
dba_tables – shows all tables, their owners, tablespace they belong to. We can find information like last time table was analyzed, average row length, number of rows in a table
dba_indexes – shows all indexes and tables on which they are defined
dba_part_tables – shows information about all partitioned tables, including tablenames, partitioning, subpartitioning types
dba_synonyms – shows all synonyms,table names, owners of the tables on which synonym was degined
dba_triggers – shows all triggers and tells the triggering events that set off the trigger.TRIGGEr_BODY column contains the actual trigger definition
dba_sequences – shows all sequences and includes the maximum , minimum and last value of a sequence
dba_constraints – shows all constraints and constraint types
dba_cons_columns – shows all the constraints in the database, the column in which they are defined at
dba_tab_columns – provides detailed information on every column in the table. Gives information on average column length, density of column, time it was last analyzed.