SQL tips

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 index  rebuild tablespace ;

Coalesce indexes

--------------------

ALTER index  coalesce;

Checking indexes and their validity

-----------------------------------

analyze index  validate structure;

Monitor the usage of the index ( start/stop).

-------------------------------------------------

Start

-----

ALTER index  monitoring usage;

Stop

----

ALTER index  nomonitoring usage;

Rename the index

---------------------

ALTER index  rename 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 table  move tablespace ;

Move partitions of a partitioned table to a different tablespace_name

----------------------------------------------------------------------

ALTER table  move 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 table  exchange partition p200609 with table  with validation;

Split the partition

-------------------

eg: splits p200701 into p200701 and 200612
ALTER table   split 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) FROM  partition(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">

SQL Tutorial with Examples

Pages

SQL tips

Leave a Reply

Popular Posts

Archives

Labels

Followers