Create Table - SGajre/Tutorial GitHub Wiki

CRMR Stress PV Full Reval Batch: This document guide DB related queries and troubleshooting application. Create Table Space: CREATE TABLESPACE "RCAST_GSST" datafile '/optware/oradata/rcastgsst_1.dbf' SIZE 175G LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

Create Tables: --TRANSACTIONAL DATABASE

---

-- DDL TO DROP TABLES

DROP TABLE RCAST_STRESS_PV_ATTRIBUTE; DROP TABLE RCAST_STRESS_PV; DROP TABLE RCAST_STRESS_SCENARIO_FEED; DROP TABLE RCAST_STRESS_SCENARIO; DROP TABLE RCAST_STRESS_BATCH_AUDIT;

--Static table DROP TABLE RCAST_STRESS_FEED;

--DROP Indexes DROP INDEX RCAST_STRESS_PV_ATTR_IDX; DROP INDEX RCAST_STRESS_PV_IDX;

-- DDL for Table RCAST_STRESS_FEED

CREATE TABLE RCAST_STRESS_FEED( FEED_CODE VARCHAR2 (8) NOT NULL, FILE_NAME VARCHAR2 (100) NOT NULL, FEED_TYPE VARCHAR2 (30) NOT NULL, FEEDING_SYSTEM VARCHAR2 (30) NOT NULL, CALCULATE_PL CHAR (1) DEFAULT 'Y' NOT NULL, CONTACT_INFO VARCHAR2(100 BYTE), EFFECTIVE_DATE DATE NOT NULL, EXPIRATION_DATE DATE NOT NULL, EXPECTED_TIME NUMBER NOT NULL, CUTOFF_TIME NUMBER NOT NULL, CREATED_BY VARCHAR(10) NOT NULL, CREATED_TIME DATE NOT NULL, UPDATED_BY VARCHAR(10), UPDATED_TIME DATE DEFAULT SYSDATE, PRIMARY KEY (FEED_CODE) )TABLESPACE RCAST_GSST;

COMMENT ON TABLE RCAST_STRESS_FEED IS 'CRMR GSST Full Reval Batch (sk14882, sg45637)';

---

-- DDL for Table RCAST_STRESS_SCENARIO_FEED

CREATE TABLE RCAST_STRESS_SCENARIO_FEED( SCENARIO_FEED_ID NUMBER NOT NULL , FEED_CODE VARCHAR2 (8) NOT NULL, FEED_FILE VARCHAR2 (100) NOT NULL, SEQUENCE_NO VARCHAR2 (10) NOT NULL, VERSION_NO VARCHAR2 (10) NOT NULL, AS_OF_DATE DATE NOT NULL, SCENARIO_RUN_ID VARCHAR2 (20) NULL, SCENARIO_ID VARCHAR2 (20) NOT NULL, SCENARIO_VERSION NUMBER NOT NULL, SCENARIO_NAME VARCHAR2 (50) NOT NULL, STRESS_TEST_TYPE VARCHAR2 (30) NULL, RECORD_COUNT NUMBER NOT NULL, CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(SCENARIO_FEED_ID), CONSTRAINT FK_FEED_CODE FOREIGN KEY(FEED_CODE) REFERENCES RCAST_STRESS_FEED (FEED_CODE) ON DELETE CASCADE )TABLESPACE RCAST_GSST;

COMMENT ON TABLE RCAST_STRESS_SCENARIO_FEED IS 'CRMR GSST Full Reval Batch (sk14882, sg45637)';

---

-- DDL for Table RCAST_STRESS_PV

CREATE TABLE RCAST_STRESS_PV ( STRESS_PV_ID NUMBER NOT NULL, SCENARIO_FEED_ID NUMBER NOT NULL, ORG_UNIT VARCHAR2 (18) NOT NULL, UNIQUE_ID VARCHAR2 (50) NOT NULL, UIT_ID VARCHAR2 (100) NULL, UIP_ID VARCHAR2 (100) NULL, MODEL_ID VARCHAR2 (100) NULL, PRIMARY_MODEL_ID VARCHAR2 (100) NULL, POSITION_UNS_PV NUMBER NOT NULL, POSITION_S_PV NUMBER NOT NULL, NO_OF_ATTRIBUTIONS NUMBER NOT NULL, AS_OF_DATE DATE NULL, CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (STRESS_PV_ID), CONSTRAINT FK_SCENARIO_FEED_ID FOREIGN KEY (SCENARIO_FEED_ID) REFERENCES RCAST_STRESS_SCENARIO_FEED (SCENARIO_FEED_ID) ON DELETE CASCADE ) PARTITION BY RANGE (AS_OF_DATE ) INTERVAL (Numtodsinterval(7,'day')) ( PARTITION RCAST_STRESS_PV_0 VALUES LESS THAN ('01-JAN-2016')) TABLESPACE RCAST_GSST;

