Oracle viewing Object Information

Oracle viewing Object Information

1. View information about all objects of a user:

SELECT owner, object_type, status, COUNT (*) count #
FROM all_objects
Where owner = 'xxx'
Group by owner, object_type, status
Order by 2;

2. view the number of rows in the table (multiple tables) (An analysis table is required first ):

1 select 'analyze table' | table_name | 'ute statistics; 'from user_tables;
2
3 select table_name, num_rows from dba_tables where owner = 'webagent' order by 1;
4
5 select count (*) from WITHDRAWAL_ORG;

3. view the tablespace size:

SELECT t. tablespace_name, round (SUM (bytes/(1024*1024*1024), 0) ts_size_GB
FROM dba_tablespaces t, dba_data_files d
WHERE t. tablespace_name = d. tablespace_name
Group by t. tablespace_name;

4. view the table space usage:

SELECT tablespace_name, SUM (bytes)/(1024*1024) AS free_space_MG
FROM dba_free_space
Group by tablespace_name;

SELECT a. tablespace_name,
A. bytes total,
B. bytes used,
C. bytes free,
(B. bytes * 100)/a. bytes "% USED ",
(C. bytes * 100)/a. bytes "% FREE"
FROM sys. sm $ ts_avail a, sys. sm $ ts_used B, sys. sm $ ts_free c
WHERE a. tablespace_name = B. tablespace_name
AND a. tablespace_name = c. tablespace_name;

5. view the name and size of the tablespace physical file:

SELECT tablespace_name,
File_id,
File_name,
Round (bytes/(1024*1024*1024), 0) total_space
FROM dba_data_files
Order by tablespace_name;

6. Check the executed SQL and the executed SQL:

A. query the SQL statement being executed

Select a. username, a. sid, B. SQL _TEXT, B. SQL _FULLTEXT
From v $ session a, v $ sqlarea B
Where a. SQL _address = B. address;

B. query the SQL statements executed within the specified time

Select B. SQL _TEXT, B. FIRST_LOAD_TIME, B. SQL _FULLTEXT
From v $ sqlarea B
Where B. FIRST_LOAD_TIME between '2017-10-2009: 24: 47 'and
'2017-10-2009: 24: 47 'order by B. FIRST_LOAD_TIME

7. Recycle Bin:

1 drop table t1 purge;
2 purge recyclebin; View Code
8. Monitoring of Oracle wait events:

Select event,
Sum (decode (wait_Time, 0, 0, 1) "Prev ",
Sum (decode (wait_Time, 0, 1, 0) "Curr ",
Count (*) "Tot"
From v $ session_Wait
Group by event
Order by 4;

9. Object Information in statistical mode:

A. view the object information of the current schema

Select object_type, count (*) from user_objects group by object_type;

B. view the object information of the specified schema under the sys user

Select SEGMENT_TYPE, COUNT (*) from dba_segments where tablespace_name = 'tsp _ WEBAGENT 'group by SEGMENT_TYPE;