View information (user, table view, index, table space, synonym, etc.) under Oracle Current user

1. Users
View the default tablespace for the current user

Sql>select username,default_tablespace from User_users;

View the role of the current user

Sql>select * from User_role_privs;

View system and table-level permissions for the current user

Sql>select * from User_sys_privs; Sql>select * from User_tab_privs;

Displays the permissions that the current session has

Sql>select * from Session_privs;

Displays the system permissions that the specified user has

Sql>select * from Dba_sys_privs where grantee= ' ezxx ';

　　

2. Table
View all the tables under the user

Sql>select * from User_tables;

View a table with a name that contains a log character

Sql>select object_name,object_id from User_objects where InStr (object_name, ' LOG ') >0;

To view the creation time of a table

Sql>select object_name,created from User_objects where Object_name=upper (' &table_name ');

View the size of a table

Sql>select sum (bytes)/(1024*1024) as "size (M)" from User_segments where Segment_name=upper (' &table_name ');

View tables placed in Oracle's memory area

Sql>select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0;

　3. Index
View index number and category

Sql>select Index_name,index_type,table_name from User_indexes ORDER by TABLE_NAME;

View the fields indexed by the index

Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');

To view the size of an index

Sql>select sum (bytes)/(1024*1024) as "size (M)" from User_segments where Segment_name=upper (' &index_name ');

　4. Serial number
View serial number, Last_number is the current value

Sql>select * from User_sequences;

5. View
View the name of the view

Sql>select view_name from User_views;

View the SELECT statement that created the view

Sql>set view_name,text_length from User_views; Sql>set Long 2000; Description: The size of the set long can be set according to the Text_length value of the view Sql>select text from User_views where View_name=upper (' &view_name ');

　　6. Synonyms
View the name of a synonym

Sql>select * from user_synonyms;

7, Constraints
View constraints on a table

sql>select constraint_name, constraint_type,search_condition ,  r_constraint_name from user_constraints where table_name = upper (' & table_name '); Sql>select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c, User_cons_columns cc where c.owner = upper (' &table_owner ')       and c.table_name = upper (' &table_name ') and c.owner = cc.owner  and c.constraint_name = cc.constraint_name order by cc.position; 

　　

8. Storage functions and procedures
View the status of functions and procedures

Sql>select object_name,status from user_objects where object_type= ' FUNCTION '; Sql>select object_name,status from user_objects where object_type= ' PROCEDURE ';

View source code for functions and procedures

Sql>select text from All_source where Owner=user and Name=upper (' &plsql_name ');

9. View the statement of the build table

SELECT Dbms_metadata. GET_DDL (' TABLE ', ' table_name ') from DUAL; # (table name table_name must be capitalized)

View information (user, table view, index, table space, synonym, etc.) under Oracle Current user