COMMENT ON TABLE RCAST_STRESS_PV IS 'CRMR GSST Full Reval Batch (sk14882, sg45637)';

CREATE INDEX "OCFGMR_P"."RCAST_STRESS_PV_IDX" ON "OCFGMR_P"."RCAST_STRESS_PV" ("SCENARIO_FEED_ID") TABLESPACE RCAST_GSST;

-- DDL for Table RCAST_STRESS_PV_ATTRIBUTE

CREATE TABLE RCAST_STRESS_PV_ATTRIBUTE ( ATTRIBUTE_ID NUMBER NOT NULL, STRESS_PV_ID NUMBER NOT NULL, RISK_BUCKET_NAME VARCHAR2(120) NOT NULL, CALCULATED CHAR(1) NOT NULL, BUCKET_STRESS_PV NUMBER, BUCKET_STRESS_PNL NUMBER NULL, AS_OF_DATE DATE NULL, CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(ATTRIBUTE_ID), CONSTRAINT FK_STRESS_PV_ID FOREIGN KEY(STRESS_PV_ID) REFERENCES RCAST_STRESS_PV(STRESS_PV_ID) ON DELETE CASCADE ) PARTITION BY RANGE (AS_OF_DATE) INTERVAL (Numtodsinterval(7,'day')) (PARTITION RCAST_STRESS_PV_ATT_0 VALUES LESS THAN ('01-JAN-2016')) TABLESPACE RCAST_GSST;

COMMENT ON TABLE RCAST_STRESS_PV_ATTRIBUTE IS 'CRMR GSST Full Reval Batch (sk14882, sg45637)';

CREATE INDEX "OCFGMR_P"."RCAST_STRESS_PV_ATTR_IDX" ON "OCFGMR_P"."RCAST_STRESS_PV_ATTRIBUTE" ("STRESS_PV_ID") TABLESPACE RCAST_GSST;

-- DDL for Table RCAST_STRESS_BATCH_AUDIT

CREATE TABLE RCAST_STRESS_BATCH_AUDIT ( LOG_ID INTEGER NOT NULL, AS_OF DATE NOT NULL, FEED_FILE VARCHAR2(100) NOT NULL, STATUS VARCHAR2(10) NOT NULL, DESCRIPTION VARCHAR2(512) NOT NULL, INBOUND_LOC VARCHAR2(255) NULL, OUTBOUND_LOC VARCHAR2(255) NULL, TIME_STAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(LOG_ID) ) TABLESPACE RCAST_GSST;

COMMENT ON TABLE RCAST_STRESS_BATCH_AUDIT IS 'CRMR GSST Full Reval Batch (sk14882, sg45637)';

---

-- DDL for Table RCAST_STRESS_BATCH_AUDIT

CREATE TABLE RCAST_STRESS_SCENARIO ( SCENARIO_ID VARCHAR2(20 byte) NOT NULL enable, SCENARIO_NAME VARCHAR2(100 byte) NOT NULL enable, SCENARIO_VERSION NUMBER NOT NULL enable, COB_DATE DATE NOT NULL enable, SCENARIO_STRESS_TYPE VARCHAR2(30 byte), CREATED_BY VARCHAR2(30 BYTE) DEFAULT USER, CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (SCENARIO_ID) ) TABLESPACE RCAST_GSST;

COMMENT ON TABLE RCAST_STRESS_SCENARIO IS 'CRMR GSST Full Reval Batch (sk14882, sg45637)'; Create Indexes: CREATE INDEX "OCFGMR_P"."RCAST_STRESS_PV_ATTR_IDX" ON "OCFGMR_P"."RCAST_STRESS_PV_ATTRIBUTE" ("STRESS_PV_ID") TABLESPACE RCAST_GSST;

