-- CATALOG Table Spaces
CREATE TABLESPACE DSW_CATALOGA DATAFILE 'DSW_CATALOGA.dat' SIZE 4G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 6G;
CREATE TABLESPACE DSW_CATALOGB DATAFILE 'DSW_CATALOGB.dat' SIZE 4G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 6G;

-- Core Tablespaces:
CREATE TABLESPACE DSW_CORE DATAFILE 'DSW_CORE.dat' SIZE 6G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
ALTER TABLESPACE DSW_CORE ADD DATAFILE 'DSW_CORE_2.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G;

CREATE TABLESPACE DSW_CORE_DCSPP_AMOUNT_INFO DATAFILE 'DSW_CORE_3.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
CREATE TABLESPACE DSW_CORE_DCSPP_ITEM DATAFILE 'DSW_CORE_4.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
CREATE TABLESPACE DSW_CORE_DCSPP_ORDER DATAFILE 'DSW_CORE_5.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
CREATE TABLESPACE DSW_CORE_DCSPP_PRICE_ADJUST DATAFILE 'DSW_CORE_6.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
CREATE TABLESPACE DSW_CORE_DCSPP_SHIPITEM_REL DATAFILE 'DSW_CORE_7.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
CREATE TABLESPACE DSW_CORE_DPS_USER DATAFILE 'DSW_CORE_8.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
CREATE TABLESPACE DSW_CORE_DSW_COMMERECE_ITEM DATAFILE 'DSW_CORE_9.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
CREATE TABLESPACE DSW_CORE_DSW_CONTACT_INFO DATAFILE 'DSW_CORE_A.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
CREATE TABLESPACE DSW_CORE_DSW_USER  DATAFILE 'DSW_CORE_B.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;


-- Merch Tablespaces:
CREATE TABLESPACE DSW_CA_MERCH DATAFILE 'DSW_CA_MERCH.dat' SIZE 3G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

-- Integration Tablespaces:
CREATE TABLESPACE DSW_CATFEED DATAFILE 'DSW_CATFEED.dat' SIZE 15G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;
ALTER  TABLESPACE DSW_CATFEED ADD DATAFILE 'DSW_CATFEED_2.dat' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 30G;


-- List all datafiles:
SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;


-- Create accounts:
-- (WE NEED YOU! PLEASE DO NOT GET FIRED FOR CREATING SECURITY HOLES BY SETTING TRIVIAL or WELL-KNOWN PASSWORDS!!!)

CREATE USER DSW_CATALOGA IDENTIFIED BY DSW_CATALOGA ;
GRANT "AUTHENTICATEDUSER" TO DSW_CATALOGA WITH ADMIN OPTION;
GRANT "CONNECT" TO DSW_CATALOGA WITH ADMIN OPTION;

CREATE USER DSW_CATALOGB IDENTIFIED BY DSW_CATALOGB ;
GRANT "AUTHENTICATEDUSER" TO DSW_CATALOGB WITH ADMIN OPTION;
GRANT "CONNECT" TO DSW_CATALOGB WITH ADMIN OPTION;

CREATE USER DSW_CORE IDENTIFIED BY DSW_CORE ;
GRANT "AUTHENTICATEDUSER" TO DSW_CORE WITH ADMIN OPTION;
GRANT "CONNECT" TO DSW_CORE WITH ADMIN OPTION;

CREATE USER DSW_CATFEED IDENTIFIED BY DSW_CATFEED ;
GRANT "AUTHENTICATEDUSER" TO DSW_CATFEED WITH ADMIN OPTION;
GRANT "CONNECT" TO DSW_CATFEED WITH ADMIN OPTION;

CREATE USER DSW_CA_MERCH IDENTIFIED BY DSW_CA_MERCH ;
GRANT "AUTHENTICATEDUSER" TO DSW_CA_MERCH WITH ADMIN OPTION;
GRANT "CONNECT" TO DSW_CA_MERCH WITH ADMIN OPTION;


-- Add roles (including some to avoid error with grants to QA accounts):
CREATE ROLE GIS_DSW_CATALOG;
CREATE ROLE GIS_DSW_CORE;
CREATE ROLE SA_ATG_MAINT;
CREATE ROLE DSW_CORE_ROLE;
CREATE ROLE INF_FULL;
CREATE ROLE ATG_MAINT_ROLE;
CREATE ROLE ATG_RO;
CREATE ROLE ATG_FULL;
CREATE ROLE QA1A_TEST;
CREATE ROLE BATCH_DSW_CORE;
CREATE ROLE PB350345;
CREATE ROLE MZ394924;

CREATE ROLE ATG_INVENTORY;
CREATE ROLE DLP_SCAN_ROLE;
CREATE ROLE SQLT_USER_ROLE;
CREATE ROLE DSW_CORE_TMP_ROLE;
CREATE ROLE BATCH_DSW_CATALOGA;
CREATE ROLE BATCH_DSW_CATALOGB;
CREATE ROLE DSW_CORE_MONITOR;


CREATE ROLE DSW_CATFEED_FULL;
CREATE ROLE DSW_CA_MERCH_RO;
CREATE ROLE DSW_CATFEED_RO;
CREATE ROLE KP_READ;
CREATE ROLE SK410976;
CREATE ROLE DSW_CATFEED_ROLE;

CREATE ROLE GB_ATGADMIN;
COMMIT;


CREATE USER SA_DSW_CATFEED_BATCH IDENTIFIED BY SA_DSW_CATFEED_BATCH ;
GRANT "AUTHENTICATEDUSER" TO SA_DSW_CATFEED_BATCH WITH ADMIN OPTION;
GRANT "CONNECT" TO SA_DSW_CATFEED_BATCH WITH ADMIN OPTION;

CREATE USER SA_DSW_CA_MERCH_BATCH IDENTIFIED BY SA_DSW_CA_MERCH_BATCH ;
GRANT "AUTHENTICATEDUSER" TO SA_DSW_CA_MERCH_BATCH WITH ADMIN OPTION;
GRANT "CONNECT" TO SA_DSW_CA_MERCH_BATCH WITH ADMIN OPTION;

CREATE USER SA_DSW_CORE_BATCH IDENTIFIED BY SA_DSW_CORE_BATCH ;
GRANT "AUTHENTICATEDUSER" TO SA_DSW_CORE_BATCH WITH ADMIN OPTION;
GRANT "CONNECT" TO SA_DSW_CORE_BATCH WITH ADMIN OPTION;

CREATE USER INF_INT IDENTIFIED BY INF_INT ;
GRANT "AUTHENTICATEDUSER" TO INF_INT WITH ADMIN OPTION;
GRANT "CONNECT" TO INF_INT WITH ADMIN OPTION;


-- Extra grants - not sure needed:
GRANT "DBA" TO DSW_CATALOGA WITH ADMIN OPTION;
GRANT "EXP_FULL_DATABASE" TO DSW_CATALOGA WITH ADMIN OPTION;
GRANT "IMP_FULL_DATABASE" TO DSW_CATALOGA WITH ADMIN OPTION;

-- Extra users - not sure needed:
CREATE USER SA_DSW_CATALOGA_BATCH IDENTIFIED BY SA_DSW_CATALOGA_BATCH ;
CREATE USER SA_DSW_CATALOGB_BATCH IDENTIFIED BY SA_DSW_CATALOGB_BATCH ;


-- Permissions:
GRANT SELECT ON dsw_core.dcs_inventory TO DSW_CATALOGA WITH GRANT OPTION;
GRANT SELECT ON dsw_core.dcs_inventory TO DSW_CATALOGB WITH GRANT OPTION;