126 lines
4.5 KiB
SQL
126 lines
4.5 KiB
SQL
-- pdb_attributes_setup.sql - Create pdb_attributes Table and merge_pdb_attributes Procedure for IOD PDBs Fleet Inventory
|
|
DEF 1 = 'C##IOD';
|
|
DECLARE
|
|
l_exists NUMBER;
|
|
l_sql_statement VARCHAR2(32767) := q'[
|
|
CREATE TABLE &&1..pdb_attributes (
|
|
-- /* key: version, db_domain, db_name, pdb_name */
|
|
version DATE NOT NULL
|
|
, db_domain VARCHAR2(64) NOT NULL
|
|
, db_name VARCHAR2(9) NOT NULL
|
|
, pdb_name VARCHAR2(30) NOT NULL
|
|
-- oci domains
|
|
, host_name VARCHAR2(64) NOT NULL
|
|
, realm_type VARCHAR2(1) NOT NULL
|
|
, realm_type_order_by NUMBER NOT NULL
|
|
, realm VARCHAR2(4) NOT NULL
|
|
, realm_order_by NUMBER NOT NULL
|
|
, region VARCHAR2(64) NOT NULL
|
|
, region_acronym VARCHAR2(4) NOT NULL
|
|
, region_order_by NUMBER NOT NULL
|
|
, locale VARCHAR2(4) NOT NULL
|
|
, locale_order_by NUMBER NOT NULL
|
|
, kiev_or_wf VARCHAR2(1) NULL
|
|
-- data attributes
|
|
, ez_connect_string VARCHAR2(256) NOT NULL
|
|
, total_size_bytes NUMBER NOT NULL
|
|
, sessions NUMBER NOT NULL
|
|
, avg_running_sessions NUMBER NULL
|
|
, created DATE NULL
|
|
, open_time DATE NULL
|
|
, timestamp DATE NOT NULL
|
|
)
|
|
PARTITION BY RANGE (version)
|
|
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
|
|
(
|
|
PARTITION before_2016_01_01 VALUES LESS THAN (TO_DATE('2016-01-01', 'YYYY-MM-DD'))
|
|
)
|
|
ROW STORE COMPRESS ADVANCED
|
|
TABLESPACE IOD
|
|
]';
|
|
l_sql_statement2 VARCHAR2(32767) := q'[
|
|
CREATE UNIQUE INDEX &&1..pdb_attributes_pk
|
|
ON &&1..pdb_attributes
|
|
(version, db_domain, db_name, pdb_name)
|
|
LOCAL
|
|
COMPRESS ADVANCED LOW
|
|
TABLESPACE IOD
|
|
]';
|
|
l_sql_statement3 VARCHAR2(32767) := q'[
|
|
ALTER TABLE &&1..pdb_attributes ADD PRIMARY KEY
|
|
(version, db_domain, db_name, pdb_name)
|
|
USING INDEX &&1..pdb_attributes_pk
|
|
]';
|
|
BEGIN
|
|
SELECT COUNT(*) INTO l_exists FROM dba_tables WHERE owner = UPPER(TRIM('&&1.')) AND table_name = UPPER('pdb_attributes');
|
|
IF l_exists = 0 THEN
|
|
EXECUTE IMMEDIATE l_sql_statement;
|
|
END IF;
|
|
SELECT COUNT(*) INTO l_exists FROM dba_indexes WHERE owner = UPPER(TRIM('&&1.')) AND index_name = UPPER('pdb_attributes_pk');
|
|
IF l_exists = 0 THEN
|
|
EXECUTE IMMEDIATE l_sql_statement2;
|
|
EXECUTE IMMEDIATE l_sql_statement3;
|
|
END IF;
|
|
END;
|
|
/
|
|
|
|
CREATE OR REPLACE
|
|
PROCEDURE c##iod.merge_pdb_attributes (
|
|
p_version IN VARCHAR2
|
|
, p_timestamp IN VARCHAR2
|
|
, p_db_domain IN VARCHAR2
|
|
, p_db_name IN VARCHAR2
|
|
, p_pdb_name IN VARCHAR2
|
|
, p_host_name IN VARCHAR2
|
|
, p_realm_type IN VARCHAR2
|
|
, p_realm_type_order_by IN VARCHAR2
|
|
, p_realm IN VARCHAR2
|
|
, p_realm_order_by IN VARCHAR2
|
|
, p_region IN VARCHAR2
|
|
, p_region_acronym IN VARCHAR2
|
|
, p_region_order_by IN VARCHAR2
|
|
, p_locale IN VARCHAR2
|
|
, p_locale_order_by IN VARCHAR2
|
|
, p_kiev_or_wf IN VARCHAR2
|
|
, p_ez_connect_string IN VARCHAR2
|
|
, p_total_size_bytes IN VARCHAR2
|
|
, p_sessions IN VARCHAR2
|
|
, p_avg_running_sessions IN VARCHAR2
|
|
, p_created IN VARCHAR2
|
|
, p_open_time IN VARCHAR2
|
|
)
|
|
IS
|
|
r c##iod.pdb_attributes%ROWTYPE;
|
|
BEGIN
|
|
EXECUTE IMMEDIATE q'[ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS']';
|
|
--
|
|
r.version := p_version;
|
|
r.db_domain := p_db_domain;
|
|
r.db_name := p_db_name;
|
|
r.pdb_name := p_pdb_name;
|
|
r.host_name := p_host_name;
|
|
r.realm_type := p_realm_type;
|
|
r.realm_type_order_by := p_realm_type_order_by;
|
|
r.realm := p_realm;
|
|
r.realm_order_by := p_realm_order_by;
|
|
r.region := p_region;
|
|
r.region_acronym := p_region_acronym;
|
|
r.region_order_by := p_region_order_by;
|
|
r.locale := p_locale;
|
|
r.locale_order_by := p_locale_order_by;
|
|
r.kiev_or_wf := p_kiev_or_wf;
|
|
r.ez_connect_string := p_ez_connect_string;
|
|
r.total_size_bytes := p_total_size_bytes;
|
|
r.sessions := p_sessions;
|
|
r.avg_running_sessions := p_avg_running_sessions;
|
|
r.created := p_created;
|
|
r.open_time := p_open_time;
|
|
r.timestamp := p_timestamp;
|
|
--
|
|
DELETE c##iod.pdb_attributes WHERE version = r.version /*AND host_name = r.host_name*/ AND db_domain = r.db_domain AND db_name = r.db_name AND pdb_name = r.pdb_name;
|
|
INSERT INTO c##iod.pdb_attributes VALUES r;
|
|
COMMIT;
|
|
END merge_pdb_attributes;
|
|
/
|
|
SHOW ERRORS;
|