Oracle Table Space Operations

1.oracle Viewing table space usage

1) Use SYSDBA to enter Sqlplus--Query tablespace usage Select UPPER (f.tablespace_name) "tablespace name", D.TOT_GROOTTE_MB "table space size (M)", D.TOT_GROOTTE_MB -F.total_bytes "used Space (M)", To_char (ROUND ((d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb * 100,2), ' 990.99 ') | | '% ' "use ratio", f.total_bytes "free Space (M)", f.max_bytes "maximum block (m)" from (select Tablespace_name, ROUND (SUM (BYTES)/(1024 * 1024 ), 2) total_bytes, ROUND (MAX (BYTES)/(1024x768 * 1024x768), 2) Max_bytes from SYS. Dba_free_space GROUP by Tablespace_name) F, (SELECT DD. Tablespace_name, ROUND (SUM (DD). BYTES)/(1024x768 * 1024x768), 2) tot_grootte_mb from SYS. Dba_data_files DD GROUP by DD. Tablespace_name) D WHERE d.tablespace_name = f.tablespace_name ORDER by 1;--query tablespace for free spaceselect tablespace_name, cou NT (*) as extends, round (sum (bytes)/1024/1024, 2) as MB, sum (blocks) as blocks from Dba_free_space GROUP by Tablesp ace_name;--Total capacity of the query tablespace select tablespace_name, sum (bytes)/1024/1024 as MB from Dba_data_files Group by Tablespace_name; --Query table space usage Select total.Tablespace_name, round (total. MB, 2) as TOTAL_MB, round (total. Mb-free. MB, 2) as USED_MB, round (1-free. Mb/total. MB) * 100, 2) | | '% ' as used_pct from (select Tablespace_name, sum (bytes)/1024/1024 as MB from Dba_free_space Group by Tablespace_na　　Me) free, (select Tablespace_name, sum (bytes)/1024/1024 as MB from Dba_data_files Group by Tablespace_name) total where free.tablespace_name = Total.tablespace_name;

2.oracle Extended Table Space

1 First step: View the name and file location of the tablespace: select Tablespace_name, file_id, File_name,round (bytes/(1024*1024), 0) Total_space from Dba_data _files ORDER BY Tablespace_name2 Second step: Increase the required tablespace size: Alter DATABASE datafile ' tablespace location ' resize New dimensions for example: Alter DATABASE datafile ' \ora CLE\ORADATA\ANITA_2008.DBF ' Resize 4000m3 step three: Set table space to automatically expand ALTER DATABASE DataFile ' \ORACLE\ORADATA\ANITA_2008.DBF ' Autoextend on next 100m maxsize 10000m4 what are the tables under table space Select Table_name,tablespace_name from Dba_tables where tablespace_name Like '%tbs_portal% ';//with specific user access to database 5. Select Segment_name from user_segmant where segmant_type= ' tbs_portal ';d ESC dba_segmant;--View the size of each table footprint for the current user: Select Segment_name,sum (bytes)/1024/1024 from User_extents Group by segment_name;--View the size of each tablespace footprint: Select tablespace_name, Sum (bytes)/1024/1024 from Dba_segments Group by tablespace_name--View table occupied capacity select a.segment_name,a.bytes/1024/1024 as M F Rom user_segments a where a.segment_type= ' TABLE ' and a.tablespace_name= ' tbs_portal ' ORDER by M desc; Select A.segment_name , a.bytes/1024/1024 asM from user_segments a where a.segment_type= ' TABLE ' ORDER by M Desc; Select segment_name,sum (bytes)/1024/1024 from User_extents Group by Segment_name;

3.oracle Delete table space free space

Sql> ALTER TABLE tb_im_req_msg enable row movement; Table altered. sql> ALTER TABLE tb_im_req_msg shrink space; Table altered.

4.oracle Undo Table Space

1) View the current undo tablespace show parameter undoshow parameter undo_tablespace2) displays all undo tablespace for the database. SELECT Tablespace_name from Dba_ Tablespaces WHERE contents= ' undo '; 3) toggle UNDO table space. Alter system set UNDO_TABLESPACE=UNDOTBS02 Scope=both;

Oracle Table Space Operations