Oracle View object Information

1. View information for all objects under a user:

SELECT owner, object_type, status, COUNT (*where owner='xxx'  2

View Code

2. View the number of table rows (multiple tables) (the table must be analyzed first):

1 Select 'Analyze Table'|| table_name| |'compute statistics;'  fromUser_tables;2 3 SelectTable_name,num_rows fromDba_tableswhereOwner='webagent'ORDER BY1;4 5 SelectCOUNT (*) fromwithdrawal_org;

View Code

3. View the size of the table space:

SELECT T.tablespace_name, round (SUM (Bytes/(1024x768*0=

View Code

4. View the usage of the tablespace:

SELECT tablespace_name, SUM (bytes)/(1024x768

View Code

 - "  "" "= = C.tablespace_ Name

View Code

5. View the table space physical file name and size:

/(1024x768 *1024x7680) total_space from Dba_data_files ORDER by Tablespace_name;

View Code

6. Check the SQL that is being executed and the SQL that was executed:

A. Querying the SQL that is being executed

Select A.username, A.sid,b.sql_text, B.sql_fulltext    from  where a.sql_address = b.address;

View Code

B. Querying for SQL executed over a specified time

Select B.sql_text,b.first_load_time,b.sql_fulltext    from V$sqlarea b where ' 2009-10-15/09:24:47 ' and        ' 2009-10-15/09:24:47 '

View Code

7. Recycle Bin:

1 drop table T1 purge; 2 purge recyclebin;

View Code

8. Monitor Oracle's Wait events:

Select Event, SUM (Decode (wait_time,0,0,1))"Prev", SUM (Decode (wait_time,0,1,0))"Curr", COUNT (*)"Tot"   fromv$session_wait Group byEventOrder by4;

View Code

9. Object information in the statistics mode:

A. Viewing object information for the current schema

Select  from User_objects Group by Object_type;

View Code

View object information for the specified schema under B.sys user

Select  from where tablespace_name='tsp_webagent'  GROUP by Segment_type;

View Code

Oracle View object Information