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

Source: Internet
Author: 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.