Rem
Rem $Header: rdbms/admin/bug25792605_apply.sql /st_rdbms_12.2.0.1.0dbbp/4 2017/07/28 16:38:55 saratho Exp $
Rem
Rem bug25792605_apply.sql
Rem
Rem Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
Rem
Rem    NAME
Rem      bug25792605_apply.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: rdbms/admin/bug25792605_apply.sql
Rem    SQL_SHIPPED_FILE: rdbms/admin/bug25792605_apply.sql
Rem    SQL_PHASE: PATCH
Rem    SQL_STARTUP_MODE: NORMAL
Rem    SQL_IGNORABLE_ERRORS: NONE
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    saratho     07/19/17 - fix file metadata
Rem    saratho     04/21/17 - Bug 25928796: fix invalids seen after patch
Rem                           install
Rem    saratho     04/21/17 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

@@?/rdbms/admin/sqlsessstart.sql

--------------------- LOCAL_CHUNKS view and it's friends (for clients)

create or replace view GSMADMIN_INTERNAL.LOCAL_CHUNK_TYPES
  (TABFAM_ID, TABLE_NAME, SCHEMA_NAME,
    GROUP_TYPE, GROUP_COL_NUM,   --   number of columns in super key
    SHARD_TYPE, SHARD_COL_NUM,   --  number of columns in shard key
    DEF_VERSION,                 --  version of the shard definition
    SHARDGROUP_NAME
  )
as select
  TF.FAMILY_ID, GT.TABLE_NAME, GT.SCHEMA_NAME,
  decode(TF.PARTITION_SET_TYPE, 1, 'RANGE', 2, 'HASH', 4, 'LIST', 'NONE'),
  (select count(1) from SHARDKEY_COLUMNS PK where PK.FAMILY_ID = TF.FAMILY_ID and PK.KEY_LEVEL = 0),
  decode(TF.SHARD_TYPE, 1, 'RANGE', 2, 'HASH', 4, 'LIST', 'NONE'),
  (select count(1) from SHARDKEY_COLUMNS PK where PK.FAMILY_ID = TF.FAMILY_ID and PK.KEY_LEVEL = 1),
  D.ddlid, dbms_gsm_common.getDBParameterStr('_shardgroup_name', 1)
from
  GLOBAL_TABLE GT, TABLE_FAMILY TF, DDLID$ D
where GT.REF_TABLE_FLAG = 'R' AND 
  TF.FAMILY_ID = GT.FAMILY_ID
with read only
/

create or replace public synonym LOCAL_CHUNK_TYPES for GSMADMIN_INTERNAL.LOCAL_CHUNK_TYPES
/

create or replace view GSMADMIN_INTERNAL.LOCAL_CHUNKS
(
  CHUNK_NAME, SHARD_KEY_LOW, SHARD_KEY_HIGH, GROUP_KEY_LOW, GROUP_KEY_HIGH,
  PRIORITY, TABFAM_ID, GRP_ID, CHUNK_ID, STATE, SHARD_NAME, SHARDSPACE_NAME,
  INST_ID, CHUNK_UNIQUE_ID
)
as
with LOCAL_CHUNK_LOC as
  (SELECT CLX.REPLICA_NUM PRIORITY, NVL(CLX.SHARDSPACE_ID, 0) SSID,
    CLX.CHUNK_NUMBER CHUNK_NUMBER FROM CHUNK_LOC CLX
      WHERE CLX.DATABASE_NUM = dbms_gsm_common.getDBParameterNum('_db_num_gsm', 1)),
     LOCAL_CHUNK AS
  (SELECT NVL(C.SHARDSPACE_ID, 0) SHARDSPACE_ID, C.CHUNK_NUMBER CHUNK_NUMBER,
          C.BLOBOUNDVAL BLOBOUNDVAL, C.BHIBOUNDVAL BHIBOUNDVAL, C.STATE STATE,
          SS.NAME SHARD, C.PART_OBJ# PART_OBJECT_ID, C.CHUNK_ID CHUNK_UNIQUE_ID
     FROM CHUNKS C, SHARD_SPACE SS
    WHERE C.SHARDSPACE_ID = SS.SHARDSPACE_ID)
