Oracle queries table space usage and queries which database instances are running

Oracle queries table space usage and queries which database instances are running

Query table space usage

　　

Select a. tablespace_name "tablespace name ",

100-round (nvl (B. bytes_free, 0)/a. bytes_alloc) *) "usage (% )",

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 extension 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),

(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. query the degree of table space fragmentation

　　

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

Select tablespace_name, block_id, bytes, blocks, 'free space' 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. Query which database tutorial instances are running

　　

Select inst_name from v $ active_instances;

========================================================== ==============================

######### Create a database ---- look $ ORACLE_HOME/rdbms/admin/buildall. SQL #############

Create database db01

Maxlogfiles 10

Maxdatafiles 1024

Maxinstances 2

Logfile

GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo') SIZE 15 M,

GROUP 2 ('/u01/oradata/db01/log_02_db01.rdo') SIZE 15 M,

GROUP 3 ('/u01/oradata/db01/log_03_db01.rdo') SIZE 15 M,

Datafile 'u01/oradata/db01/system_01_db01.dbf') SIZE 100 M,

Undo tablespace UNDO

Datafile '/u01/oradata/db01/undo_01_db01.dbf' SIZE 40 M