oracle cheat sheet - v1.1.8

-- File:OracleCheatSheet.sql v.1.1.8 docs at the end

-- DEFINE A NICE DATETIME FORMAT

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY.MM.DD HH24:MI:SS';

-- FIND FIRST THE NAME OF THE TABLE 

SELECT TABLE_NAME FROM ALL_TABLES 

-- WHERE TABLE_NAME LIKE '%LOAD%'

; 

-- GENERATE A COLUMN SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'FACT_COPA'

;

-- GENERATE A TABLE_NAME.COLUMN_NAME SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || TABLE_NAME || '.' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'FACT_ORDER_HIS2'

ORDER BY COLUMN_ID

;

SELECT * FROM ALL_TAB_COLUMNS

; 

-- how-to select top or limit the result set in oracle 

select * from 

( select tmpTable.*, ROWNUM RowNumber from 

  ( -- this is the initial select clause

    SELECT * FROM MyTable

    ORDER BY MyTableId DESC

  ) tmpTable 

  where ROWNUM <= 100 

)

where RowNumber  >= 0

; 

-- GET THE COLUMN LIST FROM A TABLE 

SELECT ',' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'FACT_SAP_HIS' ; 

-- convert ids to human readable values 

select 

  T2.WORKFLOW_NAME 

, T2.START_TIME

, T2.END_TIME

, decode (RUN_STATUS_CODE,

1 , 'Succeeded',

2,  'Disabled',

3,  'Failed',

4,  'Stopped',

5,  'Aborted',

6,  'Running',

15, 'Terminated')  Status

from OPB_WFLOW_RUN T2

WHERE 1=1

AND START_TIME >= to_date('2012-04-03 00:00:00','YYYY-MM-DD HH24:MI:SS')

AND END_TIME   <=   to_date('2012-04-04 00:00:00','YYYY-MM-DD HH24:MI:SS')

ORDER BY START_TIME DESC

; 

SELECT TABLE_NAME as TableName , COLUMN_NAME as ColumnName  FROM all_tab_columns ; 

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 

       -- referenced pk

       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk

  FROM all_cons_columns a

  JOIN all_constraints c ON a.owner = c.owner

                        AND a.constraint_name = c.constraint_name

  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner

                           AND c.r_constraint_name = c_pk.constraint_name

 WHERE c.constraint_type = 'R'

   -- AND a.table_name = 'OPB_CNX'

   -- and c_pk.table_name = 'OPB_CNX'

   AND c_pk.constraint_name LIKE '%%'

   AND OWNER='INFAPROD'

   ; 

-- FIND A COLUMN_NAME 

SELECT OWNER ||'.' ||  TABLE_NAME ||'.' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE 1=1 

-- AND COLUMN_NAME LIKE '%OBJECT%' 

AND OWNER = 'INFAPROD'

-- AND TABLE_NAME LIKE 'REP_SESS_WIDGET_CNXS'

-- AND COLUMN_NAME LIKE '%OBJECT%'

ORDER BY OWNER , TABLE_NAME

;

-- get the short SID name 

select name from v$database;

-- get the global TNS Service name 

select * from global_name;

-- LIST THE INSTANCE NAME AND HOSTNAME 

select instance_number, instance_name, host_name from v$instance;

-- how-to copy table from another table from one schema to another 

create table SOURCE_SHEMA.TARGET_TABLE unrecoverable as SELECT * from TARGET_SCHEMA.SOURCE_TABLE

;

-- check the priveledges of your current session

select * from session_privs

; 

-- check the priveledges 

SELECT OWNER , OBJECT_NAME, OBJECT_TYPE FROM all_objects

where 1=1

; 

-- check oracle version 

select * from v$version where banner like 'Oracle%';

-- get h priveldeges 

select * from system_privilege_map

; 

SELECT GRANTEE , OWNER , TABLE_NAME , GRANTOR , PRIVILEGE ,  GRANTABLE ,  HIERARCHY  FROM all_tab_privs_made

; 

SELECT * FROM all_tab_privs_made

; 

-- LIST ALL THE PRIMARY KEYS 

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME 

WHERE 1=1

AND C.TABLE_NAME =  'SOR_IM_MAP_TULOSLASKELMA_ENT'

-- AND C.OWNER='MANKELI_OWNER'

AND C.CONSTRAINT_TYPE = 'P'

 ; 

-- LIST ALL THE FOREIGH KEYS 

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME 

WHERE 1=1 

-- AND A.OWNER='MANKELI_OWNER'

--AND C.TABLE_NAME = 'SOR_IM_MAP_TULOSLASKELMA_ENT'

AND C.CONSTRAINT_TYPE = 'R'

; 

-- CHECK A TABLE FOR DUPLICATES ( A DUPLICATE IS DEFINED AS THE SAVE VALUES IN THE GROUP BY COL LIST)

SELECT       

   TableName.Col1 

 , TableName.Col2T

 FROM TableName

 WHERE 1=1

 AND FilterColumn='SomeFilterValue'

 group by    

   TableName.Col1 

 , TableName.Col2T

  having count (*) > 1;

select constraint_name from user_constraints 

where 1=1

and table_name = 'TABLE_NAME'

;  

-- GET ALL THE INDEXES FOR A TABLE 

select * from ALL_INDEXES

where 1=1

AND table_name = 'FACT_ORDER_HIS'

AND OWNER='MANKELI_OWNER'

;

-- how-to restore allready dropped table 

Flashback table "SchemaName"."TableName" to before drop;

/* Purpose: 

To provide a cheat sheet for oracle sql 

VersionHistory: 

1.1.8 --- 2012-05-30 17:24:59 --- ysg --- Added order by for all_tab_columns !!! bug 

1.1.7 --- 2012-05-29 10:12:40 --- ysg --- how-to restore allready dropped table

1.1.6 --- 2012-05-28 16:39:22 --- ysg --- Added indexes 

1.1.5 --- 2012-05-16 14:01:57 --- ysg --- Added select top and select limit ora version

1.1.4 --- 2012.05.02 10:12:03 --- ysg --- Added check duplicates example

1.1.3 --- 2012-04-27 10:28:29 --- ysg --- Added check oracle version  

1.1.2 --- 2012-04-26 15:27:14 --- ysg --- Added current sessions privs query 

1.1.1 --- ysg --- Added how-to copy table 

1.1.0 --- ysg --- added generate col list 

1.0.0 --- ysg --- initial creation 

*/