select
 'CHUNK_' || TO_CHAR(LC.SHARDSPACE_ID) || '_' || TO_CHAR(LC.CHUNK_NUMBER),
 LC.BLOBOUNDVAL, LC.BHIBOUNDVAL, PS.BLOBOUNDVAL, PS.BHIBOUNDVAL,
  (SELECT CL.PRIORITY FROM LOCAL_CHUNK_LOC CL
    WHERE LC.CHUNK_NUMBER = CL.CHUNK_NUMBER AND LC.SHARDSPACE_ID = CL.SSID
      AND ROWNUM < 2) PRIORITY,
 TF.FAMILY_ID, LC.SHARDSPACE_ID, LC.CHUNK_NUMBER, LC.STATE, LC.SHARD,
 LC.SHARD SHARDSPACE_NAME, CM.FUTURE_MASTER, LC.CHUNK_UNIQUE_ID
from
  (LOCAL_CHUNK LC cross join TABLE_FAMILY TF)
    left outer join PARTITION_SET PS on (PS.SHARDSPACE_ID = LC.SHARDSPACE_ID)
    left outer join GV$GCSPFMASTER_INFO CM on 
        (LC.PART_OBJECT_ID > 0 AND LC.PART_OBJECT_ID=CM.DATA_OBJECT_ID
            AND CM.INST_ID=USERENV('Instance'))
where
  LC.STATE in (0,1) --exclude chunks that are down
with read only
/


create or replace public synonym LOCAL_CHUNKS for GSMADMIN_INTERNAL.LOCAL_CHUNKS
/


-- Sharded databases
CREATE OR REPLACE VIEW gsmadmin_internal.sha_databases
   (db_unique_name, region_name, connect_string, db_created, status, version,
    rac_type, shardgroup, last_ddl, ddl_error, deployment_state, dg_broker_id,
    shardspace, db_up, is_primary, db_host, oracle_home
   )
AS SELECT
   d.name,
   r.name,
   d.connect_string,
   CASE d.conv_state
      WHEN 'S' THEN 'N'
      WHEN 'C' THEN 'Y' 
      ELSE 'UNKNOWN' END,
   CASE d.status
      WHEN 'U' THEN 'UNDEPLOYED'
      WHEN 'R' THEN 'REPLICATON_CONFIGURED'
      WHEN 'D' THEN 'GSM_SET_UP'
      WHEN 'I' THEN 'ADD_INCOMPLETE'
      WHEN 'S' THEN 'NEEDS_RESYNC'
      ELSE 'UNKNOWN' END,
   dbms_gsm_utility.dbVersRevLookup(d.version),
   CASE d.db_type
      WHEN 'N' THEN 'NON_RAC'
      WHEN 'A' THEN 'ADMIN_RAC'
      WHEN 'P' THEN 'POLICY_RAC'
      WHEN 'S' THEN 'SIHA'
      WHEN 'U' THEN 'UNKNOWN'
      ELSE 'UNKNOWN' END,
   s.name,
   d.ddl_num,
   d.ddl_error,
   CASE d.dpl_status
      WHEN 0 THEN 'NOT_DEPLOYED'
      WHEN 1 THEN 'DEPLOY_REQUESTED'
      WHEN 2 THEN 'REPLICATION_CONFIGURED'
      WHEN 3 THEN 'HAS_CHUNKS'
      WHEN 4 THEN 'DEPLOYED'
      WHEN 5 THEN 'OGG DEPLOYED'
      ELSE 'UNKNOWN' END,
   d.drset_number,
   ss.name,
   CASE BITAND(d.flags, 1)
      WHEN 1 THEN 'Y'
      ELSE 'N' END,
   CASE BITAND(d.flags,2)
      WHEN 2 THEN 'Y'
      ELSE 'N' END,
   v.hostname,
   d.oracle_home
FROM database d
   LEFT JOIN region r ON (d.region_num = r.num)
   LEFT JOIN shard_group s ON (d.shardgroup_id = s.shardgroup_id)
   LEFT JOIN shard_space ss ON (d.shardspace_id = ss.shardspace_id)
   LEFT JOIN vncr v ON (d.hostid = v.hostid);
/

create or replace public synonym SHA_DATABASES for GSMADMIN_INTERNAL.SHA_DATABASES
/

-- re-compile views just to be double shure they remain valid
ALTER VIEW GSMADMIN_INTERNAL.LOCAL_CHUNK_TYPES COMPILE
/

ALTER VIEW GSMADMIN_INTERNAL.LOCAL_CHUNKS COMPILE
/

ALTER VIEW gsmadmin_internal.sha_databases COMPILE
/

-- re-compile synonyms
ALTER PUBLIC SYNONYM LOCAL_CHUNK_TYPES  COMPILE
/

ALTER PUBLIC SYNONYM LOCAL_CHUNKS COMPILE
/

ALTER PUBLIC SYNONYM SHA_DATABASES COMPILE
/

@?/rdbms/admin/sqlsessend.sql
 
