Common query methods in Oracle
Querying all user information in Oracle
SELECT * from Dba_users;
Querying users and Passwords only
Select Username,password from Dba_users;
Querying Current User Information
SELECT * from Dba_ustats;
Querying view text that users can access
SELECT * from Dba_varrays;
Querying text for all views in the database
SELECT * from Dba_views;
View the default tablespace for the current user
Select Username,default_tablespace from User_users
View the role of the current user
SELECT * FROM User_role_privs
View system and table-level permissions for the current user
SELECT * FROM User_sys_privs
SELECT * FROM User_tab_privs
View all the tables under the user
SELECT * FROM User_tables
Display user information (owning table space)
Select Default_tablespace,temporary_tablespace from Dba_users
Displays the permissions that the current session has
SELECT * FROM Session_privs
Displays the system permissions that the specified user has
SELECT * FROM Dba_sys_privs
Show Privileged Users
SELECT * FROM V$pwfile_users
View a table with a name that contains a log character
Select object_name,object_id from User_objects where InStr (object_name, ' log ') >0
To view the creation time of a table
Select object_name,created from user_objects where object_name= ' Zw_yingyez '
View the size of a table
Select sum (bytes)/(1024*1024) Tablesize from user_segments
where Segment_name= ' Zw_yingyez '
View tables placed in Oracle's memory area
Select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0
View index number and category
Select Index_name,index_type,table_name from User_indexes ORDER BY table_name
View the fields indexed by the index
SELECT * from User_ind_columns where table_name= ' cb_chaobiaosj201004 '
To view the size of an index
Select sum (bytes)/(1024*1024) as indexsize from user_segments
where Segment_name=upper (' As_menuinfo ')
Viewing View information
SELECT * FROM User_views
View the name of a synonym
SELECT * FROM user_synonyms
View the status of functions and procedures
Select Object_name,status from user_objects where object_type= ' FUNCTION '
Select Object_name,status from user_objects where object_type= ' PROCEDURE '
View source code for functions and procedures
Select text from All_source where Owner=user and Name= ' sf_split_string '
View table fields
Select cname from col where tname= ' Zw_yingyez '
Select column_name from User_tab_columns where table_name= ' Zw_yingyez '
To view the Oracle version command:
SELECT * FROM V$version
Common query methods in Oracle
