-- 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
*/
No comments:
Post a Comment
- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!