Create index -- Parallel Option Syntax

SQL> create table t as select * from all_objects where rownum < 20;

Table created.

SQL> create index owner_idx on t(owner)
2 parallel (degree 8)
3 ;

Index created.

SQL> select index_name, degree from user_indexes where index_name='OWNER_%';

no rows selected

SQL> select index_name, degree from user_indexes;

INDEX_NAME DEGREE
------------------------------ ----------------------------------------
OWNER_IDX 8
SYS_C006454 1
SYS_C006455 1

SQL> alter index OWNER_IDX parallel (degree 1);

Index altered.

SQL> select index_name, degree from user_indexes;

INDEX_NAME DEGREE
------------------------------ ----------------------------------------
OWNER_IDX 1
SYS_C006454 1
SYS_C006455 1

ORA-01652: unable to extend temp segment by 512 in tablespace GEN_DATA

ISSUE

-----------

Trying to create a table by issuing the following statement:

Create table wh_receivable_sum_t1 as select * from wh_receivable_sum;

This is a large table with a lot of rows.

ORA-01652: unable to extend temp segment by 512 in tablespace GEN_DATA

SOLUTION

----------------

ORA-01652:	unable to extend temp segment by string in tablespace string
Cause:	Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
Action:	Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

( http://ora-01652.ora-code.com/ )

RESULTS

----------------

gen@PRD3S> select count(*) from wh_receivable_sum;

COUNT(*)
----------
9885692

gen@PRD3S> create table ds_t1 as select * from wh_receivable_sum;

Table created.

gen@PRD3S> select count(*) from ds_t1;

COUNT(*)
----------
9885692

gen@PRD3S> drop table ds_t1;

Table dropped.

Find tables exsiting in database A but not in database B in the same schema

repadmin@PRDQA> select table_name from dba_tables where owner='PRD'
minus
select table_name from dba_tables@prdp.world where owner='PRD';

TABLE_NAME
------------------------------
AUD$
PRD_AUDIT
PRD_AUDIT_SESSION

repadmin@PRDQA> select table_name from dba_tables@prdp.world where
owner='PRD'
minus
select table_name from dba_tables where owner='PRD';

TABLE_NAME
------------------------------
BK_PRDERAL_STATISTICS

Unregister database GENQA from RMAN catalog database

1. Obtain DBID of the database

   rman target / nocatalog
   Recovery Manager: Release 8.1.7.4.0 - Production
   RMAN-06005: connected to target database: GENQA (DBID=514069585)
   RMAN-06009: using target database controlfile instead of recovery catalog


2. List copies and backup sets recorded in the control file

   RMAN> list backup of database;


3. Issue change ... delete statement

   RMAN> allocate channel for maintenance type disk;
   RMAN> change backupset 989 delete;


4. Execute another list command to confirm that RMAN removed all backups

   Note: following two statements maybe executed:

   RMAN> crosscheck backupset;
   RMNA>  delete expired backupset;


5. Use SQL*Plus to connect to the recovery catalog database and execute the
following query in the recovery catalog to find the correct row of the DB table,
setting DB_ID equal to the value you obtained from step 1. For example, enter:

rman@CATALOGDB> select db_key, db_id from db where db_id=514069585;


DB_KEY      DB_ID
---------- ----------
4656927     514069585


6. While still connected to the recovery catalog, enter the following:

   rman@CATALOGDB> EXECUTE dbms_rcvcat.unregisterdatabase(4656927, 514069585);
   PL/SQL procedure successfully completed.

Issues in today's imp job

ISSUE A: rollback segment

Export file created by EXPORT:V08.01.07 via direct path
import done in WE8ISO8859P9 character set and WE8ISO8859P9 NCHAR character set
. importing GEN's objects into GEN
. . importing table "WH_RECEIVABLE_SUM"
...........................................................................
...........................................................................
...........................................................................
...............................................
IMP-00058: ORACLE error 1562 encountered
ORA-01562: failed to extend rollback segment number 20
ORA-01650: unable to extend rollback segment RBS26 by 512 in tablespace RBS
IMP-00028: partial import of previous table rolled back: 5489182 rows rolled back
Import terminated successfully with warnings.

Solution:
 add COMMIT=Y in the imp parameter file.

ISSUE B: unable to create INITIAL extent

CAUSE: when doing export, compress=N should be specified. (Default is Y)
SOLUTION:
Manually using DDL script to create the table, then imp with ignore=Y

IMP-00017: following statement failed with ORACLE error 1658:
"CREATE TABLE "WH_RECEIVABLE_SUM" ("RECEIVABLE_SUM_ID" NUMBER(15, 0) NOT NUL"
"L ENABLE, "RECEIVABLE_ID" NUMBER(9, 0) NOT NULL ENABLE, "ACCOUNT_ID" NUMBER"
"(9, 0), "PLANT_ID" NUMBER(9, 0), "MTD_DLR_INT_EARNED_AMT" NUMBER(14, 2), "M"
"TD_SUP_INT_EARNED_AMT" NUMBER(14, 2), "MTD_DLR_ITM_FEES_EARNED_AMT" NUMBER("
"14, 2), "MTD_SUP_ITM_FEES_EARNED_AMT" NUMBER(14, 2), "MTD_INS_EARNED_AMT" N"
"UMBER(14, 2), "MTD_SUP_DISCOUNT_EARNED_AMT" NUMBER(14, 2), "MTD_CHARGE_OFF_"
"AMT" NUMBER(14, 2), "MTD_RECOVERY_AMT" NUMBER(14, 2), "MTD_CASH_PRIN_PMT_AM"
"T" NUMBER(14, 2), "MTD_CRMEMO_PRIN_PMT_AMT" NUMBER(14, 2), "MTD_HIGHEST_CUR"
"R_BAL_AMT" NUMBER(14, 2), "MTD_CURR_BAL_AMT" NUMBER(14, 2), "MTD_ASSET_VALU"
"E_AMT" NUMBER(14, 2), "MTD_ADB_CURR_BAL_AMT" NUMBER(14, 2), "MTD_ADB_ASSET_"
"VALUE_AMT" NUMBER(14, 2), "PROCESS_DATE" DATE, "CREATE_DATE" DATE, "CREATE_"
"ID" VARCHAR2(20), "MOD_DATE" DATE, "MOD_ID" VARCHAR2(20), "LAST12_DLR_INT_E"
"ARNED_AMT" NUMBER(14, 2), "LAST12_SUP_INT_EARNED_AMT" NUMBER(14, 2), "LAST1"
"2_DLR_ITM_FEES_EARNED_AMT" NUMBER(14, 2), "LAST12_SUP_ITM_FEES_EARNED_AMT" "
"NUMBER(14, 2), "LAST12_INS_EARNED_AMT" NUMBER(14, 2), "LAST12_SUP_DISCOUNT_"
"EARNED_AMT" NUMBER(14, 2), "ITD_DLR_INT_EARNED_AMT" NUMBER(14, 2), "ITD_SUP"
"_INT_EARNED_AMT" NUMBER(14, 2), "ITD_DLR_ITM_FEES_EARNED_AMT" NUMBER(14, 2)"
", "ITD_SUP_ITM_FEES_EARNED_AMT" NUMBER(14, 2), "ITD_INS_EARNED_AMT" NUMBER("
"14, 2), "ITD_SUP_DISCOUNT_EARNED_AMT" NUMBER(14, 2), "YTD_DLR_INT_EARNED_AM"
"T" NUMBER(14, 2), "YTD_SUP_INT_EARNED_AMT" NUMBER(14, 2), "YTD_DLR_ITM_FEES"
"_EARNED_AMT" NUMBER(14, 2), "YTD_SUP_ITM_FEES_EARNED_AMT" NUMBER(14, 2), "Y"
"TD_INS_EARNED_AMT" NUMBER(14, 2), "YTD_SUP_DISCOUNT_EARNED_AMT" NUMBER(14, "
"2), "MTD_SUP_DISCOUNT_AMT" NUMBER(14, 2), "MTD_LAST_INT_BILL_DLR_AMT" NUMBE"
"R(14, 2), "MTD_LAST_INT_BILL_SUP_AMT" NUMBER(14, 2), "MTD_LAST_ITMFEES_BILL"
"_DLR_AMT" NUMBER(14, 2), "MTD_LAST_ITMFEES_BILL_SUP_AMT" NUMBER(14, 2), "MT"
"D_LAST_INS_BILL_AMT" NUMBER(14, 2), "MTD_LAST_INS_COST_AMT" NUMBER(14, 2), "
""MTD_TOTAL_INT_BILL_DLR_AMT" NUMBER(14, 2), "MTD_TOTAL_INT_BILL_SUP_AMT" NU"
"MBER(14, 2), "MTD_TOT_ITMFEES_BILL_DLR_AMT" NUMBER(14, 2), "MTD_TOT_ITMFEES"
"_BILL_SUP_AMT" NUMBER(14, 2), "MTD_TOT_INS_BILL_AMT" NUMBER(14, 2), "MTD_TO"
"T_INS_COST_AMT" NUMBER(14, 2), "MTD_UNEARNED_SUP_DISC_AMT" NUMBER(14, 2), ""
"MTD_UNEARNED_INT_DLR_AMT" NUMBER(14, 2), "MTD_UNEARNED_INT_SUP_AMT" NUMBER("
"14, 2), "LAST12_SUP_DISCOUNT_AMT" NUMBER(14, 2), "LAST12_LAST_INT_BILL_DLR_"
"AMT" NUMBER(14, 2), "LAST12_LAST_INT_BILL_SUP_AMT" NUMBER(14, 2), "LAST12_L"
"ASTITMFEES_BILLDLR_AMT" NUMBER(14, 2), "LAST12_LASTITMFEES_BILLSUP_AMT" NUM"
"BER(14, 2), "LAST12_LAST_INS_BILL_AMT" NUMBER(14, 2), "LAST12_LAST_INS_COST"
"_AMT" NUMBER(14, 2), "LAST12_TOT_INT_BILL_DLR_AMT" NUMBER(14, 2), "LAST12_T"
"OT_INT_BILL_SUP_AMT" NUMBER(14, 2), "LAST12_TOTITMFEES_BILLDLR_AMT" NUMBER("
"E, "CREATED_DATE" DATE, "TRIGGER_EVENT" VARCHAR2(15), "TABLE_NAME" VARCHAR2"
"(30), "RECORD_ID_1" VARCHAR2(30), "RECORD_ID_2" VARCHAR2(30), "PHOENIX_TABL"
"E_NAME" VARCHAR2(30), "VALUE_1" VARCHAR2(30), "VALUE_2" VARCHAR2(30), "VALU"
"E_3" VARCHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING S"
"TORAGE(INITIAL 1170522112 NEXT 81920 MINEXTENTS 1 MAXEXTENTS 2147483645 PCT"
"INCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "G"
"EN_DATA""
IMP-00003: ORACLE error 1658 encountered
ORA-01658: unable to create INITIAL extent for segment in tablespace GEN_DATA