Create Sequences: DROP SEQUENCE RCAST_STRESS_SCENARIO_FEED_SEQ; DROP SEQUENCE RCAST_STRESS_PV_SEQ; DROP SEQUENCE RCAST_STRESS_PV_ATTRIBUTE_SEQ; DROP SEQUENCE RCAST_STRESS_BATCH_AUDIT_SEQ;

CREATE SEQUENCE RCAST_STRESS_SCENARIO_FEED_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 1000 NOORDER
NOCYCLE ;

CREATE SEQUENCE RCAST_STRESS_PV_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 5000 NOORDER
NOCYCLE;

CREATE SEQUENCE RCAST_STRESS_PV_ATTRIBUTE_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10000 NOORDER
NOCYCLE;

CREATE SEQUENCE RCAST_STRESS_BATCH_AUDIT_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 1000 NOORDER
NOCYCLE; Grants: DROP PUBLIC SYNONYM RCAST_STRESS_BATCH_AUDIT; DROP PUBLIC SYNONYM RCAST_STRESS_FEED; DROP PUBLIC SYNONYM RCAST_STRESS_PV; DROP PUBLIC SYNONYM RCAST_STRESS_PV_ATTRIBUTE; DROP PUBLIC SYNONYM RCAST_STRESS_SCENARIO; DROP PUBLIC SYNONYM RCAST_STRESS_SCENARIO_FEED;

DROP PUBLIC SYNONYM RCAST_STRESS_SCENARIO_FEED_SEQ; DROP PUBLIC SYNONYM RCAST_STRESS_PV_SEQ; DROP PUBLIC SYNONYM RCAST_STRESS_PV_ATTRIBUTE_SEQ; DROP PUBLIC SYNONYM RCAST_STRESS_BATCH_AUDIT_SEQ;

-- GRANT ACCESS FOR RCAST_STRESS_FEED CREATE PUBLIC SYNONYM RCAST_STRESS_FEED For RCAST_STRESS_FEED; GRANT SELECT, INSERT, UPDATE, DELETE ON RCAST_STRESS_FEED TO GMR_BATCH;

-- GRANT ACCESS FOR RCAST_STRESS_PV CREATE PUBLIC SYNONYM RCAST_STRESS_PV FOR RCAST_STRESS_PV; GRANT SELECT, INSERT, UPDATE, DELETE ON RCAST_STRESS_PV TO GMR_BATCH;

-- GRANT ACCESS FOR RCAST_STRESS_PV_ATTRIBUTE CREATE PUBLIC SYNONYM RCAST_STRESS_PV_ATTRIBUTE FOR RCAST_STRESS_PV_ATTRIBUTE; GRANT SELECT, INSERT, UPDATE, DELETE ON RCAST_STRESS_PV_ATTRIBUTE TO GMR_BATCH;

-- GRANT ACCESS FOR RCAST_STRESS_SCENARIO CREATE PUBLIC SYNONYM RCAST_STRESS_SCENARIO FOR RCAST_STRESS_SCENARIO; GRANT SELECT, INSERT, UPDATE, DELETE ON RCAST_STRESS_SCENARIO TO GMR_BATCH;

-- GRANT ACCESS FOR RCAST_STRESS_SCENARIO_FEED CREATE PUBLIC SYNONYM RCAST_STRESS_SCENARIO_FEED FOR RCAST_STRESS_SCENARIO_FEED; GRANT SELECT, INSERT, UPDATE, DELETE ON RCAST_STRESS_SCENARIO_FEED TO GMR_BATCH;

-- GRANT ACCESS FOR RCAST_STRESS_BATCH_AUDIT CREATE PUBLIC SYNONYM RCAST_STRESS_BATCH_AUDIT FOR RCAST_STRESS_BATCH_AUDIT; GRANT SELECT,ALTER ON RCAST_STRESS_BATCH_AUDIT TO GMR_BATCH;

-- GRANT SEQUENCE ACCESS FOR RCAST_STRESS_SCENARIO_FEED_SEQ CREATE PUBLIC SYNONYM RCAST_STRESS_SCENARIO_FEED_SEQ FOR RCAST_STRESS_SCENARIO_FEED_SEQ; GRANT SELECT,ALTER ON RCAST_STRESS_SCENARIO_FEED_SEQ TO GMR_BATCH;

