Categotry Archives: 脚本

XDB is INVALID after ORA-31084 ORA-43853 errors during install

0

Posted on by

新安装的XDB组件状态是INVALID的,报下面的错:

SQL> set echo on
SQL> -- @catqm.sql    
SQL>
SQL> @?/rdbms/admin/catqm xdbpwd XDB TEMP NO
SQL> Rem
...
...

SQL> declare
  2    ACLXSD BFILE := dbms_metadata_hack.get_bfile('acl.xsd.11.2');
  3    ACLURL VARCHAR2(2000) := 'http://xmlns.oracle.com/xdb/acl.xsd';
  4  begin
  5  xdb.dbms_xmlschema.registerSchema(ACLURL, ACLXSD, FALSE, FALSE, FALSE, TRUE,
  6                                    FALSE, 'XDB',
  7                                   options => DBMS_XMLSCHEMA.REGISTER_BINARYXML);
  8  
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-31084: error while creating table "XDB"."XDB$ACL" for element "acl"
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "XDB"
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 37
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 65
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 136
ORA-06512: at line 5

SQL> 
SQL> -- Disable XRLS hierarchy priv check for xdb$acl and xdb$schema tables
SQL> BEGIN
  2     xdb.dbms_xdbz.disable_hierarchy('XDB', 'XDB$ACL');
  3     xdb.dbms_xdbz.disable_hierarchy('XDB', 'XDB$SCHEMA');
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-31061: XDB error: DBMS_XDBZ.DISABLE_HIERARCHY
ORA-06512: at "XDB.DBMS_XDBZ0", line 131
ORA-06512: at "XDB.DBMS_XDBZ0", line 802
ORA-29329: Table not of type XMLType
ORA-06512: at "XDB.DBMS_XDBZ", line 66
ORA-06512: at line 2

SQL> -- INSERT bootstrap AND root acl's
SQL> DECLARE
  2    b_abspath          VARCHAR2(200);
  3    b_data             VARCHAR2(2000);
...
 99     retbool := dbms_xdb.createresource(ro_abspath, ro_data);
100  END;
101  /
DECLARE
*
ERROR at line 1:
ORA-31114: XDB configuration has been deleted or is corrupted
ORA-06512: at "XDB.DBMS_XDB", line 174
ORA-06512: at line 96

...

查了MOS:

If COMPATIBLE is set to 11.2 or higher, XDB installation defaults to SecureFiles for XMLType storage regardless of the value of the 4th argument passed to catqm.sql (YES OR NO).

在11.2版本后,默认的表空间segment_space_management为AUTO

SQL> select tablespace_name, extent_management, segment_space_management
  2  from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN SEGMEN
--------------- ---------- ------
SYSTEM          LOCAL      MANUAL
SYSAUX          LOCAL      AUTO
UNDOTBS1        LOCAL      MANUAL
TEMP            LOCAL      MANUAL
USERS           LOCAL      AUTO
XDB             LOCAL      MANUAL

6 rows selected.

SQL> @?/rdbms/admin/catqm xdbpwd SYSAUX TEMP NO

 

 

拼出加分区表脚本

0

Posted on by

下面的脚本为新建2014年一年的分区。可根据不同的需求修改下。

sql:

select 'alter table ' || b.table_name || ' add partition PA_' ||
       TO_CHAR(a - 1, 'YYYYMM') ||
       
       ' values less than (TO_DATE( ''' ||
       TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'');'

  from (select add_months(trunc(DATE '2014-01-01', 'month'), level) a
          from dual
        connect by level < 13),
       (select owner || '.' || table_name table_name
          from dba_tables
         where table_name in ('T_WCP_FRZ_CREDENTIAL', 'T_WCP_ACCT_QUERY_CREDENTIAL')) b
union all
select 'alter table ' || c.owner||'.'||c.table_name
        ||
       ' add partition PA_MAX values less than (MAXVALUE);'
       
  from dual,(select owner, table_name
          from dba_tables
         where table_name in ('T_WCP_FRZ_CREDENTIAL','T_WCP_ACCT_QUERY_CREDENTIAL')) c

 order by 1

 

执行的结果:

alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201401 values less than (TO_DATE( '2014-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201402 values less than (TO_DATE( '2014-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201403 values less than (TO_DATE( '2014-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201404 values less than (TO_DATE( '2014-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201405 values less than (TO_DATE( '2014-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201406 values less than (TO_DATE( '2014-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201407 values less than (TO_DATE( '2014-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201408 values less than (TO_DATE( '2014-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201409 values less than (TO_DATE( '2014-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201410 values less than (TO_DATE( '2014-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201411 values less than (TO_DATE( '2014-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201412 values less than (TO_DATE( '2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_MAX values less than (MAXVALUE);
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201401 values less than (TO_DATE( '2014-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201402 values less than (TO_DATE( '2014-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201403 values less than (TO_DATE( '2014-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201404 values less than (TO_DATE( '2014-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201405 values less than (TO_DATE( '2014-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201406 values less than (TO_DATE( '2014-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201407 values less than (TO_DATE( '2014-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201408 values less than (TO_DATE( '2014-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201409 values less than (TO_DATE( '2014-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201410 values less than (TO_DATE( '2014-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201411 values less than (TO_DATE( '2014-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201412 values less than (TO_DATE( '2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_MAX values less than (MAXVALUE);

 

附件一个查找数据库中range分区的分区表(我们系统基本是时间的range分区,查找出来的表还需仔细核对,防止出现误执行):

select OWNER, TABLE_NAME
  from dba_part_tables c
 where c.partitioning_type = 'RANGE'
   and table_name not like 'BIN$%'
   and owner not in ('SYS',
                     'SYSTEM',
                     'DBSNMP',
                     'SYSMAN',
                     'OUTLN',
                     'MDSYS',
                     'ORDSYS',
                     'EXFSYS',
                     'DMSYS',
                     'WMSYS',
                     'CTXSYS',
                     'ANONYMOUS',
                     'XDB',
                     'ORDPLUGINS',
                     'OLAPSYS',
                     'PUBLIC')
 order by 1