Oracle query table space usage and query for which database instances are running

Query table Space usage

　　

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