-- GRANT SEQUENCE ACCESS FOR RCAST_STRESS_PV_SEQ CREATE PUBLIC SYNONYM RCAST_STRESS_PV_SEQ FOR RCAST_STRESS_PV_SEQ; GRANT SELECT,ALTER ON RCAST_STRESS_PV_SEQ TO GMR_BATCH;

-- GRANT SEQUENCE ACCESS FOR RCAST_STRESS_PV_ATTRIBUTE_SEQ CREATE PUBLIC SYNONYM RCAST_STRESS_PV_ATTRIBUTE_SEQ FOR RCAST_STRESS_PV_ATTRIBUTE_SEQ; GRANT SELECT,ALTER ON RCAST_STRESS_PV_ATTRIBUTE_SEQ TO GMR_BATCH;

-- GRANT SEQUENCE ACCESS FOR RCAST_STRESS_BATCH_AUDIT_SEQ CREATE PUBLIC SYNONYM RCAST_STRESS_BATCH_AUDIT_SEQ FOR RCAST_STRESS_BATCH_AUDIT_SEQ; GRANT SELECT,ALTER ON RCAST_STRESS_BATCH_AUDIT_SEQ TO GMR_BATCH; Truncate Tables: --Check Constraints names: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'RCAST_STRESS_PV_ATTRIBUTE'; SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'RCAST_STRESS_PV'; SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'RCAST_STRESS_SCENARIO_FEED';

--Truncate Tables ALTER TABLE RCAST_STRESS_PV_ATTRIBUTE disable CONSTRAINT FK_STRESS_PV_ID; ALTER TABLE RCAST_STRESS_PV disable CONSTRAINT FK_SCENARIO_FEED_ID; ALTER TABLE RCAST_STRESS_SCENARIO_FEED disable CONSTRAINT FK_FEED_CODE;

TRUNCATE TABLE RCAST_STRESS_PV_ATTRIBUTE ; TRUNCATE TABLE RCAST_STRESS_PV; TRUNCATE TABLE RCAST_STRESS_SCENARIO_FEED; TRUNCATE TABLE RCAST_STRESS_BATCH_AUDIT;

ALTER TABLE RCAST_STRESS_PV_ATTRIBUTE ENABLE CONSTRAINT FK_STRESS_PV_ID; ALTER TABLE RCAST_STRESS_PV ENABLE CONSTRAINT FK_SCENARIO_FEED_ID; ALTER TABLE RCAST_STRESS_SCENARIO_FEED ENABLE CONSTRAINT FK_FEED_CODE; Drop Partition: SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where TABLE_NAME like 'RCAST%' ORDER BY table_name, partition_name; Part 1 --Disable the Contraint before droping Indexes ALTER TABLE RCAST_STRESS_PV_ATTRIBUTE disable CONSTRAINT FK_STRESS_PV_ID; ALTER TABLE RCAST_STRESS_PV disable CONSTRAINT FK_SCENARIO_FEED_ID; ALTER TABLE RCAST_STRESS_SCENARIO_FEED disable CONSTRAINT FK_FEED_CODE;

ALTER TABLE RCAST_STRESS_PV_ATTRIBUTE DROP PARTITION partition_name1; ALTER TABLE RCAST_STRESS_PV_ATTRIBUTE DROP PARTITION partition_name2; ALTER TABLE RCAST_STRESS_PV_ATTRIBUTE DROP PARTITION partition_name3; ALTER TABLE RCAST_STRESS_PV DROP PARTITION partition_name5; ALTER TABLE RCAST_STRESS_PV DROP PARTITION partition_name10;

ALTER TABLE RCAST_STRESS_PV_ATTRIBUTE ENABLE CONSTRAINT FK_STRESS_PV_ID; ALTER TABLE RCAST_STRESS_PV ENABLE CONSTRAINT FK_SCENARIO_FEED_ID; ALTER TABLE RCAST_STRESS_SCENARIO_FEED ENABLE CONSTRAINT FK_FEED_CODE; Part2: Exception: Caused by: java.sql.SQLException: ORA-01502: index 'OCFGMR_P.SYS_C0013561700' or partition of such index is in unusable state --Check unusable index after partition drop SELECT * from USER_INDEXES WHERE STATUS = 'UNUSABLE';

