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