76 lines
1.7 KiB
SQL
76 lines
1.7 KiB
SQL
DEFINE tbl_data = t24dwh_data
|
|
DEFINE tbl_idx = t24dwh_index
|
|
DEFINE tiny = 'initial 32K next 64K pctincrease 0'
|
|
DEFINE small = 'initial 64K next 128K pctincrease 0'
|
|
DEFINE normal = 'initial 512K next 2M pctincrease 0'
|
|
DEFINE large = 'initial 2M next 16M pctincrease 0'
|
|
DEFINE owner = 'T24DWH'
|
|
|
|
PROMPT Create External Tables
|
|
|
|
CREATE TABLE t_stmt_dist_code_ext
|
|
(
|
|
record_status VARCHAR2(1),
|
|
stmt_dist_code VARCHAR2(2),
|
|
stmt_dist_desc VARCHAR2(100)
|
|
)
|
|
organization external
|
|
(
|
|
type ORACLE_LOADER
|
|
default directory T24DWH_FILES
|
|
access parameters
|
|
(
|
|
RECORDS DELIMITED BY NEWLINE
|
|
CHARACTERSET WE8MSWIN1252
|
|
STRING SIZES ARE IN BYTES
|
|
NOBADFILE
|
|
NODISCARDFILE
|
|
NOLOGFILE
|
|
FIELDS
|
|
TERMINATED BY ','
|
|
OPTIONALLY ENCLOSED BY '|' AND '|'
|
|
NOTRIM
|
|
(
|
|
"RECORD_STATUS" CHAR,
|
|
"STMT_DIST_CODE" CHAR,
|
|
"STMT_DIST_DESC" CHAR
|
|
)
|
|
)
|
|
location (T24DWH_FILES:'T_STMT_DIST_CODE.TXT')
|
|
)
|
|
reject limit UNLIMITED
|
|
/
|
|
|
|
PROMPT Create tables and indexes
|
|
|
|
CREATE TABLE t_stmt_dist_code
|
|
(
|
|
record_status VARCHAR2(1),
|
|
stmt_dist_code VARCHAR2(2) not null,
|
|
stmt_dist_desc VARCHAR2(100)
|
|
)
|
|
TABLESPACE &tbl_data
|
|
STORAGE (&tiny)
|
|
/
|
|
|
|
-- Create/Recreate primary, unique and foreign key constraints
|
|
ALTER TABLE t_stmt_dist_code
|
|
ADD ( CONSTRAINT pk_stmt_dist_code PRIMARY KEY (stmt_dist_code)
|
|
USING INDEX
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&tiny))
|
|
/
|
|
|
|
CREATE OR REPLACE PUBLIC SYNONYM syn_stmt_dist_code
|
|
FOR &owner..t_stmt_dist_code;
|
|
|
|
-- Grant/Revoke object privileges
|
|
GRANT SELECT ON t_stmt_dist_code TO T24MIS;
|
|
GRANT SELECT ON t_stmt_dist_code TO WSTMT;
|
|
|
|
|
|
CREATE INDEX ix_account_2
|
|
ON t_account (branch_code, product_code)
|
|
TABLESPACE &tbl_idx
|
|
STORAGE (&large)
|
|
/ |