Category: Database
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 …
Grant permissions to all tables in schema
BEGIN FOR TAB_NAME IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = ‘‘ ) LOOP EXECUTE IMMEDIATE ‘GRANT SELECT ON ‘ || TAB_NAME.TABLE_NAME || ‘ TO ‘; END LOOP; END;
Oracle: Find tables referring to another
select table_name, constraint_name, status, owner from all_constraints where r_constraint_name in (–get primary and unique constraints on the target table. These can be used for FK references by other tables select constraint_name from all_constraints where constraint_type in (‘P’, ‘U’) and table_name …
Oracle QUOTE function: Use to avoid complicated escape character sequences
Rather than fret about how to escape 2 single quotes for example, just use the quote function and native text: QUOTE: q'{}’; vSQL := q'{ALTER TABLE DIM_ISSUE ADD (CONSTRAINT DIM_ISSUE_C9 CHECK (length(regexp_replace(UPPER(issue_name),'[ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_]’,”)) = 0 AND regexp_like(issue_name,’^[ABCDEFGHIJKLMNOPQRSTUVWXYZ]+.*$’)))}’; EXECUTE IMMEDIATE(vSQL;)
Oracle: Drop table / column if it exists
DECLARE table_exists NUMBER :=0; –column_exists NUMBER :=0; BEGIN –Drop table / column if it exists SELECT COUNT(*) INTO table_exists FROM USER_TABLES WHERE TABLE_NAME = ‘‘; –SELECT COUNT(*) INTO column_exists ALL_TAB_COLUMNS WHERE OWNER = ‘‘ AND TABLE_NAME = ‘‘ AND COLUMN_NAME …