Oracle cheat sheet v.1.1.2

-- File:OracleCheatSheet.sql v.1.1.2 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 '%RUN%'

; 

-- 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 = 'OPB_WFLOW_VAR_RUN'

;

-- 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 = 'OPB_WFLOW_VAR_RUN'

;

-- GET THE COLUMN LIST FROM A TABLE 

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

-- 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

; 

/* Purpose: 

To provide a cheat sheet for oracle sql 

VersionHistory: 

1.1.2 ---  2012.04.26 - 15:27:14  --- 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 

*/