Tablespace datafiles
SET LINESIZE 147 SET PAGESIZE 9999 SET VERIFY OFF COLUMN tablespace FORMAT a29 HEADING 'Tablespace Name' COLUMN filename FORMAT a64 HEADING 'Filename' COLUMN filesize FORMAT 9,999,999,999,999 HEADING 'File Size' COLUMN autoextensible FORMAT a4 HEADING 'Auto' COLUMN increment_by FORMAT 999,999,999 HEADING 'Next' COLUMN maxbytes FORMAT 99,999,999,999 HEADING 'Max' BREAK ON report COMPUTE SUM OF filesize ON report SELECT /*+ ordered */ d.tablespace_name tablespace , d.file_name filename , d.bytes filesize , d.autoextensible autoextensible , d.increment_by * e.value increment_by , d.maxbytes maxbytes FROM sys.dba_data_files d , v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e WHERE (d.file_name = v.name) and d.tablespace_name = upper('&TABLESPACE_NAME') /
Tablespace usage
SET LINESIZE 141 SET PAGESIZE 9999 SET VERIFY OFF COLUMN status FORMAT a9 HEADING 'Status' COLUMN name FORMAT a22 HEADING 'Tablespace Name' COLUMN type FORMAT a12 HEADING 'TS Type' COLUMN extent_mgt FORMAT a10 HEADING 'Ext. Mgt.' COLUMN segment_mgt FORMAT a9 HEADING 'Seg. Mgt.' COLUMN ts_size FORMAT 9,999,999,999,999 HEADING 'Tablespace Size' COLUMN used FORMAT 9,999,999,999,999 HEADING 'Used (in bytes)' COLUMN free FORMAT 9,999,999,999,999 HEADING 'Free (in bytes)' COLUMN pct_used FORMAT 999 HEADING 'Pct. Used' BREAK ON report COMPUTE SUM OF ts_size ON report COMPUTE SUM OF used ON report COMPUTE SUM OF free ON report COMPUTE AVG OF pct_used ON report SELECT d.status status , d.tablespace_name name , d.contents type , d.extent_management extent_mgt , d.segment_space_management segment_mgt , NVL(a.bytes, 0) ts_size , NVL(a.bytes - NVL(f.bytes, 0), 0) used , NVL(f.bytes, 0) free , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used FROM sys.dba_tablespaces d , ( select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) a , ( select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name ) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT ( d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY' ) and a.tablespace_name = upper('&TABLESPACE_NAME') /
Add new datafile to tablespace
alter tablespace TABLESPACE_NAME add datafile 'NEW_DATAFILE_NAME' size 1G autoextend on next 128M maxsize 16G;