Tablespace Management.
Tablespace and datafile related dba tables
dba_tablespace
dba_data_files
dba_temp_files
Tablepace Queries.
To Create New Tablespace.
create tablespace tablespace_name datafile ‘location/tablespace_name.dbf’ size 2g;
Add new datafile to the tablespace.
Alter tablespace add datafile ‘location/tablespace_name01.dbf’ size 1g autoextend on maxsize 31G;
Resize the tablespace.
Alter database datafile ‘location/tablespace_name01.dbf’ resize 2g;
Query to view datafiles for specific tablespace.
select datafile , size/1024/1024/1024 “in GB” from dba_data_files where tablespace_name =’TABLESPACE_NAME’;
UNDO Tablespace.
UNDO TABLESPACE Usage details script
select tablespace_name , status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2 ;
SHRINK THE TEMP TABLESIZE.
1.Check the free space
SELECT * FROM dba_temp_free_space;
2.Resize the temp tablespace using shrink clause.
ALTER TABLESPACE temp SHRINK SPACE;
ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;
3.Resize temp tablespace using tempfile clause.
ALTER TABLESPACE temp SHRINK TEMPFILE ‘/location/temp01.dbf’ KEEP 30M;
Recent Posts
