SQL1:
Copy codeThe Code is as follows: -- 1. view the table space name and size.
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. view the name and size of the 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 the 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. view log files
Select member from v $ logfile;
-- 6. View 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 * 100)/a. bytes "% USED ",
(C. bytes * 100)/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 objects
SELECT owner, object_type, status, COUNT (*) count #
FROM all_objects
Group by owner, object_type, status;
-- 8. view the database version
SELECT version
FROM product_component_version
WHERE substr (product, 1, 6) = 'oracle ';
-- 9. view the database creation date and archiving method
SELECT created, log_mode, log_mode FROM v $ database;
SQL2:Copy codeThe Code is as follows: -- 1G = 1024 MB
-- 1 M = 1024KB
-- 1 K = 1024 Bytes
-- 1 M = 11048576 Bytes
-- 1G = 1024*11048576 Bytes = 11313741824 Bytes
SELECT a. tablespace_name "tablespace name ",
Total "tablespace size ",
Free "remaining tablespace size ",
(Total-free) "table space size ",
Total/(1024x1024*1024) "tablespace size (G )",
Free/(1024*1024*1024) "remaining tablespace size (G )",
(Total-free)/(1024*1024*1024) "tablespace size (G )",
Round (total-free)/total, 4) * 100 "usage %"
FROM (SELECT tablespace_name, SUM (bytes) free
FROM dba_free_space
Group by tablespace_name),
(SELECT tablespace_name, SUM (bytes) total
FROM dba_data_files
Group by tablespace_name) B
WHERE a. tablespace_name = B. tablespace_name
