Re-usable SQL Queries useful in
day-to-day scenario.
COMMON QUERIES
***************
SELECT instance_name FROM v$instance;
Find the locked_objects
-----------------------
SELECT object_name FROM all_objects WHERE object_id in(SELECT object_id FROM v$locked_object);
SELECT object_name, object_id FROM all_objects a WHERE exists (SELECT 1 FROM v$locked_object b WHERE a.object_id = b.object_id);
Find the nls_parameters values
--------------------------------
SELECT * FROM v$nls_parameters;
Find the valid nls_parameter values
------------------------------------
SELECT * FROM v$nls_valid_values ;
Check the parameter settings for
optimizer
-------------------------------------------
SELECT * FROM v$parameter WHERE lower(name) like '%optimizer%'
Enable parallel dml
-------------------
ALTER session enable parallel dml;
Check for table creation details
---------------------------------
SELECT owner,object_name,object_type,CREATEd,last_ddl_time FROM all_objects WHERE object_name = upper('<table_name>');
Find the time remaining for a query.
(Also to check if it is running properly)
------------------------------------------------------------------------------
SELECT sid,time_remaining,elapsed_seconds,message FROM v$session_longops WHERE sid in (SELECT sid FROM v$session a,v$sqltext b WHERE a.sql_address = b.address and username = 'bdm' and upper(sql_text) like 'CREATE table%' and a.status = 'active' ) and time_remaining!=0 ORDER BY sid
Find numrows
------------
SELECT table_name , num_rows FROM all_tables WHERE table_name = '&table_name';
Find the dependencies
------------------------
SELECT name , type , dependency_type FROM all_dependencies;
Find data dictionary tables available
--------------------------------------
SELECT table_name FROM dict ORDER BY 1;
Find object type
-----------------
SELECT object_name , object_type FROM all_objects WHERE object_name = upper('&object_name');
For analyzing tables
--------------------
ANALYZE TABLE &table_name COMPUTE STATISTICS;
Find last_analyzed details
--------------------------
SELECT last_analyzed FROM user_tables WHERE table_name = upper('&table_name');
SELECT last_analyzed FROM user_indexes WHERE table_name = upper('&table_name');
Gather table stats
-------------------
exec dbms_stats.delete_table_stats('&owner','&table_name'); exec dbms_stats.gather_table_stats('&owner','&table_name',null,4,false,'for all indexed columns size 50', 5,'default',true,null,null,null); exec dbms_stats.gather_table_stats(ownname=>user, tabname=>âÂÂtable_nameâÂÂ, estimate_percent=>5, method_opt=>'for all indexed columns size 50', cascade=>true);
(5 is a good starter.If you have a
really huge table (>10million rows), you may want to do just 1% as
5% will take a long time OR If you have a really small table (<10k
rows), you may want to do 99%)
Example Table_Name is csban_1
exec dbms_stats.gather_table_stats(ownname=>'bdm',tabname=>'csban_1',estimate_percent=>5,method_opt=>'for all indexed columns size 50',cascade=>true);
Delete table stats
------------------
exec dbms_stats.delete_table_stats(user,'&table_name'); exec dbms_stats.delete_table_stats(user,'agent_list_id_temp');
Unlock table stats
-------------------
exec dbms_stats.unlock_table_stats(ownname=>'cusp272',tabname=>'crtsb562v');
DB LINKS
*********
Creating private database link
-------------------------------
CREATE database link <link_name> CONNECT TO <schema> USING <database>;
Droping private database link
------------------------------
DROP database link <link_name>;
Find the db_links
-------------------
DESC all_db_links SET lines 132 pages 66 COL owner FOR a15 COL db_link FOR a15 COL username FOR a15 COL host FOR a15 SELECT owner,Db_link,username,host FROM all_db_links WHERE db_link like upper('%&link_name%');
get passwords for the user database
links
------------------------------------------
SET lines 132 pages 66 COL db_link FOR a15 COL username FOR a15 COL host FOR a15 SELECT db_link,username,password,host FROM user_db_links WHERE db_link like upper('%&link_name%');
INDEX
******
Find index details
------------------
DESC all_indexes DESC all_ind_columns
Get index details from the server
---------------------------------
COL tb_name FOR a20 COL tb_sp FOR a20 COL ind_name FOR a25 COL col_name FOR a20 COL col_pos FOR 99 break on tb_name skip 0 on tb_sp skip 0 on ind_name skip 2 on col_name skip 0 SET lines 132 pages 66 SELECT a.table_name tb_name, a.tablespace_name tb_sp, a.index_name ind_name, index_type ind_typ, column_name col_name , column_position col_pos FROM all_indexes a, all_ind_columns b WHERE a.table_name = b.table_name and a.index_name = b.index_name and a.table_name = upper('&table_name') ORDER BY a.index_name,column_position;
Get index details from remote server
------------------------------------
col tb_name for a20 col tb_sp for a20 col ind_name for a25 col col_name for a20 col col_pos for 99 break on tb_name skip 0 on tb_sp skip 0 on ind_name skip 2 on col_name skip 0 set lines 132 pages 66 SELECT a.table_name tb_name, a.tablespace_name tb_sp, a.index_name ind_name, index_type ind_typ, column_name col_name , column_position col_pos FROM all_indexes@a, all_ind_columns@ b WHERE a.table_name = b.table_name and a.index_name = b.index_name and a.table_name = upper('&table_name') ORDER BY a.index_name,column_position;
Build index
------------
exec index_builder.build('bdm','&table_name','pdm');
Nuke index
----------
exec index_builder.nuke('bdm','&table_name','pdm');
Change the tablespace for a given index
for a partitioned table
----------------------------------------------------------------
Example:
table name : prod_rev_fact
index name : prdrev_prdcddim_idx
columns : product_code_dim_id
partitions : tablespace name
-----------------------------
p200401 : prdrevfct200401
p200412 : prdrevfct200401
p200512 : prdrevfct200401
p200606 : prod_rev_fact_ts
Step 1: Drop the index using the
command.
------
DROP INDEX prdrev_prdcddim_idx;
Step 2:
-------
CREATE INDEX prdrev_prdcddim_idx ON prod_rev_fact (product_code_dim_id) initrans 2 maxtrans 255 nologging local ( partition p200401 nologging tablespace prdrevfct200401 pctfree 10 initrans 2 maxtrans 255 storage ( initial 5m next 5m minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default ), partition p200412 nologging tablespace prdrevfct200401 pctfree 10 initrans 2 maxtrans 255 storage ( initial 5m next 5m minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default ), partition p200512 nologging tablespace prdrevfct200401 pctfree 10 initrans 2 maxtrans 255 storage ( initial 5m next 5m minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default ), partition p200606 nologging tablespace prod_rev_fact_ts pctfree 10 initrans 2 maxtrans 255 storage ( initial 5m next 5m minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default ) ) parallel ( degree 3 instances 1 );
Move an index to another tablespace
-------------------------------------
ALTER indexrebuild tablespace ;
Coalesce indexes
--------------------
ALTER indexcoalesce;
Checking indexes and their validity
-----------------------------------
analyze indexvalidate structure;
Monitor the usage of the index (
start/stop).
-------------------------------------------------
Start
-----
ALTER indexmonitoring usage;
Stop
----
ALTER indexnomonitoring usage;
Rename the index
---------------------
ALTER indexrename to ;
TABLESPACE
***********
Find table spaces
------------------
DESC all_tables SET lines 132 pages 66 newp 0 COL owner FOR a20 COL tb_sp FOR a20 SELECT owner,table_name tb_name,tablespace_name tb_sp FROM all_tables WHERE table_name = upper('&table_name'); SELECT segment_name, segment_type, CREATEd, bytes FROM all_objects o, dba_segments s WHERE o.object_name=s.segment_name and o.object_type=s.segment_type AND tablespace_name=upper('&table_name') ORDER BY CREATEd DESC,bytes;
Find Free Space:
---------------
SELECT tablespace_name, sum(bytes) FROM dba_free_space having sum(bytes) in (SELECT max(sum(bytes)) FROM dba_free_space group by tablespace_name) group by tablespace_name ORDER BY 2; SELECT tablespace_name, sum(bytes) FROM dba_free_space group by tablespace_name ORDER BY 2 asc;
Actual space occupied by the table
-----------------------------------
COL segment_name FOR a25 COL partition_name FOR a20 COL segment_type FOR a15 COL space_mb FOR 99999 COL blocks FOR 999999 break on segment_name skip 0 on segment_type skip 0 compute sum of mb on segment_name compute sum of blocks on segment_name SELECT segment_name,segment_type,partition_name,bytes/1024/1024 mb,blocks FROM user_segments WHERE segment_name = upper('&table_name');
Actual space occupied by the data.
--------------------------------
SELECT table_name,(num_rows*avg_row_len)/(1024*1024) mb FROM user_tables WHERE table_name=upper('&table_name');
Move a table to another tablespace
----------------------------------
ALTER tablemove tablespace ;
Move partitions of a partitioned table
to a different tablespace_name
----------------------------------------------------------------------
ALTER tablemove partition tablepsace nologging;
PARTITIONS
***********
Find partitions of a given table along
with partition_name
-----------------------------------------------------------
DESC all_tab_partitions SET lines 132 pages 66 COL owner FOR a8 COL tbl_name FOR a20 COL part_name FOR a20 COL part_pos FOR 99 COL part_col FOR a15 COL num_rows FOR 999999999 break on owner skip 0 on tbl_name skip 0 on part_col skip 0 on part_name skip 0 on report skip 0 SELECT table_owner owner , table_name tbl_name, column_name part_col, partition_name part_name, partition_position part_pos, num_rows num_rows FROM all_tab_partitions, all_part_key_columns WHERE table_name = name(+) AND table_name = upper('&table_name') ORDER BY 1,2,3,4,5,6; DESC all_ind_partitions SELECT partition_name FROM all_ind_partitions WHERE table_name = upper('&table_name');
Find a partition_name for a given table
---------------------------------------
SET lines 132 pages 66 COL owner FOR a10 COL tbl_name FOR a10 SELECT * FROM all_part_key_columns WHERE name = upper('&table_name');
ADD/TRUNC/DEL partition in a given
table
-----------------------------------------
ALTER table &table_name ADD PARTITION p200606 values less than (200607); ALTER table &table_name TRUNCATE p200606 values less than (200607); ALTER table &table_name DROP p200606 values less than (200607);
Exchange partition (get data from a
non-partitioned table into partitioned table partition)
------------------------------------------------------------------------------------------
ALTER tableexchange partition p200609 with table with validation;
Split the partition
-------------------
eg: splits p200701 into p200701 and 200612 ALTER tablesplit partition p200701 at ('200701') into ( partition p200612 , partition p200701) ; ALTER table split partition p200612 at ('200612') into ( partition p200611 , partition p200612) ;
Get count from a partitioned table
----------------------------------
SELECT /*+ parallel(a,8) */ count(1) FROMpartition(p200608) a;
SYNONYMS
*********
DESC all_synonyms
-----------------
FROM local server
------------------
COL owner FOR a10 COL syn_name FOR a20 COL tbl_own FOR a15 COL tbl_name FOR a20 COL db_link FOR a15 break on owner skip 0 on syn_name skip 0 on tbl_own skip 0 on tbl_name skip 0 on db_link skip 0 SELECT owner, synonym_name syn_name, table_owner tbl_own, table_name tbl_name, db_link FROM all_synonyms WHERE synonym_name = upper('&synonym_name');
FROM remote server
------------------
COL owner FOR a10 COL syn_name FOR a20 COL tbl_own FOR a15 COL tbl_name FOR a20 COL db_link FOR a15 break on owner skip 0 on syn_name skip 0 on tbl_own skip 0 on tbl_name skip 0 on db_link skip 0 SELECT owner, synonym_name syn_name, table_owner tbl_own, table_name tbl_name, db_link FROM all_synonyms@crdmprd WHERE synonym_name = upper('&synonym_name');
VIEWS
*****
Get view details
----------------
SET long 3000 SET lines 132 pages 66 SELECT owner , view_name , text FROM all_views WHERE view_name = upper('&vw_name');
CONSTRAINTS
************
Get information on constraints of a
given table
------------------------------------------------
(including foreign (referred)
table/column information, a query like this may be used)
SET lines 132 pages 66 COLUMN consname FORMAT a15; COLUMN colname FORMAT a12; COLUMN type FORMAT a14; COLUMN cond FORMAT a25; COLUMN ref_tabname FORMAT a14; COLUMN ref_colname FORMAT a14; SELECT uc.constraint_name consname, ucc.column_name colname, case when uc.constraint_type='c' then 'check' when uc.constraint_type='p' then 'primary key' when uc.constraint_type='r' then 'referential' when uc.constraint_type='u' then 'unique' else uc.constraint_type end as type, uc.search_condition cond, ucc_r.table_name ref_tabname, ucc_r.column_name ref_colname FROM user_constraints uc JOIN user_cons_columns ucc on uc.constraint_name=ucc.constraint_name and uc.owner=ucc.owner LEFT JOIN user_constraints uc_r on uc.r_constraint_name=uc_r.constraint_name and uc.owner=uc_r.owner LEFT JOIN user_cons_columns ucc_r on uc_r.constraint_name=ucc_r.constraint_name and uc_r.owner=ucc_r.owner WHERE uc.table_name = upper('&tablename') ORDER BY consname,colname; COL owner FOR a10 COL tbl FOR a12 COL name FOR a23 COL typ FOR a1 COL col_name FOR a10 COL c_pos FOR 99 COL src_cond FOR a10 COL r_owner FOR a10 COL r_cons FOR a20 SET lines 132 pages 66 SELECT a.owner owner, a.table_name tbl, a.constraint_name name, a.constraint_type typ, b.column_name col_name, b.position c_pos, a.status status, a.search_condition src_cond, a.r_owner r_owner, a.r_constraint_name r_cons FROM all_constraints a, all_cons_columns b WHERE a.constraint_name = b.constraint_name and a.constraint_name = upper('&constraint_name');
PRIVILEGES
***********
Get the privileges granted on objects
(all_tab_privs)
------------------------------------------------------
SET lines 132 pages 66 COL grantor FOR a15 COL grantee FOR a15 COL table_schema FOR a8 COL table_name FOR a15 COL privilege FOR a8 COL grantable FOR a3 COL hierarchy FOR a3 break on grantor skip 0 on grantee skip 0 on table_schema skip 0 on table_name skip 0 on privilege skip 0 SELECT grantor,grantee,table_schema,table_name,privilege,grantable,hierarchy FROM all_tab_privs WHERE table_name = '&table_name' ORDER BY grantor,grantee,table_schema,table_schema,table_name,privilege;
Find user_sys_privs
--------------------
SELECT distinct grantable FROM all_tab_privs ;
Find privileges given on different
tables
------------------------------------------
DESC all_tab_privs
SELECT distinct privilege FROM
all_tab_privs ;
SELECT distinct grantable FROM
all_tab_privs ;
Grant/Revoke privileges:
------------------------
GRANT select, insert, update, delete ON <object> TO <user>; REVOKE privileges ON <object> FROM <user>;
2nd max salary
Description
This tool is used to to select the 2nd
max salary from a table.
select B.ecm_code,b.rec from( select A.ecm_code,rownum rec from ( select ECM_CODE,rownum from T_ECM order by ecm_code desc )A where rownum<3 data-blogger-escaped-pre="pre" data-blogger-escaped-rec="2" data-blogger-escaped-where="where">