1. View SQL scripts of various objects under this user
SQL statement:
table:
Select * from cat;
Select * from tab;
SELECT TABLE_NAME from User_Tables;
view:
Select text from user_views where view_name = Upper ('& view_name');
index:
SELECT INDEX_NAME, TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS from user_indexes Order by table_name
trigger:
SELECT TRIGGER_NAME, TRIGGER_TYPE, TABLE_OWNER, TABLE_NAME, STATUS from User_Triggers
Snapshot:
Select Owner, Name, Master, Table_Name, Last_refresh, Next from User_Snapshots Order by Owner, Next;
Synonym:
SELECT *.
sequence:
SELECT * from SEQ;
Database link:
Select * from user_db_links;
Constraint limit:
SELECT TABLE_NAME, ConsTRAINT_NAME, Search_Condition, Status from User_Constraints;
This user reads the permissions of other user objects:
Select * from user_tab_privs;
System authority owned by this user:
SELECT * from user_sys_privs;
user:
Select * from all_users order by user_id;
The remaining free space of the table space:
SELECT TABLESPACE_NAME, SUM (BYTES) total byte number, max (bytes), count (*) from dba_free_space group by tableSpace_name;
Data Dictionary:
SELECT TABLE_NAME from Dict Order by Table_name;
Lock and resource information:
Select * from v $ lock; excluding DDL lock
Database character set:
SELECT NAME, VALUE $ from Props $ Where name = 'nls_characterset';
ININ.ORA parameters:
Select Name, Value from V $ Parameter Order by Name;
SQL shared pool:
SELECT SQL_TEXT from V $ SQLAREA;
database:
SELECT * FROM V $ DATABASE
Control file:
SELECT * FROM V $ controlfile;
Heavy log file information:
SELECT * from V $ logfile;
Log file information from the control file:
SELECT * from V $ log;
Data file information from the control file:
SELECT * from V $ datafile;
NLS parameter current value:
Select * from V $ NLS_PARAMETERS;
Oracle version information:
SELECT * from V $ Version;
Describe the background process:
Select * from V $ BGPROCESS;
View version information:
Select * from product_component_version;
2. Monitor database
Select 'View the version of the database' from dual;
Select version from product_component_version where substr (product, 1, 6) = 'oracle'
SELECT 'View Database Library Object' from Dual;
Select Owner, Object_Type, Status, Count (*) Count # from all_Objects group by Owner, Object_type, status
Select 'View the creation date and archive of the database' from dual;
SELECT CREATED, LOG_MODE, LOG_MODE FROM V $ DATABASE
SELECT 'View Control File' from Dual;
SELECT NAME FROM V $ ControlFile;
Select 'View the name of the table space and the size' from Dual;
SELECT T.TABLESPACE_NAME, ROM (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;
Select 'View the name of the tablespace physical file and the size' from Dual;
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, ROT (Bytes / (1024 * 1024), 0) Total_Space from DBA_DATA_FILES ORDER by TABLESPACE_NAME
Select 'View the usage of tablespace' from dual;
SELECT SUM (BYTES) / (1024 * 1024) As Free_Space, TableSpace_name from dba_free_space group by tablesspace_name
SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED, C.BYTES Free, (B.BYTES * 100) /A.BYTES "% use", (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 ANTABLESPACE_NAME = C.TABLESPACE_NAME Download Adobe Reader
Select 'View Log File' from Dual;
SELECT MEMBER from V $ logfile;
SELECT 'View the split segment name and size' from dual;
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;
