How Oracle queries are used

First, users

View the default table space 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;

Second, the table

View all tables under the user

Sql>select * from User_tables;

View a table with a name that contains log characters

Sql>select object_name,object_id from User_objects

where InStr (object_name, ' LOG ') >0;

View when a table was created

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 the tables placed in Oracle's memory area

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

Third, index

View the number and category of indexes

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

To view the fields indexed by an index

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

Viewing the size of an index

Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments

where Segment_name=upper (' &index_name ');

Four, serial number

View serial number, Last_number is the current value

Sql>select * from User_sequences;

Five, view

View the name of the view

Sql>select view_name from User_views;

View a SELECT statement that creates a view

Sql>set view_name,text_length from User_views;

Sql>set Long 2000; Note: You can set the size of the set long based on the text_length value of the view

Sql>select text from User_views where View_name=upper (' &view_name ');

VI. Synonyms

View the name of a synonym

Sql>select * from user_synonyms;

Vii. Conditions of constraint

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;

VIII. 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 ');

IX. triggers

viewing triggers

Set long 50000;

Set heading off;

Set PageSize 2000;

Select

' Create or replace trigger ' | |

trigger_name | | '"' || Chr (10) | |

Decode (substr (Trigger_type, 1, 1),

' A ', ' after ', ' B ', ' before ', ' I ', ' INSTEAD of ') | |

Chr (10) | |

triggering_event | | Chr (10) | |

' On ' | | Table_owner | | '"."' ||

table_name | | '"' || Chr (10) | |

Decode (InStr (Trigger_type, ' each ROW '), 0, NULL,

' For each ROW ') | | Chr (10),

Trigger_body

From User_triggers;

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .