It is usually necessary to find the specific content of an object. For example, if you do not know the name, it is package, procedure, function, and so on, it is quite troublesome to find them one by one.
Here we will collect some commonly used useful SQL statements
1. query object
Select * From tab returns the table/view name Type
Select * From tabs is similar, but if it is to query the table information, this is the most appropriate, the returned columns are more detailed, all are table Type
Select * From dict does not seem to be useful only when the object name and comment are returned.
Select * from cat (synonym for view SYS. user_catalog)
Select * From all_objects (this is the most detailed and I usually use this)
Select * From user_objects (this is similar to all_objects, but they are all created by users rather than automatically created by the system. This is also common, actually, this is more suitable than the data obtained by all_objects, because we generally do not pay too much attention to the built-in objects)
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 * from
SYN;
Sequence:
Select * From seq;
Database link:
Select * from
User_db_links;
Constraints:
Select
Table_name, constraint_name, search_condition, status from
User_constraints;
This user's permission to read other user objects:
Select * from
User_tab_privs;
System permissions of the User:
Select * from
User_sys_privs;
User:
Select * From all_users order
User_id;
Free space remaining in the tablespace:
Select tablespace_name, sum (bytes)
Total Bytes, max (bytes), count (*) from dba_free_space group
Tablespace_name;
Data Dictionary:
Select table_name from dict order
Table_name;
Lock and Resource Information:
Select * from V $ lock; DDL lock not included
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 sharing pool:
Select SQL _text from
V $ sqlarea;
Database:
Select * from V $ Database
Control file:
Select * from
V $ controlfile;
Redo 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;
Current Value of the NLS parameter:
Select * from
V $ nls_parameters;
Oracle version:
Select * from
V $ version;
Description of the background process:
Select * from V $ bgprocess;
View version information:
Select *
From product_component_version;
View object dependencies:
Select * From dba_dependencies where name = 'objectname'
The test shows that this statement is not as useful as ideal. function, procedure, package, and so on are supported. However, this statement is not suitable for tables and cannot be created only by the system table users.
2. query code
Select line, text from user_source where name = 'object _ name ';
----------------- Unfinished -- to be continued ----------
