Category: Oracle
Oracle: Create Database Link
Create Database Link example: Or even better, because you don’t need to know the TNS_NAME_ENTRY as defined on the server you are creating the DBLINK from:
Oracle DUMP function
Use oracle DUMP function to see internal representation of a character field, e.g. to see hidden trailing characters as in example below: ENTERPRISE_ID EMPLOYER_ID SOURCE_BUSINESS_ID SOURCE_FRIENDLY_ID GOVERMENT_AGENCY_FG SETUP_COMPLETED_FG ————- ———– ————————————————– ——————– ——————- —————— 27905692 13034613 3C02DB52-52C1-43B1-98DE-EA75CA9AB7AC BULS_6930 N Y …
SQLPlus dump to flat file
Example of sqlplus script to dump tables to flat file
LEAD & LAG analytic function
Example of LEAD and LAG analytic functions used to determine previous and next records
Oracle: Select PARTITION name
Example of how to select data along with name of the partition that the data is stored in
Oracle: add many columns
–Add new columns to <TABLE> if not already present SET ECHO OFF SET SERVEROUTPUT ON SIZE 1000000 SET FEEDBACK ON DECLARE P_TABLE_NAME VARCHAR(30 CHAR):='<TABLE>’; BEGIN FOR ICP IN ( SELECT FLIST.ORDR, FLIST.COLUMN_NAME, FLIST.DATA_TYPE, USER_TAB_COLS.COLUMN_NAME AS XCOL_NAME FROM ( SELECT 1 AS …
Subpartition template
Use a subpartition template to automatically create subpartitions. –Create new table NEW_TABLE if this has not already happened DECLARE table_exists NUMBER :=0; BEGIN SELECT COUNT(*) INTO table_exists FROM USER_TABLES WHERE TABLE_NAME = ‘NEW_TABLE’; IF table_exists = 0 THEN EXECUTE IMMEDIATE(q'{ …
Oracle update global and partition stats
Assume we have table called SALES that is range partitioned by day on the SALES_DATE column. At the end of every day data is loaded into latest partition and partition statistics are gathered. Global statistics are only gathered at the …
Oracle: Email validation
A reasonably decent email validation: select PRIMARY_EMAIL_ADDRESS DATA, –Email validation rules determined as per: http://rumkin.com/software/email/rules.php –This validation checks a reasonably comprehensive subset of real world validations, taken from above. –For each individual check, 0=PASS|1=FAIL. Sum results of all checks, if …