1, check the name and size of the table space
Set linesize 140;
SET PAGES 200;
Column Tablespace_name Format A30;
SELECT TABLESPACE_NAME, Min_EXTENTS, MAX_EXTENTS, PCT_INCREASE, STATUS from DBA_TABLESPACES;
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, Contents, Logging, Extent_Management, Allocation_Type
From DBA_TABLESPACES
Order by tablespace_name;
2, check the name and size of the physical file of the table space
Column db_block_size new_value blksz Noprint
SELECT VALUE DB_BLOCK_SIZE from V $ Parameter Where Name = 'DB_BLOCK_SIZE';
Column tablespace_name format A16;
Column file_name format A60;
SET LINESIZE 160;
select file_name, round (bytes / (1024 * 1024), 0) total_space, autoextensible, increment_by * & blksz / (1024 * 1024) as incement, maxbytes / (1024 * 1024) as maxsize from dba_data_files order by tablespace_name;
3. View the name of the rollback segment and size
Column roll_name format A13 Heading 'Rollback Name'
Column TableSpace Format A11 Heading 'Tablspace'
Column in_Extents Format A20 Heading 'Init / Next Extents'
Column M_EXTents Format A10 Heading 'Min / Max Extens'
Column Status Format A8 Heading 'Status'
Column Wraps Format 999 Heading 'WRAPS'
Column Shrinks Format 999 Heading 'Shrinks'
Column Opt Format 999, 999, 999 Heading 'Opt. Size'
Column Bytes Format 999, 999, 999 Heading 'Bytes'
Column Extents Format 999 Heading 'Extents'
SELECT
A.OWNER || '.' || a.segment_name roll_name
, a.tablespace_name tablespace
, To_char (a.initial_extent) || '/' ||
TO_CHAR (A.NEXT_EXTENT) in_extents
, To_char (a.min_extents) || '/' ||
TO_CHAR (A.MAX_EXTENTS) M_EXTENTS, A.STATUS STATUS
, B.bytes bytes
, B.Extents Extents
, D.Shrinks Shrinks
, D.WRAPS WRAPS
, D.OPTSIZE OPT
From
DBA_ROLLBACK_SEGS A
, DBA_SEGMENTS B
, V $ ROLLNAME C
, V $ ROLLSTAT D
WHERE
A.SEGMENT_NAME = B.SEGMENT_NAME
And a.segment_name = c.name ( )
And C.usn = D.USN ( )
Order by a.segment_name;
4, check the control file
SELECT NAME FROM V $ ControlFile;
5, check the log file
SELECT MEMBER from V $ logfile;
6, check the usage of table space
Select * from (SELECT SUM (BYTES) / (1024 * 1024) AS "free_space (m)", TABLESPACE_NAME
From DBA_FREE_SPACE
Group by TableSpace_name) Order by "free_space (m)";
7, check the database library object
Select Owner, Object_Type, Status, Count (*) Count # from all_Objects group by Owner, Object_type, status
8, check the version of the database
SELECT * from V $ Version;
9. View the creation date and archive of the database
SELECT CREATED, LOG_MODE, LOG_MODE FROM V $ DATABASE
10. View temporary database files
Select Status, Enabled, Name from V $ TEMPFILE