ALTER INDEX SYS_C0013561700 REBUILD INVALIDATE; ALTER INDEX RCAST_STRESS_PV_IDX REBUILD INVALIDATE; ALTER INDEX SYS_C0013561706 REBUILD INVALIDATE; ALTER INDEX RCAST_STRESS_PV_ATTR_IDX REBUILD INVALIDATE;

Note: Each index takes 20 -30 mins and INVALIDATE is Optional – this reduces the time

Move Table Space: ALTER TABLE RCAST_STRESS_PV_ATTRIBUTE MOVE TABLESPACE RCAST_GSST; ALTER TABLE RCAST_STRESS_PV MOVE TABLESPACE RCAST_GSST; ALTER TABLE RCAST_STRESS_SCENARIO_FEED MOVE TABLESPACE RCAST_GSST; ALTER TABLE RCAST_STRESS_BATCH_AUDIT MOVE TABLESPACE RCAST_GSST; ALTER TABLE MOVE TABLESPACE RCAST_GSST MOVE TABLESPACE RCAST_GSST;

Alter Index

Easy way: Drop the indexes Re-Create indexed with tablespace Rebuild indexes with tablespace and invalidate

Look Up Deadlock

-- Look Up Deadlock

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal, b.object_id,substr(b.object_name,1,40) object_name from v$session a, dba_objects b, v$locked_object c where a.sid = c.session_id and b.object_id = c.object_id order by 1

---

-- GET the tablespace

SELECT b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb FROM ( SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024 ,2) AS free_space FROM dba_free_space GROUP BY tablespace_name ) a, ( SELECT tablespace_name, SUM(bytes)/1024/1024 AS tbs_size FROM dba_data_files GROUP BY tablespace_name )b WHERE a.tablespace_name(+)=b.tablespace_name ORDER BY FreeMb DESC;

---

-- Current Session i.e. Connections

SELECT COUNT() FROM v$session; -- Currently running against total connection SELECT 'Currently, ' || (SELECT COUNT() FROM V$SESSION) || ' out of ' || VP.VALUE || ' connections are used.' AS USAGE_MESSAGE FROM V$PARAMETER VP WHERE VP.NAME = 'sessions' ;

--===================================================================================== --TABLE SPACE --=====================================================================================

-- Move Tables to other tablespace and fix Unuable and partition issue

alter table RCAST_STRESS_FEED move tablespace GMRFS; --alter index RCAST_STRESS_FEED_IDX rebuild tablespace GMRFS;

alter table RCAST_STRESS_PV move tablespace GMRFS; alter index OCFGMR_P.RCAST_STRESS_PV_IDX rebuild tablespace GMRFS;

alter table RCAST_STRESS_SCENARIO_FEED move tablespace GMRFS; alter index OCFGMR_P.RCAST_STRESS_SCENARIO_FEED_IDX rebuild tablespace GMRFS;

alter table RCAST_STRESS_PV_ATTRIBUTE move tablespace GMRFS; alter index OCFGMR_P.RCAST_STRESS_PV_ATTRIBUTE_IDX rebuild tablespace GMRFS;

---

-- Move Tables to other tablespace and fix Unuable and partition issue

SELECT owner, index_name, tablespace_name from DBA_INDEXES WHERE status = 'UNUSABLE';

SELECT 'alter index '||index_name||' rebuild tablespace '||tablespace_name ||';' from DBA_INDEXES where STATUS = 'UNUSABLE';

SELECT index_owner, index_name, partition_name, tablespace_name from DBA_IND_PARTITIONS WHERE status = 'UNUSABLE';

SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' from DBA_IND_PARTITIONS WHERE status = 'UNUSABLE';

--===================================================================================== select distinct TABLE_NAME, TABLESPACE_NAME from ALL_TABLES where TABLESPACE_NAME = 'RCAST_GSST_P' order by TABLE_NAME;

ALTER SEQUENCE RCAST_STRESS_PV_ATTRIBUTE_SEQ CACHE 5000; ALTER SEQUENCE RCAST_STRESS_BATCH_AUDIT_SEQ CACHE 1000; ALTER SEQUENCE RCAST_STRESS_PV_SEQ CACHE 5000; ALTER SEQUENCE RCAST_STRESS_SCENARIO_FEED_SEQ CACHE 1000