Oracle table Space Maintenance common commands

---view tablespace name and file location: Select Tablespace_name, file_id, File_name,round (bytes/(1024*1024), 0) Total_space from Dba_data_ Files ORDER BY Tablespace_name

---How to see if the Oracle Tablespace is automatically expanded (increment_by is the block number "8192byte/block") Select file_name,tablespace_name,bytes/1024/1024 "Bytes MB", autoextensible,increment_by,maxbytes/1024/1024 "MaxBytes MB" from Dba_data_files where Tablespace_name= ' HSML;

Select file_name,autoextensible,increment_by from Dba_data_files;

---View the size of the Oracle database table space (idle, used), whether to increase the Tablespace data file Select a.tablespace_name,a.bytes/1024/1024 "Sum MB", (a.bytes-b.bytes)/ 1024/1024 "used MB", b.bytes/1024/1024 "free MB", round (((a.bytes-b.bytes)/a.bytes) *100,2) ' percent_used ' from (select Tablespace_name,sum (bytes) bytes from Dba_data_files Group by Tablespace_name) A, (select Tablespace_name,sum (bytes) Bytes,max (bytes) largest from Dba_free_space Group by Tablespace_name) b where a.tablespace_name=b.tablespace_name order By ((a.bytes-b.bytes)/a.bytes) desc

---View data file Usage Select B.file_name physical file name, B.tablespace_name tablespace, b.bytes/1024/1024 size M, (B.bytes-sum (NVL (a.bytes,0)))/ 1024/1024 used M,substr ((B.bytes-sum (NVL (a.bytes,0))/(B.bytes)

*100,1,5) utilization from Dba_free_space A,dba_data_files b where a.file_id=b.file_id GROUP by B.tablespace_name,b.file_nam E,b.bytes ORDER by B.tablespace_name;

--Add double quotes when creating, delete also add double quotation mark can sql> DROP tablespace "Liang" including CONTENTS and datafiles;

--Modify tablespace as read only alter TABLESPACE test read only; --Restore table space to normal state--alter tablespace test read wirite;

How to view the usage of Oracle database tablespace Select Dbf.tablespace_name,dbf.totalspace "Total (m)", Dbf.totalblocks as Total block, dfs.freespace "Total remaining (m ) ", dfs.freeblocks" number of remaining blocks ", (dfs.freespace/dbf.totalspace) * 100" Idle

Scale "from (select T.tablespace_name,sum (t.bytes)/1024/1024 totalspace,sum (t.blocks) totalblocks from Dba_data_files T GROUP by T.tablespace_name) DBF, (select

Tt.tablespace_name,sum (tt.bytes)/1024/1024 freespace,sum (tt.blocks) freeblocks from Dba_free_space TT GROUP by TT.TABL Espace_name) Dfs where trim (dbf.tablespace_name) = Trim (dfs.tablespace_name)

--Query table space Number SELECT * from V$tablespace

Oracle table Space Maintenance common commands