Oracle common SQL syntax set

Oracle common SQL syntax set

Table:
Select * from cat;
Select * From tab;
Select table_name from user_tables;
View:
Select text from user_views where view_name = upper ('& view_name ');
Index:
Select index_name, table_owner, table_name, tablespace_name, status from user_indexes order by table_name;
Trigger:
Select trigger_name, trigger_type, table_owner, table_name, status from user_triggers;
Snapshot:
Select owner, name, Master, table_name, last_refresh, next from user_snapshots order by owner, next;
Synonym:
Select * From SYN;
Sequence:
Select * From seq;
Database link:
Select * From user_db_links;
Constraints:
Select table_name, constraint_name, search_condition, status
From user_constraints where table_name = upper ('& table_name ');
This user's permission to read other user objects:
Select * From user_tab_privs;
System permissions of the User:
Select * From user_sys_privs;
User:
Select * From all_users order by user_id;
Free space remaining in the tablespace:
Select tablespace_name, sum (bytes) Total Bytes, max (bytes), count (*) from dba_free_space group by tablespace_name;
Data Dictionary:
Select table_name from dict order by table_name;
Lock and Resource Information:
Select * from V $ lock; DDL lock not included
Database character set:
Select name, value $ from props $ where name = 'nls _ characterset ';
Inin. ora parameters:
Select name, value from V $ Parameter order by name;
SQL sharing pool:
Select SQL _ text from V $ sqlarea;
Database:
Select * from V $ Database
Control file:
Select * from V $ controlfile;
Redo log file information:
Select * from V $ logfile;
Log File Information from the control file:
Select * from V $ log;
Data File Information from the control file:
Select * from V $ datafile;
Current Value of the NLS parameter:
Select * from V $ nls_parameters;
Oracle version:
Select * from V $ version;
Description of the background process:
Select * from V $ bgprocess;
View version information:
Select * From product_component_version;
Oracle-common monitoring SQL
1. Waiting for monitoring cases:
Select event, sum (decode (wait_time, 1) Prev, sum (decode (wait_time, 0) curr, count (*)
From v $ session_wait
Group by event order by 4;
2. rollback segment contention:
Select name, waits, gets, Waits/gets ratio from V $ rollstat A, V $ rollname B where a. USN = B. USN;
3. I/O ratio of the monitored tablespace:
Select DF. tablespace_name name, DF. file_name "file", F. phyrds Pyr, F. phyblkrd PBR, F. phywrts pyw,
F. phyblkwrt PBW
From v $ filestat F, dba_data_files DF
Where F. File # = DF. file_id
4. I/O ratio of the empty file system:
Select substr (A. File #, 1, 2) "#", substr (A. Name, 1, 30) "name", A. Status, A. bytes,
B. phyrds, B. phywrts
From v $ datafile A, V $ filestat B
Where a. File # = B. File #
5. Search for all indexes under a user:
Select user_indexes.table_name, user_indexes.index_name, uniqueness, column_name
From user_ind_columns, user_indexes
Where user_ind_columns.index_name = user_indexes.index_name
And user_ind_columns.table_name = user_indexes.table_name
Order by user_indexes.table_type, user_indexes.table_name,
User_indexes.index_name, column_position;
6. Monitor SGA hit rate
Select a. Value + B. Value "logical_reads", C. Value "phys_reads ",
Round (100 * (A. Value + B. Value)-C. Value)/(A. Value + B. Value) "buffer hit ratio"
From v $ sysstat A, V $ sysstat B, V $ sysstat C
Where a. Statistic # = 38 and B. Statistic # = 39
And C. Statistic # = 40;
7. Monitor the hit rate of the Dictionary Buffer in SGA
Select parameter, gets, getmisses, getmisses/(gets + getmisses) * 100 "miss ratio ",
(1-(sum (getmisses)/(sum (gets) + sum (getmisses) * 100 "hit ratio"
From v $ rowcache
Where gets + getmisses <> 0
Group by parameter, gets, getmisses;
8. Monitor the hit rate of the shared cache area in SGA, which should be less than 1%
Select sum (PINs) "Total Pins", sum (reloads) "Total reloads ",
Sum (reloads)/sum (PINs) * 100 libcache
From v $ librarycache;
Select sum (pinhits-reloads)/sum (PINs) "Hit Radio", sum (reloads)/sum (PINs) "reload percent"
From v $ librarycache;
9. display the category and size of all database objects
Select count (name) num_instances, type, sum (source_size) source_size,
Sum (parsed_size) parsed_size, sum (code_size) code_size, sum (error_size) error_size,
Sum (source_size) + sum (parsed_size) + sum (code_size) + sum (error_size) size_required
From dba_object_size
Group by type order by 2;
10. Monitor the log cache hit rate in SGA, which should be less than 1%
Select name, gets, misses, immediate_gets, immediate_misses,
Decode (gets, 100, misses/gets *) ratio1,
Decode (immediate_gets + immediate_misses, 0, 0,
Immediate_misses // (immediate_gets + immediate_misses) * 100) ratio2
From v $ latch where name in ('redo allocation', 'redo copy ');
11. Monitor the sorting ratio of memory to hard disk. It is best to make it smaller than. 10 and increase sort_area_size.
Select name, value from V $ sysstat where name in ('sorts (memory) ', 'sorts (Disk )');
12. Monitor who is running the SQL statement of the current database
Select osuser, username, SQL _ text from V $ session A, V $ sqltext B
Where a. SQL _ address = B. Address order by address, piece;
13. Monitoring Dictionary Buffer
Select (sum (pins-reloads)/sum (PINs) "lib cache" from V $ librarycache;
Select (sum (gets-getmisses-usage-fixed)/sum (gets) "Row cache" from V $ rowcache;
Select sum (PINs) "executions", sum (reloads) "cache misses while executing" from V $ librarycache;
The latter is divided by the former. This ratio is less than 1%, and it is better to close to 0%.
Select sum (gets) "dictionary gets", sum (getmisses) "dictionary cache get misses"
From v $ rowcache
14. Find the Oracle Character Set
Select * From SYS. Props $ where name = 'nls _ characterset ';
15. Monitor MTS
Select busy/(busy + idle) "shared servers busy" from V $ dispatcher;
When the value is greater than 0.5, the parameter must be increased.
Select sum (wait)/sum (totalq) "dispatcher waits" from V $ queue where type = 'dispatcher ';
Select count (*) from V $ dispatcher;
Select servers_highwater from V $ Mts;
When servers_highwater is close to mts_max_servers, increase the Parameter
16. Fragmentation degree
Select tablespace_name, count (tablespace_name) from dba_free_space group by tablespace_name
Having count (tablespace_name)> 10;
Alter tablespace name coalesce;
Alter table name deallocate unused;
Create or replace view ts_blocks_v
Select tablespace_name, block_id, bytes, blocks, 'free space' segment_name from dba_free_space
Union all
Select tablespace_name, block_id, bytes, blocks, segment_name from dba_extents;
Select * From ts_blocks_v;
Select tablespace_name, sum (bytes), max (bytes), count (block_id) from dba_free_space
Group by tablespace_name;
View tables with high fragmentation levels
Select segment_name table_name, count (*) extents
From dba_segments where owner not in ('sys ', 'system') group by segment_name
Having count (*) = (select max (count (*) from dba_segments group by segment_name );
17. Check the storage of tables and Indexes
Select segment_name, sum (bytes), count (*) ext_quan from dba_extents where
Tablespace_name = '& tablespace_name' and segment_type = 'table' group by tablespace_name, segment_name;
Select segment_name, count (*) from dba_extents where segment_type = 'index' and owner = '& owner'
Group by segment_name;
18. Find a user session with multiple CPUs
12 is CPU used by this session
Select a. Sid, spid, status, substr (A. Program, 60/100) prog, A. Terminal, osuser, value/value
From v $ session A, V $ process B, V $ sesstat C
Where C. Statistic # = 12 and C. Sid = A. Sid and A. paddr = B. ADDR order by value DESC;
20. Monitor the usage of log_buffer: (the value should be smaller than 1%; otherwise, increase the size of log_buffer)
Select rbar. Name, rbar. Value, re. Name, re. Value, (rbar. Value * 100)/Re. Value | '%' "radio"
From v $ sysstat rbar, V $ sysstat re
Where rbar. Name = 'redo Buffer Allocation retries'
And re. Name = 'redo entries ';
19. view the running SQL statements:
Select SQL _ text
From v $ SQL
Common Oracle SQL statements
Query table structure
Select substr (table_name, 1, 20) tabname,
Substr (column_name, 1, 20) column_name,
Rtrim (data_type) | '(' | data_length | ')' from system. dba_tab_columns
Where owner = 'username'
Table space usage status
Select a. file_id "fileno", A. tablespace_name "tablespace_name ",
Round (A. Bytes/1024/1024, 4) "Total MB ",
Round (A. bytes-sum (nvl (B. bytes, 0)/1024/1024, 4) "used MB ",
Round (sum (nvl (B. bytes, 0)/1024/1024, 4) "Free MB ",
Round (sum (nvl (B. bytes, 0)/A. bytes * 100,4) "% free"
From dba_data_files A, dba_free_space B
Where a. file_id = B. file_id (+)
Group by A. tablespace_name,
A. file_id, A. bytes order by A. tablespace_name
Queries a table in a mode where data is not empty.
Declare
Cursor C is select tname from tab;
Vcount number;
Table_nm varchar2 (100 );
Sq varchar2 (300 );
Begin
For R in C Loop
Table_nm: = R. tname;
Sq: = 'select count (*) from' | table_nm;
Execute immediate sq into vcount;
If vcount> 0 then
Dbms_output.put_line (R. tname );
End if;
End loop;
End;
Client host information
Select
Sys_context ('userenv', 'terminal') terminal,
Sys_context ('userenv', 'host') host,
Sys_context ('userenv', 'OS _ user') OS _user,
Sys_context ('userenv', 'IP _ address') IP _ address
From dual
View rollback segment name and size
Column roll_name format A13 heading 'rollback name'
Column tablespace format A11 heading 'tablspace'
Column in_extents format A20 heading 'init/next extents'
Column m_extents &

Nbsp; format A10 heading 'min/MAX extents'
Column status format A8 heading 'status'
Column wraps format 999 heading 'wraps'
Column shrinks format 999 heading 'shrink'
Column opt format 999,999,999 heading 'opt. size'
Column bytes format 999,999,999 heading 'bytes'
Column extents format 999 heading 'extents'
Select
A. Owner | '.' | A. segment_name roll_name
, A. tablespace_name tablespace
, To_char (A. initial_extent) | '/' |
To_char (A. next_extent) in_extents
, To_char (A. min_extents) | '/' |
To_char (A. max_extents) m_extents
, A. Status status
, B. bytes
, B. extents
, D. Shrinks shrinks
, D. Wraps wraps
, D. optsize OPT
From
Dba_rollback_segs
, Dba_segments B
, V $ rollname C
, V $ rollstat d
Where
A. segment_name = B. segment_name
And a. segment_name = C. Name (+)
And C. USN = D. USN (+)
Order by A. segment_name;

Reprinted link: http://www.gispower.org/article/db/2007/925/079251397I7DFC178AF6GJ4H8GIBF.html