SQL1:
Copy Code code as follows:
--1, viewing the name and size of the table space
SELECT T.tablespace_name, round (SUM (Bytes/1024 * 1024)), 0 ts_size
From Dba_tablespaces T, Dba_data_files D
WHERE T.tablespace_name = D.tablespace_name
GROUP by T.tablespace_name;
--2, viewing the name and size of a tablespace physical file
SELECT Tablespace_name,
FILE_ID,
file_name,
Round (Bytes/(1024 * 1024), 0) Total_space
From Dba_data_files
Order BY Tablespace_name;
--3, view rollback segment name and size
SELECT Segment_name,
Tablespace_name,
R.status,
(initial_extent/1024) Initialextent,
(next_extent/1024) Nextextent,
Max_extents,
V.curext curextent
From Dba_rollback_segs R, V$rollstat V
WHERE r.segment_id = V.USN (+)
Order BY Segment_name;
--4, view Control files
SELECT NAME from V$controlfile;
--5, viewing log files
SELECT member from V$logfile;
--6, viewing table space usage
SELECT SUM (bytes)/(1024 * 1024) as free_space, Tablespace_name
From Dba_free_space
GROUP by Tablespace_name;
SELECT A.tablespace_name,
A.bytes Total,
B.bytes used,
C.bytes free,
(B.bytes *)/a.bytes "% USED",
(C.bytes *)/a.bytes "% free"
From Sys.sm$ts_avail A, sys.sm$ts_used B, Sys.sm$ts_free c
WHERE A.tablespace_name = B.tablespace_name
and a.tablespace_name = C.tablespace_name;
--7, view database library objects
SELECT owner, object_type, status, COUNT (*) count#
From All_objects
GROUP by Owner, object_type, status;
--8, view the version of the database
SELECT version
From Product_component_version
WHERE substr (product, 1, 6) = ' Oracle ';
--9, view the date the database was created and how it was archived
SELECT created, Log_mode, log_mode from V$database;
SQL2:
Copy Code code as follows:
--1G=1024MB
--1m=1024kb
--1k=1024bytes
--1m=11048576bytes
--1g=1024*11048576bytes=11313741824bytes
SELECT a.tablespace_name table space name,
Total table space size,
free table space remainder Size,
(total-free) Table space usage size,
Total/(1024 * 1024 * 1024) "Table space Size (G)",
Free/(1024 * 1024 * 1024) tablespace Remaining size (g) ",
(Total-free)/(1024 * 1024 * 1024)" Table space use Size (g) ",
Round ((total-free)/Total, 4) * 100" Usage% "
from (SELECT tablespace_name, SUM (bytes) free
from Dba_free_space
GROUP by Tablespace_name) A,
(SEL ECT tablespace_name, SUM (bytes) Total
from Dba_data_files
GROUP by tablespace_name) b
WHERE A.tablespace _name = B.tablespace_name
