Search This Blog

2012-05-30

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

2012-05-29

how-to improve your productivity with keyboard shortcuts on Windows 7

1. Open the programs you use at most , I will use in my example the following :
- Opera
- Explorer
- Chrome
- Textpad

2. Drag and drop the windows on the taskbar so that the left most will be the most used ones ...
3. Right click each one of them on the task bar and select "Pin this program to taskbar" option
4. Now you will be able to start each one of them by the following keyboard shortcut - Windows Logo button + the number of the program - ( in my case Win Logo + 1 would start Opera , Win Logo + 2 - File Explorer etc.

Remember that you could cycle trough the taskbar items with the WinLogo + T keyboard shortcut

oracle cheat sheet v.1.1.7

-- File:OracleCheatSheet.sql v.1.1.7 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_SL_BILLING_HIS'
;
 
 
-- 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
 
; 
 
 
-- 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_SAP_LINE_ITEM'
;
 
 
 
-- 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.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 
 
*/

2012-05-25

how-to open mutliple urls with browser - cmd batch file

@echo off
:: File: GoodMorning.Links.hostname.cmd v1.0.0 docs at the end 
 
:: go the run dir
cd %~dp0
:: this is the dir containing the batch file
set _MyDir=%CD%
 
for %%A in (%0) do set _MyDriveLetter=%%~dA
for %%A in (%0) do set _MyPath=%%~pA
for %%A in (%0) do set _MyName=%%~nA
for %%A in (%0) do set _MyExtension=%%~xA
 
set _CatFile=%_MyDir%\%_MyName%.cat
set _Program=chrome.exe
 
:: CHECK THE VARS 
set _
:: DEBUG 
PAUSE
 
 
:: for each line of the cat file do perform an action ( in this case open urls with chrome ) 
for /f %%i in ('type "%_CatFile%"') do cmd /c start /max %_Program% "%%i"
 
:: DEBUG 
PAUSE
 
:: Purpose: 
:: to provide a generic stub starter fo.cmd files
:: 
:: Usage: 
:: copy this file to a folder where you would like to start the development of th.cmd file 
:: with some customer logic 
:: create a .cmd_file_name>>.cat file with an item per like in the same directory 
:: change the program name in the _Program var
:: 
:: VersionHistory: 
:: 1.0.0 ---    2012-05-25 07:53:56 --- ysg -- Adapted from the cmd_stub

how-to open multiple folders with cmd file -

@echo off
:: File: GoodMorning.Folders.hostname.cmd v1.0.0 docs at the end 
 
:: go the run dir
cd %~dp0
:: this is the dir containing the batch file
set _MyDir=%CD%
 
for %%A in (%0) do set _MyDriveLetter=%%~dA
for %%A in (%0) do set _MyPath=%%~pA
for %%A in (%0) do set _MyName=%%~nA
for %%A in (%0) do set _MyExtension=%%~xA
 
set _CatFile=%_MyDir%\%_MyName%.cat
set _Program=explorer /n, /e,
 
:: CHECK THE VARS 
set _
:: DEBUG 
PAUSE
 
 
:: for each line of the cat file do perform an action ( in this case open folders) 
for /f %%i in ('type "%_CatFile%"') do cmd /c start /max %_Program% "%%i"
 
:: DEBUG 
PAUSE
 
:: Purpose: 
:: to provide a generic stub starter fo.cmd files
:: 
:: Usage: 
:: copy this file to a folder where you would like to start the development of th.cmd file 
:: with some customer logic 
:: create a .cmd_file_name>>.cat file with an item per like in the same directory 
:: change the program name in the _Program var
:: 
:: VersionHistory: 
:: 1.0.0 ---   2012-05-23 09:32:01  --- ysg -- Adapted from the cmd_stub

2012-05-23

netezza data types table

TYPE_NAME DATA_TYPE PRECISION NULLABLE CASE_SENSITIVE MAXIMUM_SCALE
NVARCHAR -9 [NVARCHAR] 16000 TRUE TRUE 0
NCHAR -8 [ROWID] 16000 TRUE TRUE 0
BOOLEAN -7 [BIT] 1 TRUE FALSE 0
BYTEINT -6 [TINYINT] 4 TRUE FALSE 0
BIGINT -5 [BIGINT] 20 TRUE FALSE 0
CHAR 1 [CHAR] 64000 TRUE TRUE 0
NUMERIC 2 [NUMERIC] 38 TRUE FALSE 37
DECIMAL 3 [DECIMAL] 38 TRUE FALSE 37
INTEGER 4 [INTEGER] 11 TRUE FALSE 0
SMALLINT 5 [SMALLINT] 6 TRUE FALSE 0
FLOAT 6 [FLOAT] 7 TRUE FALSE 6
REAL 7 [REAL] 7 TRUE FALSE 14
DOUBLE 8 [DOUBLE] 15 TRUE FALSE 14
VARCHAR 12 [VARCHAR] 64000 TRUE TRUE 0
DATE 91 [DATE] 10 TRUE FALSE 0
TIME 92 [TIME] 15 TRUE FALSE 6
TIMESTAMP 93 [TIMESTAMP] 26 TRUE FALSE 6
INTERVAL YEAR 101 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL MONTH 102 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL DAY 103 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL HOUR 104 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL MINUTE 105 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL SECOND 106 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL YEAR TO MONTH 107 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL DAY TO HOUR 108 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL DAY TO MINUTE 109 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL DAY TO SECOND 110 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL HOUR TO MINUTE 111 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL HOUR TO SECOND 112 [UNKNOWN] 50 TRUE FALSE 0
INTERVAL MINUTE TO SECOND 113 [UNKNOWN] 50 TRUE FALSE 0
TIMETZ 1266 [UNKNOWN] 21 TRUE FALSE 6

Netezza Cheat Sheet v1.5.0

-- File: NetezzaCheatSheet.sql v.1.4.0 docs at the end 
 
 
-- how-to list the columns of a table 
SELECT ',' || NAME || '.' || ATTNAME  from _V_RELATION_COLUMN
WHERE 1=1
AND NAME='ST_SOR_OFFICE'
ORDER BY ATTNUM
; 
 
 
 
 
-- how-to create a database 
CREATE DATABASE DatabaseName
;
-- how-to rename a database 
ALTER DATABASE OldDatabaseName RENAME TO NewDatabaseName 
;
-- how-to create a synomim 
CREATE SYNONYM synonym_name FOR DatabaseName.SchemaName.TableName 
;
-- how-to create a table 
 
CREATE TABLE SVOC_OWNER.ExampleTable
(
    ByteIntCol        byteint            NOT NULL    
  , SmallIntCol       smallint           NOT NULL    
  , IntegerCol        integer            NOT NULL    
  , BigIntCol         bigint             NOT NULL    
  , NumericPSCol      numeric(38,38)     NOT NULL    
  , NumericPCol       numeric(38,0)      NOT NULL    
  , NumericCol        numeric            NOT NULL    
  , DecimalCol        numeric            NOT NULL    
  , FloatCol          float(15)          NOT NULL    
  , RealCol           real               NOT NULL    
  , DoubleCol         double             NOT NULL    
  , CharCol           char(1)        NOT NULL    
  , VarcharCol        varchar(1)     NOT NULL    
  , NcharCol          nchar(1)       NOT NULL    
  , NvarcharCol       nvarchar(1)    NOT NULL    
  , BooleanCol        boolean            NOT NULL    
  , DateCol           date               NOT NULL    
  , TimeCol           time               NOT NULL    
  , TimeTzCol         timetz             NOT NULL    
  , TimestampCol      timestamp          NOT NULL    
 
 )
DISTRIBUTE ON RANDOM
;
 
-- how-to copy table 
CREATE TABLE NewTable AS SELECT * FROM TableToCopy ; 
 
 
-- how-to or insert data from non-current db to current db table
INSERT INTO TableName SELECT * FROM DatabaseName..TableName
;
-- how-to drop a table 
DROP TABLE DatabaseName..TableName 
; 
-- how-to change the ownership of a table 
ALTER TABLE TableName OWNER TO NewOwner 
;
-- how-to perform a simple select 
SELECT * FROM TableName 
WHERE 
AND 1=1 
AND WhereColumnName = 'WhereCondition' 
AND GreaterThanColumnName > 0.0
ORDER BY WhereColumnName
;
-- how-to delete from table 
DELETE FROM TableNameToDeleteFrom 
WHERE FilterColumnName = 'FilterValue'
;
-- how-to call a stored procedure 
CALL ProcName ; 
EXEC ProcName ; 
EXECUTE ProcName ; 
-- example stored procedure 
CREATE OR REPLACE PROCEDURE ProcName() 
RETURNS INT4 LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE
    StrVar varchar;
  BEGIN
    StrVar := 'This string is quoted';
  END;
END_PROC
; --END PROC
-- a single line comment
/*
a multi-line comment
*/
-- example proc with parameters
CREATE OR REPLACE PROCEDURE ProcName (int, varchar(ANY)) RETURNS int
LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE
    pId ALIAS FOR $1;
    pName ALIAS FOR $2;
  BEGIN
    INSERT INTO t1 SELECT * FROM t2 WHERE id = pId;
  END; 
END_PROC 
; 
-- Control structure
IF movies.genre = 'd' THEN
  film_genre := 'drama';
ELSIF movies.genre = 'c' THEN
  film_genre := 'comedy';
ELSIF movies.genre = 'a' THEN
  film_genre := 'action';
ELSIF movies.genre = 'n' THEN
  film_genre := 'narrative';
ELSE
-- An uncategorized genre form has been requested.
film_genre := 'Uncategorized';
END IF;
-- how-to list all stored procedures 
SHOW PROCEDURE ALL 
; 
-- how-to document a stored procedure
COMMENT ON PROCEDURE customer() IS 'Author: bsmith
Version: 1.0 Description: A procedure that writes a customer name to
the database log file.';
-- how-to list all stored procedures 
SHOW PROCEDURE ALL 
; 
 
-- how-to document a stored procedure
COMMENT ON PROCEDURE customer() IS 'Author: bsmith
Version: 1.0 Description: A procedure that writes a customer name to
the database log file.';
 
-- how-to convert date str into nzdate
select  to_date(substring(20090731 from 1 for 8),'YYYYMMDD') as NZDATE
 
-- select top 
select a.* from some_schema.some_table a limit 10
 
-- START how to remove duplicates =================================
CREATE TABLE TmpTableDuplicates as
    SELECT col11,col2,col3 from DuplicatesContainingTable 
    where FilterCol = 'FilterValue'
    group by 1,2,3
; 
DELETE FROM DuplicatesContainingTable where FilterCol = 'FilterValue'
; 
INSERT INTO Source_table select * from TmpTableDuplicates
; 
DROP TABLE TmpTableDuplicates
; 
-- STOP how to remove duplicates =================================
 
 
-- Query to get a list of views and thier definitions in a database:
SELECT VIEWNAME,OWNER,CREATEDATE,DEFINITION FROM _V_VIEW WHERE OBJTYPE='VIEW';
 
-- Query to get a list of tables in a database:
SELECT TABLENAME,OWNER,CREATEDATE FROM _V_TABLE WHERE OBJTYPE='TABLE';
 
-- Query to get a list of columns from a table or a view:
SELECT ATTNUM,ATTNAME FROM _V_RELATION_COLUMN WHERE NAME=UPPER('<TABLE NAME>')
 ORDER BY ATTNUM ASC;
 
-- Query to get list of user groups on the box:
SELECT GROUPNAME,OWNER,CREATEDATE,ROWLIMIT,SESSIONTIMEOUT,
 QUERYTIMEOUT,DEF_PRIORITY,MAX_PRIORITY FROM _V_GROUP;
 
-- Query to get list of users and the groups they are in, on the box:
 SELECT GROUPNAME,OWNER,USERNAME FROM _V_GROUPUSERS;
-- (Does not give any LDAP users in this query)
 
--Query to find the number of rows in a table without actually querying the table:
-- (Sometimes needed for some really huge tables of rowcount > 80 Billion)
 
SELECT RELNAME TABLE_NAME,
 CASE
 WHEN RELTUPLES < 0
 THEN ((2^32) * RELREFS) + ((2^32) + RELTUPLES )
 ELSE ((2^32) * RELREFS) + ( RELTUPLES )
 END NUM_ROWS
 FROM
 _T_CLASS,
 _T_OBJECT
 WHERE
 _T_OBJECT.OBJID=_T_CLASS.OID AND
 _T_OBJECT.OBJCLASS=4905  DISPLAY ONLY TABLES
 AND RELNAME = UPPER('<TABLE NAME>')
 ;
--Query to check if any of the SPU's are running slower than the rest:
--  (This actually gives the read-write speed of each SPU that is online)
SELECT HWID, BYTE_COUNT/TOTAL_MSEC
 FROM
 _VT_DISK_TIMING
 ORDER BY 2;
 
--- HOW-TO GET THE LIST OF TABLES AND THIER SKEW AND SIZE:
 SELECT TABLENAME,OBJTYPE,OWNER,CREATEDATE,USED_BYTES,SKEW
 FROM _V_TABLE_ONLY_STORAGE_STAT
 WHERE OBJCLASS = 4905 OR OBJCLASS = 4911
 ORDER BY TABLENAME;
 
-- START SELECT INTO
INSERT INTO DatabaseNameTarget.SchemaNameTarget.TableNameTarget 
SELECT ColumnName1 , ColumnName2
FROM DatabaseNameSource.SchemaNameSource.TableNameSource
;
-- STOP SELECT INTO 
 
-- how-to remove duplicates
delete from TableWithDuplicates 
where rowid not in 
(
  select min(rowid) from TableWithDuplicates 
  group by (DuplicateDefiningCol1 , DuplicateDefiningCol2 , DuplicateDefiningCol3) 
);
 
-- _V_USER : The user view gives information about the users in the netezza system.
select * from _v_user;
 
-- _V_TABLE: The table view contains the list of tables created in the netezza performance system.
select * from _v_table;
 
-- _V_RELATION_COLUMN: The relation column system catalog view contains the columns available in a table.
select * from _v_relation_column;
 
-- _V_TABLE_INDEX: This system catalog contains the information about the indexes created on table. netezza does not support creating indexes on a table as of now.
select * from _v_table_index;
 
-- _V_OBJECTS: Lists the different objects like tables, view, functions etc available in the netezza.
select * from _v_objects;
 
-- what is running currently 
select * from _v_qrystat;
 
-- what has been running lately 
select * from _v_qryhist;
 
 
 
 
 
 
 
-- Use \dt in nzsql session to get the list tables
/*
\dv to get list of views
 \dmv - list of materialized views
 \l - list of databases
 \dg - list of groups
 \du - list of users
 \dpu - permissions set to a user
 \dT - list of datatypes
 \d <tablename> - describes the table
 \act - show current active sessions
 \d - describe table(or view,sequence)
 \dt , \dv , \ds , \de - list tables,views,sequences,temp tables
 \dSt , \dSv - list system tables and views
 \df - list functions
 \l - list databases
 \dT - list data types
 \du - list users
 \dg - list groups
 \dpu - list permissions granted to a user
 \dpg - list permissions granged to a group
*/
 
-- Purpose:
-- To provide a simple cheat sheet for netezza 
-- VersionHistory
-- 1.5.0 ---  2012-05-23 14:43:38 --- added important views
-- 1.4.0 ---  2012-05-18 15:26:35 --- how-to remove duplicates
-- 1.3.0 ---  2012-05-18 13:44:02 --- added how-to copy table 
-- 1.2.0 --- ysg --- added list cols of a table 
-- 1.1.1 --- ysg --- create table example
-- 1.1.0 --- ysg --- added duplicates removal , date conversion
-- 1.0.0 --- ysg --- Initial creation

2012-05-18

Netezza sql cheat sheet v.1.4.0

-- File: NetezzaCheatSheet.sql v.1.4.0 docs at the end 
 
 
-- how-to list the columns of a table 
SELECT ',' || NAME || '.' || ATTNAME  from _V_RELATION_COLUMN
WHERE 1=1
AND NAME='ST_SOR_OFFICE'
ORDER BY ATTNUM
; 
 
 
 
 
-- how-to create a database 
CREATE DATABASE DatabaseName
;
-- how-to rename a database 
ALTER DATABASE OldDatabaseName RENAME TO NewDatabaseName 
;
-- how-to create a synomim 
CREATE SYNONYM synonym_name FOR DatabaseName.SchemaName.TableName 
;
-- how-to create a table 
 
CREATE TABLE SVOC_OWNER.ExampleTable
(
    ByteIntCol        byteint            NOT NULL    
  , SmallIntCol       smallint           NOT NULL    
  , IntegerCol        integer            NOT NULL    
  , BigIntCol         bigint             NOT NULL    
  , NumericPSCol      numeric(38,38)     NOT NULL    
  , NumericPCol       numeric(38,0)      NOT NULL    
  , NumericCol        numeric            NOT NULL    
  , DecimalCol        numeric            NOT NULL    
  , FloatCol          float(15)          NOT NULL    
  , RealCol           real               NOT NULL    
  , DoubleCol         double             NOT NULL    
  , CharCol           char(1)        NOT NULL    
  , VarcharCol        varchar(1)     NOT NULL    
  , NcharCol          nchar(1)       NOT NULL    
  , NvarcharCol       nvarchar(1)    NOT NULL    
  , BooleanCol        boolean            NOT NULL    
  , DateCol           date               NOT NULL    
  , TimeCol           time               NOT NULL    
  , TimeTzCol         timetz             NOT NULL    
  , TimestampCol      timestamp          NOT NULL    
 
 )
DISTRIBUTE ON RANDOM
;
 
-- how-to copy table 
CREATE TABLE NewTable AS SELECT * FROM TableToCopy ; 
 
 
-- how-to or insert data from non-current db to current db table
INSERT INTO TableName SELECT * FROM DatabaseName..TableName
;
-- how-to drop a table 
DROP TABLE DatabaseName..TableName 
; 
-- how-to change the ownership of a table 
ALTER TABLE TableName OWNER TO NewOwner 
;
-- how-to perform a simple select 
SELECT * FROM TableName 
WHERE 
AND 1=1 
AND WhereColumnName = 'WhereCondition' 
AND GreaterThanColumnName > 0.0
ORDER BY WhereColumnName
;
-- how-to delete from table 
DELETE FROM TableNameToDeleteFrom 
WHERE FilterColumnName = 'FilterValue'
;
-- how-to call a stored procedure 
CALL ProcName ; 
EXEC ProcName ; 
EXECUTE ProcName ; 
-- example stored procedure 
CREATE OR REPLACE PROCEDURE ProcName() 
RETURNS INT4 LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE
    StrVar varchar;
  BEGIN
    StrVar := 'This string is quoted';
  END;
END_PROC
; --END PROC
-- a single line comment
/*
a multi-line comment
*/
-- example proc with parameters
CREATE OR REPLACE PROCEDURE ProcName (int, varchar(ANY)) RETURNS int
LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE
    pId ALIAS FOR $1;
    pName ALIAS FOR $2;
  BEGIN
    INSERT INTO t1 SELECT * FROM t2 WHERE id = pId;
  END; 
END_PROC 
; 
-- Control structure
IF movies.genre = 'd' THEN
  film_genre := 'drama';
ELSIF movies.genre = 'c' THEN
  film_genre := 'comedy';
ELSIF movies.genre = 'a' THEN
  film_genre := 'action';
ELSIF movies.genre = 'n' THEN
  film_genre := 'narrative';
ELSE
-- An uncategorized genre form has been requested.
film_genre := 'Uncategorized';
END IF;
-- how-to list all stored procedures 
SHOW PROCEDURE ALL 
; 
-- how-to document a stored procedure
COMMENT ON PROCEDURE customer() IS 'Author: bsmith
Version: 1.0 Description: A procedure that writes a customer name to
the database log file.';
-- how-to list all stored procedures 
SHOW PROCEDURE ALL 
; 
?
-- how-to document a stored procedure
COMMENT ON PROCEDURE customer() IS 'Author: bsmith
Version: 1.0 Description: A procedure that writes a customer name to
the database log file.';
?
-- how-to convert date str into nzdate
select  to_date(substring(20090731 from 1 for 8),'YYYYMMDD') as NZDATE
?
-- select top 
select a.* from some_schema.some_table a limit 10
?
-- START how to remove duplicates =================================
CREATE TABLE TmpTableDuplicates as
    SELECT col11,col2,col3 from DuplicatesContainingTable 
    where FilterCol = 'FilterValue'
    group by 1,2,3
; 
DELETE FROM DuplicatesContainingTable where FilterCol = 'FilterValue'
; 
INSERT INTO Source_table select * from TmpTableDuplicates
; 
DROP TABLE TmpTableDuplicates
; 
-- STOP how to remove duplicates =================================
?
?
-- Query to get a list of views and thier definitions in a database:
SELECT VIEWNAME,OWNER,CREATEDATE,DEFINITION FROM _V_VIEW WHERE OBJTYPE='VIEW';
?
-- Query to get a list of tables in a database:
SELECT TABLENAME,OWNER,CREATEDATE FROM _V_TABLE WHERE OBJTYPE='TABLE';
?
-- Query to get a list of columns from a table or a view:
SELECT ATTNUM,ATTNAME FROM _V_RELATION_COLUMN WHERE NAME=UPPER('<TABLE NAME>')
 ORDER BY ATTNUM ASC;
?
-- Query to get list of user groups on the box:
SELECT GROUPNAME,OWNER,CREATEDATE,ROWLIMIT,SESSIONTIMEOUT,
 QUERYTIMEOUT,DEF_PRIORITY,MAX_PRIORITY FROM _V_GROUP;
?
-- Query to get list of users and the groups they are in, on the box:
 SELECT GROUPNAME,OWNER,USERNAME FROM _V_GROUPUSERS;
-- (Does not give any LDAP users in this query)
?
--Query to find the number of rows in a table without actually querying the table:
-- (Sometimes needed for some really huge tables of rowcount > 80 Billion)
?
SELECT RELNAME TABLE_NAME,
 CASE
 WHEN RELTUPLES < 0
 THEN ((2^32) * RELREFS) + ((2^32) + RELTUPLES )
 ELSE ((2^32) * RELREFS) + ( RELTUPLES )
 END NUM_ROWS
 FROM
 _T_CLASS,
 _T_OBJECT
 WHERE
 _T_OBJECT.OBJID=_T_CLASS.OID AND
 _T_OBJECT.OBJCLASS=4905 ? DISPLAY ONLY TABLES
 AND RELNAME = UPPER('<TABLE NAME>')
 ;
?--Query to check if any of the SPU's are running slower than the rest:
--  (This actually gives the read-write speed of each SPU that is online)
SELECT HWID, BYTE_COUNT/TOTAL_MSEC
 FROM
 _VT_DISK_TIMING
 ORDER BY 2;
?
--- HOW-TO GET THE LIST OF TABLES AND THIER SKEW AND SIZE:
 SELECT TABLENAME,OBJTYPE,OWNER,CREATEDATE,USED_BYTES,SKEW
 FROM _V_TABLE_ONLY_STORAGE_STAT
 WHERE OBJCLASS = 4905 OR OBJCLASS = 4911
 ORDER BY TABLENAME;
 
-- START SELECT INTO
INSERT INTO DatabaseNameTarget.SchemaNameTarget.TableNameTarget 
SELECT ColumnName1 , ColumnName2
FROM DatabaseNameSource.SchemaNameSource.TableNameSource
;
-- STOP SELECT INTO 
 
-- how-to remove duplicates
delete from TableWithDuplicates 
where rowid not in 
(
  select min(rowid) from TableWithDuplicates 
  group by (DuplicateDefiningCol1 , DuplicateDefiningCol2 , DuplicateDefiningCol3) 
);
 
 
 
-- Use \dt in nzsql session to get the list tables
/*
\dv to get list of views
 \dmv - list of materialized views
 \l - list of databases
 \dg - list of groups
 \du - list of users
 \dpu - permissions set to a user
 \dT - list of datatypes
 \d <tablename> - describes the table
 \act - show current active sessions
 \d - describe table(or view,sequence)
 \dt , \dv , \ds , \de - list tables,views,sequences,temp tables
 \dSt , \dSv - list system tables and views
 \df - list functions
 \l - list databases
 \dT - list data types
 \du - list users
 \dg - list groups
 \dpu - list permissions granted to a user
 \dpg - list permissions granged to a group
*/
?
-- Purpose:
-- To provide a simple cheat sheet for netezza 
-- VersionHistory
-- 1.4.0 ---  2012-05-18 15:26:35 --- how-to remove duplicates
-- 1.3.0 ---  2012-05-18 13:44:02 --- added how-to copy table 
-- 1.2.0 --- ysg --- added list cols of a table 
-- 1.1.1 --- ysg --- create table example
-- 1.1.0 --- ysg --- added duplicates removal , date conversion
-- 1.0.0 --- ysg --- Initial creation

Netezza cheat sheet v.1.3.0

-- File: NetezzaCheatSheet.sql v.1.3.0 docs at the end 
 
 
-- how-to list the columns of a table 
SELECT ',' || NAME || '.' || ATTNAME  from _V_RELATION_COLUMN
WHERE 1=1
AND NAME='ST_SOR_OFFICE'
ORDER BY ATTNUM
; 
 
 
 
-- how-to create a database 
CREATE DATABASE DatabaseName
;
-- how-to rename a database 
ALTER DATABASE OldDatabaseName RENAME TO NewDatabaseName 
;
-- how-to create a synomim 
CREATE SYNONYM synonym_name FOR DatabaseName.SchemaName.TableName 
;
-- how-to create a table 
 
CREATE TABLE SVOC_OWNER.ExampleTable
(
    ByteIntCol        byteint            NOT NULL    
  , SmallIntCol       smallint           NOT NULL    
  , IntegerCol        integer            NOT NULL    
  , BigIntCol         bigint             NOT NULL    
  , NumericPSCol      numeric(38,38)     NOT NULL    
  , NumericPCol       numeric(38,0)      NOT NULL    
  , NumericCol        numeric            NOT NULL    
  , DecimalCol        numeric            NOT NULL    
  , FloatCol          float(15)          NOT NULL    
  , RealCol           real               NOT NULL    
  , DoubleCol         double             NOT NULL    
  , CharCol           char(1)        NOT NULL    
  , VarcharCol        varchar(1)     NOT NULL    
  , NcharCol          nchar(1)       NOT NULL    
  , NvarcharCol       nvarchar(1)    NOT NULL    
  , BooleanCol        boolean            NOT NULL    
  , DateCol           date               NOT NULL    
  , TimeCol           time               NOT NULL    
  , TimeTzCol         timetz             NOT NULL    
  , TimestampCol      timestamp          NOT NULL    
 
 )
DISTRIBUTE ON RANDOM
;
 
-- how-to copy table 
CREATE TABLE NewTable AS SELECT * FROM TableToCopy ; 
 
 
-- how-to or insert data from non-current db to current db table
INSERT INTO TableName SELECT * FROM DatabaseName..TableName
;
-- how-to drop a table 
DROP TABLE DatabaseName..TableName 
; 
-- how-to change the ownership of a table 
ALTER TABLE TableName OWNER TO NewOwner 
;
-- how-to perform a simple select 
SELECT * FROM TableName 
WHERE 
AND 1=1 
AND WhereColumnName = 'WhereCondition' 
AND GreaterThanColumnName > 0.0
ORDER BY WhereColumnName
;
-- how-to delete from table 
DELETE FROM TableNameToDeleteFrom 
WHERE FilterColumnName = 'FilterValue'
;
-- how-to call a stored procedure 
CALL ProcName ; 
EXEC ProcName ; 
EXECUTE ProcName ; 
-- example stored procedure 
CREATE OR REPLACE PROCEDURE ProcName() 
RETURNS INT4 LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE
    StrVar varchar;
  BEGIN
    StrVar := 'This string is quoted';
  END;
END_PROC
; --END PROC
-- a single line comment
/*
a multi-line comment
*/
-- example proc with parameters
CREATE OR REPLACE PROCEDURE ProcName (int, varchar(ANY)) RETURNS int
LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE
    pId ALIAS FOR $1;
    pName ALIAS FOR $2;
  BEGIN
    INSERT INTO t1 SELECT * FROM t2 WHERE id = pId;
  END; 
END_PROC 
; 
-- Control structure
IF movies.genre = 'd' THEN
  film_genre := 'drama';
ELSIF movies.genre = 'c' THEN
  film_genre := 'comedy';
ELSIF movies.genre = 'a' THEN
  film_genre := 'action';
ELSIF movies.genre = 'n' THEN
  film_genre := 'narrative';
ELSE
-- An uncategorized genre form has been requested.
film_genre := 'Uncategorized';
END IF;
-- how-to list all stored procedures 
SHOW PROCEDURE ALL 
; 
-- how-to document a stored procedure
COMMENT ON PROCEDURE customer() IS 'Author: bsmith
Version: 1.0 Description: A procedure that writes a customer name to
the database log file.';
-- how-to list all stored procedures 
SHOW PROCEDURE ALL 
; 
?
-- how-to document a stored procedure
COMMENT ON PROCEDURE customer() IS 'Author: bsmith
Version: 1.0 Description: A procedure that writes a customer name to
the database log file.';
?
-- how-to convert date str into nzdate
select  to_date(substring(20090731 from 1 for 8),'YYYYMMDD') as NZDATE
?
-- select top 
select a.* from some_schema.some_table a limit 10
 
-- START how to remove duplicates =================================
CREATE TABLE TmpTableDuplicates as
    SELECT col11,col2,col3 from DuplicatesContainingTable 
    where FilterCol = 'FilterValue'
    group by 1,2,3
; 
DELETE FROM DuplicatesContainingTable where FilterCol = 'FilterValue'
; 
INSERT INTO Source_table select * from TmpTableDuplicates
; 
DROP TABLE TmpTableDuplicates
; 
-- STOP how to remove duplicates =================================
?
?
-- Query to get a list of views and thier definitions in a database:
SELECT VIEWNAME,OWNER,CREATEDATE,DEFINITION FROM _V_VIEW WHERE OBJTYPE='VIEW';
?
-- Query to get a list of tables in a database:
SELECT TABLENAME,OWNER,CREATEDATE FROM _V_TABLE WHERE OBJTYPE='TABLE';
?
-- Query to get a list of columns from a table or a view:
SELECT ATTNUM,ATTNAME FROM _V_RELATION_COLUMN WHERE NAME=UPPER('<TABLE NAME>')
 ORDER BY ATTNUM ASC;
?
-- Query to get list of user groups on the box:
SELECT GROUPNAME,OWNER,CREATEDATE,ROWLIMIT,SESSIONTIMEOUT,
 QUERYTIMEOUT,DEF_PRIORITY,MAX_PRIORITY FROM _V_GROUP;
?
-- Query to get list of users and the groups they are in, on the box:
 SELECT GROUPNAME,OWNER,USERNAME FROM _V_GROUPUSERS;
-- (Does not give any LDAP users in this query)
?
--Query to find the number of rows in a table without actually querying the table:
-- (Sometimes needed for some really huge tables of rowcount > 80 Billion)
?
SELECT RELNAME TABLE_NAME,
 CASE
 WHEN RELTUPLES < 0
 THEN ((2^32) * RELREFS) + ((2^32) + RELTUPLES )
 ELSE ((2^32) * RELREFS) + ( RELTUPLES )
 END NUM_ROWS
 FROM
 _T_CLASS,
 _T_OBJECT
 WHERE
 _T_OBJECT.OBJID=_T_CLASS.OID AND
 _T_OBJECT.OBJCLASS=4905 ? DISPLAY ONLY TABLES
 AND RELNAME = UPPER('<TABLE NAME>')
 ;
?--Query to check if any of the SPU's are running slower than the rest:
--  (This actually gives the read-write speed of each SPU that is online)
SELECT HWID, BYTE_COUNT/TOTAL_MSEC
 FROM
 _VT_DISK_TIMING
 ORDER BY 2;
?
--- HOW-TO GET THE LIST OF TABLES AND THIER SKEW AND SIZE:
 SELECT TABLENAME,OBJTYPE,OWNER,CREATEDATE,USED_BYTES,SKEW
 FROM _V_TABLE_ONLY_STORAGE_STAT
 WHERE OBJCLASS = 4905 OR OBJCLASS = 4911
 ORDER BY TABLENAME;
 
-- START SELECT INTO
INSERT INTO DatabaseNameTarget.SchemaNameTarget.TableNameTarget 
SELECT ColumnName1 , ColumnName2
FROM DatabaseNameSource.SchemaNameSource.TableNameSource
;
-- STOP SELECT INTO 
 
-- Use \dt in nzsql session to get the list tables
/*
\dv to get list of views
 \dmv - list of materialized views
 \l - list of databases
 \dg - list of groups
 \du - list of users
 \dpu - permissions set to a user
 \dT - list of datatypes
 \d <tablename> - describes the table
 \act - show current active sessions
 \d - describe table(or view,sequence)
 \dt , \dv , \ds , \de - list tables,views,sequences,temp tables
 \dSt , \dSv - list system tables and views
 \df - list functions
 \l - list databases
 \dT - list data types
 \du - list users
 \dg - list groups
 \dpu - list permissions granted to a user
 \dpg - list permissions granged to a group
*/
?
-- Purpose:
-- To provide a simple cheat sheet for netezza 
-- VersionHistory
-- 1.3.0 ---  2012-05-18 13:44:02 --- added how-to copy table 
-- 1.2.0 --- ysg --- added list cols of a table 
-- 1.1.1 --- ysg --- create table example
-- 1.1.0 --- ysg --- added duplicates removal , date conversion
-- 1.0.0 --- ysg --- Initial creation
span class=

2012-05-16

oracle cheat sheet v.1.1.5

-- File:OracleCheatSheet.sql v.1.1.5 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 = 'OPB_WFLOW_VAR_RUN'
;
 
 
-- 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
 
; 
 
 
-- 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
; 
 
 
-- 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;
 
 
 
/* Purpose: 
To provide a cheat sheet for oracle sql 
 
VersionHistory: 
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  --- 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 
 
*/
span class=

Labels

perl (41) Cheat Sheet (25) how-to (24) sql server 2008 (13) windows (13) oracle (12) sql (12) Unix (11) linux (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) cygwin (7) meta data mssql (7) metadata (7) code generation (6) Informatica (5) cheatsheet (5) energy (5) tsql (5) utilities (5) bash (4) excel (4) future (4) generic (4) html (4) perl modules (4) programs (4) settings (4) sh (4) shortcuts (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) git cheat sheet (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) python (3) sftp (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) ascii (2) awk (2) batch (2) cas (2) chrome extensions (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) excel shortcuts (2) export (2) file (2) free programs (2) informatica sql repository (2) logging (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nz netezza cheat sheet (2) nzsql (2) ora (2) prediction (2) publish (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) .virmrc vim settings configs (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) JavaScript anchor html jquery (1) Khan Academy (1) LINUX UNIX BASH AND CYGWIN TIPS AND TRICKS (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Scala (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture (1) archive (1) arguments (1) avatar (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash perl search and replace (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) chrome (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) db2 cheat sheet (1) db2 starter ibm bash Linux (1) debt (1) diagram (1) dictionaries (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) freaky (1) functions (1) fusion research (1) german (1) git gitlab issues handling system (1) google cli (1) google code (1) google command line interface (1) gpg (1) ha (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ibm db2 cognos installation example db deployment provisioning (1) ideas (1) image (1) informatica oracle sql (1) informatica repo sql workflows sessions file source dir (1) informatica source files etl (1) install (1) it management best practices (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash shell grep xargs (1) linux bash tips and t ricks (1) linux bash unix cygwin cheatsheet (1) linux bash user accounts password (1) linux bash xargs space (1) linux cheat sheet (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql cheat sheet (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz table count rows (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) oracle PL/SQL (1) oracle Perl perl (1) oracle installation usability (1) oracle number formatting format-model ora-sql oracle (1) oracle templates create table (1) oracle trigger generic autoincrement (1) oracle vbox virtual box cheat sheet (1) oracle virtual box cheat sheet (1) outlook (1) parser (1) password (1) paths (1) perl @INC compile-time run-time (1) perl disk usage administration Linux Unix (1) perl modules configuration management (1) permissions (1) php (1) picasa (1) platform (1) postgreSQL how-to (1) powerShell cmd cygwin mintty.exe terminal (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scp (1) scripts (1) scsi (1) sed (1) sendEmail (1) sh stub (1) shortcuts Windows sql developer Oracle (1) sidebar (1) silicon (1) smtp (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) sshd cygwin (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tmux efficiency bash (1) tool (1) ui code prototyping tips and tricks (1) umask Linux Unix bash file permissions chmod (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vim perl regex bash search for string (1) vim recursively hacks (1) vim starter (1) vim-cheat-sheet vim cheat-sheet (1) vimeo (1) visual stuio (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows 8 (1) windows programs (1) windows reinstall (1) windows utility batch perl space Windows::Clipboard (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List

VideoBar

This content is not yet available over encrypted connections.