Oracle snippets

oracle_snippets.sql

-- create table
create table "TABLE_NAME" 
(
  "ID" NUMBER(9, 0) NOT NULL,
  "COL_1" VARCHAR2(32) NULL,
  "COL_2" DATE NULL,
  "COL_3" NVARCHAR2(2000) NULL,
  "COL_4" NUMBER(9, 0) NOT NULL,
  constraint "PK_TABLE_NAME" PRIMARY KEY ("ID")
);

-- add column to an existing table
alter table TABLE_NAME add COLUMN_NAME DATA_TYPE default DEFAULT_VALUE;

-- constraints (pks, fks ...)
alter table TABLE_NAME add constraint PK_TABLE_COLUMN_NAME primary key (COLUMN_NAME);
alter table supplier add constraint pk_supplier primary key (s_suppkey) rely; -- NOTE: rely
alter table lineorder add constraint fk_suppkey foreign key (lo_suppkey) references supplier(s_suppkey) rely disable novalidate; -- NOTE: rely, novalidate
alter table lineorder drop constraint pk_lineorder;
alter table supplier drop constraint pk_supplier;

-- indexes
drop index ix_part_clr;
create index INDEX_NAME ON TABLE_NAME (COLUMN_NAME);
create bitmap index ix_supplier_region on supplier(s_region); --note: bitmap index
create index ix_part_mfgr on part(p_mfgr);

-- sequences
drop sequence "SQ_TABLE_NAME_PK";
create sequence "SQ_TABLE_NAME_PK";

-- triggers
create or replace trigger "TG_TABLE_NAME_PK"
before insert on "TABLE_NAME"
for each row

begin
  select "SQ_TABLE_NAME_PK".nextval into :new."ID" from dual;
end;

-- parallel
alter table lineorder parallel 4;


-- partitioning/repartitionint
create table lineorder_cpy parallel 4 nologging
partition by range (lo_orderdate) interval (numtoyminterval(1, 'MONTH'))
(
  partition R199201 values less than (to_date('19920201', 'YYYYMMDD'))
)
as select * from lineorder
;

-- optimizer tricks
-- after equping relations with: rely tell the optimizer to trust data/keys
alter system set query_rewrite_integrity = trusted;

-- stats
begin
  dbms_stats.unlock_table_stats ('DW083', 'PART');

  dbms_stats.gather_table_stats('DW083', 'PART');
end;
/

-- in-memory stuff
alter table lineorder no inmemory; -- explicit no in-memory
--note: "memcompress for query" is here for perf
--note: "duplicate all" will ensure we load in all RAC instances on exadata
alter table lineorder inmemory memcompress for query duplicate all; -- define in-memory table
select /*+ full */ count(*) from lineorder_cpy; -- force load to memory
select * from gv$im_segments; -- check if data in-memory

show parameter inmemory_size;
show sga;


# export/import

exp username/password@oradb tables=CRM\$DOCUMENT_CUSTOMER indexes=n grants=n triggers=n direct=y statistics=none constraints=n buffer=4194304 file=document_customer.dmp
exp username/password@oradb tables=CRM\$PAYMENT indexes=n grants=n triggers=n direct=y statistics=none constraints=n buffer=4194304 file=payment.dmp

-- free table lock (kill session)
ALTER SYSTEM KILL SESSION '221, 3881';

SELECT SID, SERIAL# FROM V$SESSION WHERE SID IN (
    SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = 'TABLE_NAME'
);