Archive for the ‘Oracle Queries’ Category

« Older Entries
Newer Entries »

EBS — Datapump expdp error ORA-33272 , ZPB, DBMS_AW, dba_aws

January 31st, 2015, posted in Oracle Queries
Share

EBS -- Datapump expdp error,EBS ,Datapump expdp error,Datapump error,ORA-33272 ,Oracle,Oracle 9i,Oracle 10g,Oracle 11g,Ora Error,Oracle Error,Error In Oracle Export

While exporting an EBS 11i database using datapump , you can get ORA-33272 error on Analytical Workspaces..
This errors are mostly due to the versions of aw objects..
Suppose you have upgraded your Ebs database from 9i to 10g and the AW objects belong to 9i version of Oracle Database have remained in your database..  In such a situation, you can encounter ORA-33272 error on your future data pump exports..

An example for these kind of errors are below;

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_AW_EXP.schema_info_exp(‘ZPB’,1,1,’10.02.00.00.00′,newblock)
ORA-33272: Analytic workspace ZPB.ZPBCODE cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], []
ORA-06512: at “SYS.DBMS_AW”, line 67
ORA-06512: at “SYS.DBMS_AW”, line 131
ORA-06512: at “SYS.DBMS_AW”, line 792
ORA-06512: at “SYS.DBMS_AW”, line 1142
ORA-06512: at “SYS.DBMS_AW_EXP”, line 517
ORA-06512: at line 1
ORA-06512: at “SYS.DB.


For the solution, use following query to list the version for the object in question;
select * from all_aws where owner=’ZPB’;  (ZPB is the schema name of failing objects in the example above)SQL> select * from dba_aws;
Example output:
OWNER AW_NUMBER AW_NAME AW_V PAGESPACES GENERATIONS FROZEN
———- ———- ——————– —- ———- ———– ——
ZPB 1000 ZPBCODE 9.1 292 2
SYS 129 AWREPORT 10.2 9 2
SYS 128 AWXML 10.2 64 2
ZPB 1001 ZPBANNOT 9.1 7 1
ZPB 1002 ZPBDATA 9.1 7 1
SYS 125 AWMD 10.2 363 2
SYS 124 EXPRESS 10.2 46 2
SYS 126 AWCREATE 10.2 21 2
SYS 127 AWCREATE10G 10.2 9 2
Analyze the output of the query and if you have older versions, delete them..
For the example above: the ZPB Aws are 9i and they are need to be deleted.

You can use dbms_aw.execute to do that..

exec dbms_aw.execute(‘aw delete zpb.zpbcode’);
exec dbms_aw.execute(‘aw delete zpb.zpbannot’);
exec dbms_aw.execute(‘aw delete zpb.zpbdata’);

Log in AS ZPB
SQL> set serverout on
SQL> call dbms_aw.execute(‘AW DELETE ZPBCODE’);
SQL> call dbms_aw.execute(‘AW DELETE ZPBANNOT’);
SQL> call dbms_aw.execute(‘AW DELETE ZPBDATA’);
If you receive errors deleting the AWs, drop the associated AW$ tables :
AS SYS:
SQL> drop table AW$ZPBCODE;
SQL> drop table AW$ZPBANNOT;
SQL> drop table AW$ZPBDATA;select * from dba_aws;SYS 125 AWREPORT 9.1 9 2
SYS 124 AWXML 9.1 50 2
SYS 123 AWCREATE10G 9.1 9 2
SYS 122 AWCREATE 9.1 18 2
SYS 121 AWMD 9.1 205 2
SYS 120 EXPRESS 9.1 38 2


Also if you have all 9i ‘s in awr$ table, then you may be need to -> Remove OLAP, Clean up Left Over Objects And Add It back (Doc ID 1560841.1)
———————————————————————————————————————————————————————————————————————-
Note : Before applying any of this. Do understand your problem and concern with some-else as well.
———————————————————————————————————————————————————————————————————————
Share
read more no comments

LOCK TABLES and UNLOCK TABLES Syntax For ORACLE

December 11th, 2013, posted in Oracle Queries
Share
Identify locked objects :
select object_name, v.session_id SID, v.oracle_username,type,lmode,request
       from v$locked_object v, v$lock l, dba_objects o
       where l.sid=v.session_id
       and v.object_id=o.object_id
       and l.block > 0;
Identify who locks whom :
select
      (select osuser from v$session where sid=a.sid) blocker,
      a.sid,
      (select serial# from v$session where sid=a.sid) serial#,
      ' blocks ',
      (select osuser from v$session where sid=b.sid) blockee,
      b.sid, c.username username
    from v$lock a, v$lock b, v$session c
    where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2
    and b.sid = c.sid

Kill session :
SQL> alter system kill session 'sid,serial#';

 

 

*********************************************************************************************************************

Also Check : http://aliimmam.com/locking-and-unlocking-tables-in-oracle/

Also Check : http://aliimmam.com/forms-could-not-reserve-record-2-tries-keep-trying/

Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as well.


*********************************************************************************************************************


Share
read more 4 comments

ORACLE : How to Calculate Average of Date Values ?

February 25th, 2013, posted in Oracle Queries
Share

Are you getting trouble of calculating average time of a filed, where data type is date time ?
Don’t worry. You are at right place to get right solution.

I’m going to calculate average time of a filed.
If there is two time value in a day, i will calculate the first one means minimum one.

SELECT TO_CHAR(TRUNC(SYSDATE)+AVG(VDATE-TRUNC(VDATE)),’HH24:MI:SS’)
FROM (SELECT MIN(DDATE) VDATE
FROM DEPT
GROUP BY TRUNC(DDATE))

****************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
well.
*****************************************************************************************************************
Share
read more no comments

ORACLE : How to Check Only One Item In a Check Box ?

February 7th, 2013, posted in Oracle Queries
Share

Here i will share a tricky PL/SQL code with you.
If you want to use Check Box in oracle forms and want to restrict end user to select only one check box, how will you handle this ?

Just customize the following code and you will get the result :

declare
l_current_record number;
l_last_record    number;
begin
l_current_record := :system.trigger_record;

last_record;
l_last_record := :system.cursor_record;

first_record;

for i in 1 .. l_last_record loop
if :system.cursor_record <> l_current_record then
:SM_ACT_FLAG := 0;
end if;
next_record;
end loop;

go_record(l_current_record);
end;

*********************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.
********************************************************************************************************************
Share
read more no comments

ORACLE : How to Show / Get, Year Month and Date between two date ?

January 28th, 2013, posted in Oracle Queries
Share

Use bellow sql to show year month and date :

SELECT    TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE) / 12) as YEARS,
MOD(TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE)), 12) as MONTHS,
(  TO_DATE (:END_DATE)- ADD_MONTHS (:START_DATE,TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE))))  as Date
FROM DUAL;

*********************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.
*********************************************************************************************************************
Share
read more 6 comments
« Older Entries
Newer Entries »