From Database to MPP to Data Lake

select name,value/1024/1024 as mb from v$statname natural join v$mystat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index'); select name,value byt from v$statname natural join v$mystat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index'); select name,value byt from v$statname natural join v$sysstat where name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index'); select * from (select first_name, salary, avg(salary) over (partition by department_id order by employee_id) as avg_sal from emp_lg_rnd) where salary > avg_sal CREATE USER APP1 IDENTIFIED BY APP1 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT DBA TO APP1; sqlplus app1/APP1 create table sales as select 'Oracle Enterprise Edition' as produc...

-- Oracle Query to identify non ascii data SELECT 'PRICING' , 'PROD_CHAN' , 'PROD_CHAN_TYP_NM' , PROD_CHAN_TYP_NM FROM   PRICING . PROD_CHAN   WHERE LENGTH ( ASCIISTR ( PROD_CHAN_TYP_NM )) <> LENGTH ( PROD_CHAN_TYP_NM ) ; -- Oracle Query to identify non ascii data  SELECT 'PRICING' , 'PROD_CHAN' , 'PROD_CHAN_TYP_NM' , PROD_CHAN_TYP_NM FROM   PRICING . PROD_CHAN   WHERE REPLACE ( TRANSLATE ( LOWER ( PROD_CHAN_TYP_NM ) , 'abcdefghijklmnopqrstuvwxyz1234567890_-+=() *&^%$#@!~`:}{][|\/?.,><"'''||chr ( 10 )|| chr ( 13 )|| chr ( 9 ), 'X' ), 'X' , '' ) ;

#! /bin/ksh filename=$1 echo $filename . /pkg1/Informatica/PowerCenter860/server/infa_shared/UnixScripts/DataInt/TD/hoovers_etl.env bteq <<stop>/pkg1/Informatica/PowerCenter860/server/infa_shared/UnixScripts/DataInt/TD/logs/loader/$(date +"20%y%m%d")/$(echo $filename|cut -c85-100).log 2>&1 logon $TDPID/$TDUSER,$TDPASS; .RUN FILE=$filename; EXIT; STOP

REPLACE MACRO BTEQ_EXPORT ( DATABASENAME VARCHAR(100), TABLENAME VARCHAR(100), FILENAME VARCHAR(500), SERVER VARCHAR(100) default '' , USERNAME VARCHAR(300) default '', PASS VARCHAR(15) default 'password' ) AS ( SEL '                   .SESSIONS 5 .SET ECHOREQ ON .SET FORMAT OFF .SET QUIET ON .SET WIDTH 1000 .SET RETCANCEL ON .LOGON  '   || :SERVER || '/' || :USERNAME || ', ' || :PASS || '; '  ||     ' .EXPORT REPORT FILE =' || :FILENAME BTEQ_SCRIPT     FROM SYS_CALENDAR.CALENDAR WHERE DATE=CALENDAR_DATE     UNION   SEL '                   SEL TOP 1000000            '   FROM SYS_CALENDAR.CALENDAR WHERE DATE=CALENDAR_DATE   UNION   SEL   '                 TRIM(COALESCE (CAST(' || COLUMNNAME ||  'AS VARCHAR(300)),' ||'27'x...

To find contact_opt_out recrsively inside the directory find . | xargs grep -s OMNI_Balbao_Loads.par ~~ date + "%y-%m-%d" ~~ to manipulate string in command use brackets preceeded by dollar sign eg: mv filename $(echo filename|cut c-2-100) renames file from fielname to ilename ( because of cut command) ~~ To ridrect error commands use 2> or >& eg sh script.sh 2>errorfile ,sh script.sh >& errorfile ~~ To check space du -sh df /foldername df -h ~~

--Check query Performance SEL     TOP 5   QueryId  , cast ( QUERYTEXT as varchar ( 5000 ))  , ElapsedTime                    , MaxAMPCPUTime                  , AMPCPUTime                     , MaxAmpCpuTime * NumOfActiveAmps                  ,( MaxAmpCpuTime - ( 1.00 * AmpCpuTime )/ NumOfActiveAmps ) ,( 1 -( 1.00 * AmpCpuTime /( MaxAmpCpuTime * NumOfActiveAmps )))* 100 ,( MaxAmpCpuTime * NumOfActiveAmps )/( AmpCpuTime ) FROM            DBC . QRYLOG WHERE         MAXAMPCPUTIME > 100 ; -- Query...