DBA-Philiac

CREATE TABLE TBL_TXN ( USER_ID VARCHAR2(15 BYTE) NOT NULL, TRANS_DATE DATE NOT NULL, SERVICE_TYPE NUMBER NOT NULL ) PARTITION BY RANGE (TRANS_DATE) SUBPARTITION BY LIST(SERVICE_TYPE) SUBPARTITION TEMPLATE( SUBPARTITION type1 VALUES (1) TABLESPACE USERS, SUBPARTITION type2 VALUES (2) TABLESPACE USERS, SUBPARTITION type3 VALUES (3) TABLESPACE USERS) (PARTITION TXN_010311 VALUES LESS THAN (TO_DATE(' 2011-03-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE USERS, PARTITION TXN_020311 VALUES LESS THAN (TO_DATE(' 2011-03-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE USERS, PARTITION TXN_030311 VALUES LESS THAN (TO_DATE(' 2011-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE USERS ) alter table TBL_TXN add partition TXN_040311 VALUES LESS THAN (TO_DATE(' 2011-03-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', ...

-- create a tablespace for data CREATE TABLESPACE TBS_PART_TEST DATAFILE 'TBS_PART_TEST01.DBF' SIZE 10M AUTOEXTEND OFF, 'TBS_PART_TEST02.DBF' SIZE 10M AUTOEXTEND OFF LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; -- create a tablespace for index CREATE TABLESPACE TBS_PART_TEST_IND DATAFILE 'TBS_PART_TEST_IND01.DBF' SIZE 10M AUTOEXTEND OFF LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; -- creat a user to perform the activities CREATE USER PART IDENTIFIED BY VALUES '9725DA1D2FBD22FC' DEFAULT TABLESPACE TBS_PART_TEST TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- 4 Roles for PART GRANT SELECT_CATALOG_ROLE TO PART; GRANT DBA TO PART; GRANT RESOURCE TO PART; GRANT CONNECT TO PART; ALTER USER PART DEFAULT ROLE ALL; -- 1 System Privilege for PART GRANT UNLIMITED TABLESPACE T...