Queries on Tablespace Management

 Query to check tablespaces filled more than 80%:

SQL> col name for a25
SQL> select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
  2     round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
  3                 (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
  4                 where tablespace_name like '%%' group by tablespace_name) a,
  5                 (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
  6                 where tablespace_name like '%%' group by tablespace_name) b
  7     Where a.tbl=b.tblsp and round((1-(b.fsz/a.tsz))*100)>80;

Name                      Total Size Free Space   Pct Used   Pct Free
------------------------- ---------- ---------- ---------- ----------
SYSAUX                          1300   140.8125         89         11
SYSTEM                           810     9.3125         99          1

Query to check the space usage of a tablespace:

SQL> col name for a25
SQL> select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
  2     round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
  3                 (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
  4                 where tablespace_name like '%SYSTEM%' group by tablespace_name) a,
  5                 (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
  6                 where tablespace_name like '%SYSTEM%' group by tablespace_name) b
   Where a.tbl=b.tblsp;  7

Name                      Total Size Free Space   Pct Used   Pct Free
------------------------- ---------- ---------- ---------- ----------
SYSTEM                           810     9.3125         99          1



Query to check the datafile assigned to a tablespace:

SQL> col file_name for a60
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name ='SYSTEM';
SQL>   2
FILE_NAME                                                    BYTES/1024/1024
------------------------------------------------------------ ---------------
+DATA/RACDB/DATAFILE/system.278.934916281                                810

alter tablespace: Backups

ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;
alter tablespace: Data Files and Tempfiles

ALTER TABLESPACE mytbs
ADD DATAFILE '/ora100/oracle/mydb/mydb_mytbs_01.dbf' SIZE 100M;

12 Portable DBA: Oracle
ALTER TABLESPACE mytemp
ADD TEMPFILE '/ora100/oracle/mydb/mydb_mytemp_01.dbf'
SIZE 100M;
ALTER TABLESPACE mytemp AUTOEXTEND OFF;
ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;

alter tablespace: Rename
ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;

alter tablespace: Tablespace Management
ALTER TABLESPACE my_data_tbs DEFAULT
STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);
ALTER TABLESPACE my_data_tbs MINIMUM EXTENT 500k;
ALTER TABLESPACE my_data_tbs RESIZE 100m;
ALTER TABLESPACE my_data_tbs COALESCE;
ALTER TABLESPACE my_data_tbs OFFLINE;
ALTER TABLESPACE my_data_tbs ONLINE;
ALTER TABLESPACE mytbs READ ONLY;
ALTER TABLESPACE mytbs READ WRITE;
ALTER TABLESPACE mytbs FORCE LOGGING;
ALTER TABLESPACE mytbs NOLOGGING;
ALTER TABLESPACE mytbs FLASHBACK ON;
ALTER TABLESPACE mytbs FLASHBACK OFF;
ALTER TABLESPACE mytbs RETENTION GUARANTEE;
ALTER TABLESPACE mytbs RETENTION NOGUARANTEE;

Shrinking Datafiles:

show parameter block

SELECT FILE_NAME,
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) and  DBADF.FILE_NAME LIKE '/prod/u0%'