Select A.tablespace_name "Table space name",
100-round ((NVL (b.bytes_free,0)/a.bytes_alloc) *100,2) "occupancy rate (%)",
Round (a.bytes_alloc/1024/1024,2) "Capacity (M)",
Round (NVL (b.bytes_free,0)/1024/1024,2) "Idle (M)",
Round ((A.BYTES_ALLOC-NVL (b.bytes_free,0))/1024/1024,2) "Use (M)",
Largest "Maximum expansion segment (M)",
To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') "Sampling Time"
From (select F.tablespace_name,
SUM (f.bytes) Bytes_alloc,
SUM (Decode (f.autoextensible, ' YES ', f.maxbytes, ' NO ', f.bytes)) MaxBytes
From Dba_data_files F
Group by Tablespace_name) A,
(Select F.tablespace_name,
SUM (f.bytes) bytes_free
From Dba_free_space F
Group by Tablespace_name) B,
(Select Round (max (ff.length) *16/1024,2) Largest,
Ts.name Tablespace_name
From sys.fet$ FF, sys.file$ tf,sys.ts$ TS
where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
Group by Ts.name, Tf.blocks) c
where a.tablespace_name = b.tablespace_name and a.tablespace_name = C.tablespace_name
25. The degree of fragmentation of the query table space
Select Tablespace_name,count (tablespace_name) from Dba_free_space GROUP by Tablespace_name
Having count (Tablespace_name) >10;
Alter tablespace name COALESCE;
ALTER TABLE name DEALLOCATE unused;
Create or replace view Ts_blocks_v as
Select Tablespace_name,block_id,bytes,blocks, ' free spaces ' segment_name from Dba_free_space
UNION ALL
Select Tablespace_name,block_id,bytes,blocks,segment_name from Dba_extents;
SELECT * from Ts_blocks_v;
Select Tablespace_name,sum (bytes), max (bytes), COUNT (block_id) from Dba_free_space
Group BY Tablespace_name;
26. What database tutorial instances are queried to run
Select Inst_name from V$active_instances
===========================================================
######### Create a database----look $ORACLE _home/rdbms/admin/buildall.sql #############
Create DATABASE Db01
Maxlogfiles
Maxdatafiles 1024
Maxinstances 2
LogFile
GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo ') SIZE 15M,
GROUP 2 ('/u01/oradata/db01/log_02_db01.rdo ') SIZE 15M,
GROUP 3 ('/u01/oradata/db01/log_03_db01.rdo ') SIZE 15M,
DataFile ' u01/oradata/db01/system_01_db01.dbf ') Size 100M,
Undo tablespace undo
DataFile '/u01/oradata/db01/undo_01_db01.dbf ' size 40M