http://sosdba.wordpress.com/2010/07/30/scan-reconfiguration/
Monday 20 December 2010
Thursday 16 December 2010
Thursday 2 December 2010
ORA-01762: vopdrv: view query block not in FROM
ORA-01762: vopdrv: view query block not in FROM
01762. 00000 - "vopdrv: view query block not in FROM"
*Cause:
*Action:
Error at Line: 29 Column: 18
Answer:: Relace join with simple muti-join table query , see below e.g.
WITH
NewsT AS ( SELECT P.related_id ID
FROM Profiling P
JOIN Account A
ON A.ID = P.account_id
WHERE P.profiling_type = 1
AND P.related_type = 3
AND A.ID = 6 )
Select * FROM NewsT;
Replace Join:
WITH
NewsT AS ( SELECT P.related_id ID
FROM Profiling P , Account A
Where A.ID = P.account_id
AND P.profiling_type = 1
AND P.related_type = 3
AND A.ID = 6 )
Select * FROM NewsT;
Great, it works !
DataPump
Typical Export & Import of Few Tables and Sequences
Export(without sequence)11.2.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=IIS_ORACLE_VODAFONE_UAT4_02122010.DMP
LOGFILE=exp_4_BSQUAT_VODAFONE_UAT4_02122010.log
TABLES=IIS_ORACLE_VODAFONE_UAT4.RESOURCECATEGORY,IIS_ORACLE_VODAFONE_UAT4.RESOURCEITEM,IIS_ORACLE_VODAFONE_UAT4.RESOURCENEWSJOIN,IIS_ORACLE_VODAFONE_UAT4.RESOURCETAGJOIN,IIS_ORACLE_VODAFONE_UAT4.NEWS
QUERY=IIS_ORACLE_VODAFONE_UAT4.NEWS:"WHERE ID > 207"
VERSION=11.1
----------------------------------
Export: Release 11.2.0.1.0 - Production on Thu Dec 2 11:38:38 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** parfile=exp_oracle_VODAFONE_UAT2_4_BSQUAT.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.437 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "IIS_ORACLE_VODAFONE_UAT4"."NEWS" 149.3 KB 21 rows
. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCEITEM" 101.8 KB 66 rows
. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCECATEGORY" 8.273 KB 15 rows
. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCENEWSJOIN" 7.218 KB 91 rows
. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCETAGJOIN" 7.640 KB 123 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u02/backup/dpdump/bsquat/IIS_ORACLE_VODAFONE_UAT4_02122010.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 11:38:44
Export(with sequence)11.2.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=IIS_ORACLE_VODAFONE_UAT4_02122010_with_SEQNC.DMP
LOGFILE=exp_4_BSQUAT_VODAFONE_UAT4_02122010_with_sequnse.log
SCHEMAS=IIS_ORACLE_VODAFONE_UAT4
INCLUDE=TABLE:"IN('RESOURCECATEGORY','RESOURCEITEM','RESOURCENEWSJOIN','RESOURCETAGJOIN','NEWS')"
QUERY=IIS_ORACLE_VODAFONE_UAT4.NEWS:"WHERE ID > 207"
INCLUDE=SEQUENCE:\"like'RESOU%'\"
VERSION=11.1
--------------
Export: Release 11.2.0.1.0 - Production on Thu Dec 2 12:25:04 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=exp_test.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.437 MB
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "IIS_ORACLE_VODAFONE_UAT4"."NEWS" 149.3 KB 21 rows
. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCEITEM" 101.8 KB 66 rows
. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCECATEGORY" 8.273 KB 15 rows
. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCENEWSJOIN" 7.218 KB 91 rows
. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCETAGJOIN" 7.640 KB 123 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u02/backup/dpdump/bsquat/IIS_ORACLE_VODAFONE_UAT4_02122010_with_SEQNC.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:25:16
Import 11.1.par
DUMPFILE = IIS_ORACLE_VODAFONE_UAT4_02122010.DMP
LOGFILE = IMP_2_LIVE_IIS_ORACLE_VODAFONE_UAT4_02122010.log
DIRECTORY = DATA_PUMP_DIR
REMAP_SCHEMA = IIS_ORACLE_VODAFONE_UAT4:IIS_ORACLE_VODAFONE_LIVE2
TABLE_EXISTS_ACTION=APPEND
----------------------------------
Import: Release 11.1.0.6.0 - Production on Thursday, 02 December, 2010 11:48:08
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Release 11.1.0.6.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_07" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_07": system/********@xxxxxx parfile=imp_oracle_VODAFONE_UAT4_2_Live.par
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "IIS_ORACLE_VODAFONE_LIVE2"."NEWS" exists. Data will be appended to existing table but all dependent meta
data will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "IIS_ORACLE_VODAFONE_LIVE2"."NEWS" 149.3 KB 21 rows
. . imported "IIS_ORACLE_VODAFONE_LIVE2"."RESOURCEITEM" 101.8 KB 66 rows
. . imported "IIS_ORACLE_VODAFONE_LIVE2"."RESOURCECATEGORY" 8.273 KB 15 rows
. . imported "IIS_ORACLE_VODAFONE_LIVE2"."RESOURCENEWSJOIN" 7.218 KB 91 rows
. . imported "IIS_ORACLE_VODAFONE_LIVE2"."RESOURCETAGJOIN" 7.640 KB 123 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCETAGJOIN_ID_TRG" created with compilation warnings
ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCETAGJOIN_ID_TRG" created with compilation warnings
ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCENEWSJOIN_ID_TRG" created with compilation warnings
ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCENEWSJOIN_ID_TRG" created with compilation warnings
ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCE_ID_TRG" created with compilation warnings
ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCE_ID_TRG" created with compilation warnings
ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCECATEGORY_ID_TRG" created with compilation warnings
ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCECATEGORY_ID_TRG" created with compilation warnings
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_07" completed with 9 error(s) at 11:48:20
Datapump Export & Import some sequence :
we want to export and import tables, functions and sequences, that they have "REWARD%" name.
we created parfile for export and import(when table exist)
parfile for export =>
DIRECTORY=INVENTORY2
dumpfile=REWARD.dmp
logfile=REWARD2.log
include=TABLE:\"like'REWARD%'\"
include=FUNCTION:\"like'REWARD%'\"
include=SEQUENCE:\"like'REWARD%'\"
parfile for import =>
DIRECTORY=INVENTORY2
dumpfile=REWARD.dmp
logfile=REWARD2.log
include=TABLE:\"like'REWARD%'\"
include=FUNCTION:\"like'REWARD%'\"
include=SEQUENCE:\"like'REWARD%'\"
TABLE_EXISTS_ACTION=replace
#expdp parfile=parexport
#impdp parfile=parimport
Subscribe to:
Posts (Atom)