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
Friday, 26 November 2010
ORA-29855 error in oracle and solution & Oracle Text
We sometime see the error when insufficient privileges are assigned and CTXSYS is not presented in oracle db 10g.
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
This errors comes when lack of privileges/roles when we work with text components (formerly known as Context, or Intermedia Text), this concept provides a powerful search, retrieval, and viewing capabilities for text stored in oracle database.
The solution is as follows:
AS sys DBA:
GRANT EXECUTE ON CTX_DDL TO username ; -- for oracle text package execution i.e. index rebuild and synchronize etc. Ignore if CTX_DDL not going to be used.
grant CTXAPP to username ;
Solution:
-----------
Ensure that CTXSYS schema is installed on database instance.
Install:
@? points oracle home directory i.e. ORACLE_HOME variable, ensure ORACLE_HOME and ORACLE_SID is set (window) or with export in UNIX.
The steps to be executed as SYS user
create tablespace drsys as sysdba
run @?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock
run @?/ctx/admin/defaults/drdefus.sql (as CTXSYS user )-- see below for why and how
grant execute on ctxsys.ctx_ddl to user;
Uninstall:
@?/ctx/admin/catnoctx.sql as sys
The next step is to install appropriate language-specific default preferences. There is script which creates language-specific default preferences for every language Oracle text supports in ORACLE_HOME/ctx/admin/defaults directory and script should be executed as CTXSYS user.
@?/ctx/admin/defaults/drdefXX.sql -where XX is the language code
- wish this helps you too..
Wednesday, 24 November 2010
ORA-23421: job number <nnn> is not a job in the job queue
You can only change your own jobs by using DBMS_JOB package. Not even SYS is exception to this, meaning that if you connect to the database as SYSDBA you'll be only able to change jobs belonging to SYS, but not those belonging to SCOTT, for example.
Howevere there is undocumented package called DBMS_IJOB that enables you to manipulate other users jobs (provided that you have EXECUTE ANY PROCEDURE privilege). For example, you can remove any job (not only those owned by you) from the job queue by executing procedure SYS.DBMS_IJOB.REMOVE(:job_id)
I'm not sure if DBMS_IJOB has all the procedures that DBMS_JOB has (WHAT, NEXT_DATE, INTERVAL, BROKEN, ...) and if all the parameters are the same, but you can find this out by yourself.
logon as sysdba and use:
SQL>select * from dba_jobs;
To drop perticular job:
SQL>exec SYS.DBMS_IJOB.remove(210);
To drop all user’s job:
SQL>exec SYS.DBMS_IJOB.DROP_USER_JOBS('myuser');
Cheers!
Job Removed!
Subscribe to:
Posts (Atom)