ORA-39083 ORA-02063 Object type VIEW failed to create with error

ORA-39083: Object type VIEW failed to create with error:
ORA-02063: preceding 5 lines from HSBC26

face above error during impdp? this means your impdp tries to import the statistics to an remote database through an dblink.


Solution:

exclude=STATISTICS while expdp or impdp

or

set HS_FDS_SUPPORT_STATISTICS=FALSE in the init.ora

Note: In my case, I hit this error, when impdp is trying to create a view, which has a base table on remote database. 


impdp log snippet:

Failing sql is:
CREATE  FORCE VIEW "PLOANS"."CLIENT_TYPE" ("ITEM_CODE") AS select "SYMBOL_CODE" from  CUST_001.CLIENT_SUMMARY@HSBC26

ORA-39083: Object type VIEW failed to create with error:
ORA-02063: preceding 5 lines from HSBC26

how to tar a directory [ tested ] Linux

### Tar and compress a directory: 

[ includes sub-directories and files ]

For example: 

[oracle@oradb1 ~]$ echo $ORACLE_HOME 
/u01/app/product/11.2.0/dbhome_1

[oracle@oradb1 ~]$ cd /u01

[oracle@oradb1 u01]$ tar -pczf OH_backup.tar.gz app/



### Untar and uncompress:

[oracle@oradb1 ~]$ cd /u01

[oracle@oradb1 u01]$ tar xvfz OH_backup.tar.gz



### Tar multiple files:

[oracle@oradb1 bkp] tar -pczf dump2012.tar.gz psm_full.dmp psm_full.log qsm_full.dmp qsm_full.log 



### How do I untar ?

[oracle@oradb1 bkp] tar xvfz dump2012.tar.gz

how to move datafiles in oracle 11g

Move or Rename datafiles: Steps same in 10g or 11g

C:\> rman target /

RMAN> REPORT SCHEMA;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace   RB segs Datafile Name
---- -------- ------------ ------- ------------------------
1    1320     SYSTEM       ***     D:\ORADATA\HSBC\SYSTEM01.DBF
3    860      SYSAUX       ***     D:\ORADATA\HSBC\SYSAUX01.DBF
4    7681     USERS        ***     D:\ORADATA\HSBC\USERS01.DBF
5    5864     UNDOTBS01    ***     D:\ORADATA\HSBC\UNDOTBS1.DBF
6    6219     USERS        ***     C:\ORACLE\PRODUCT\10.2.0\ORADATA\HSBC\USERS02.DBF
9    2281     INDX01       ***     D:\ORADATA\HSBC\INDX01.DBF
10   2719     USER01       ***     D:\ORADATA\HSBC\USER01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- ---------- ----------- --------------------
1    4229      TEMP      32767       D:\ORADATA\HSBC\TEMP01.DBF

RMAN>

RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';

RMAN> COPY DATAFILE 6 TO 'D:\ORADATA\HSBC\USERS02.DBF';

RMAN> SWITCH DATAFILE 6 TO COPY;

RMAN> RECOVER TABLESPACE USERS; 

RMAN> SQL 'ALTER TABLESPACE USERS ONLINE';

RMAN> HOST 'del C:\ORACLE\PRODUCT\10.2.0\ORADATA\HSBC\USERS02.DBF';

RMAN> REPORT SCHEMA;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace   RB segs Datafile Name
---- -------- ------------ ------- ------------------------
1    1320     SYSTEM       ***     D:\ORADATA\HSBC\SYSTEM01.DBF
3    960      SYSAUX       ***     D:\ORADATA\HSBC\SYSAUX01.DBF
4    7681     USERS        ***     D:\ORADATA\HSBC\USERS01.DBF
5    5864     UNDOTBS01    ***     D:\ORADATA\HSBC\UNDOTBS1.DBF
6    6219     USERS        ***     D:\ORADATA\HSBC\USERS02.DBF
9    2281     INDX01       ***     D:\ORADATA\HSBC\INDX01.DBF
10   2719     USER01       ***     D:\ORADATA\HSBC\USER01.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- ---------- ----------- --------------------
1    4229      TEMP      32767       D:\ORADATA\HSBC\TEMP01.DBF

RMAN> EXIT;

find Database Character set

Database Charactersets :

SQL> SELECT parameter, value 
     FROM nls_database_parameters 
     WHERE parameter = 'NLS_CHARACTERSET' 
     OR parameter = 'NLS_NCHAR_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ---------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16