DBA Blogs

CROSS APPLY and OUTER APPLY in 12c

Tom Kyte - Wed, 2016-08-03 01:26
Hi, I have learned that we have CROSS APPLY and OUTER APPLY in 12c. However, I see results are same for CROSS APPLY and INNER JOIN, OUTER APPLY and LEFT / RIGHT OUTER JOIN. So when INNER JOIN and LEFT/RIGHT OUTER JOIN are ANSI Standard and yi...
Categories: DBA Blogs

CPU Waits

Tom Kyte - Wed, 2016-08-03 01:26
Hi, When i am trying to analyze my database performance through AWR reports, my findings are Wait Wait Time Total Avg(ms) %DB Time DB CPU 2,731,581.78 75.49 * System I/O db file parallel write 882,568.7...
Categories: DBA Blogs

Contraint/Check _ Prevent any value to be inserted more than twice in any table.

Tom Kyte - Wed, 2016-08-03 01:26
I have a table say xyz which contains a field say roll_number, now I want to implement a check so that any roll number is allowed only twice in xyz. OR Suppose I have a table xyz containing roll numbers, and another table abc containg roll numb...
Categories: DBA Blogs

Difference Between Two Dates With Time

Tom Kyte - Wed, 2016-08-03 01:26
Sir, There are two input dates with format 'DD/MM/YYYY HH:MI:SS' How can I get difference between these two dates like Input ---------------------------------------------- '15/11/2015 11:19:58' '14/10/2014 10:18:57' -----...
Categories: DBA Blogs

Is it feasible to use Dynamic SQL for a statement with "into :num" clause?

Tom Kyte - Wed, 2016-08-03 01:26
We have the following statement executed in the database by the outside-of-the-database application: "SELECT COUNT(*) INTO :num FROM TABLE1" I'd like to be able to execute this statatement at will via Dynamic SQL to preserve the same sql_id. ...
Categories: DBA Blogs

Creating a view using dynamic pivot table

Tom Kyte - Wed, 2016-08-03 01:26
Hi Tom I apologize if this has been posted before. I am trying to build a web page that will provide information from AWR tables and I would like to compare two months data in a chart. select * from ( select to_char(end_time,'dd') Day ...
Categories: DBA Blogs

ORA-12537: TNS:connection closed

VitalSoftTech - Wed, 2016-08-03 01:18
When trying to establish a sqlplus connection I get the "ORA-12537: TNS:connection closed" error messgae. How do I resolve this?
Categories: DBA Blogs

Oracle Partner Community - WebLogic & Developers Newsletter July 2016

The Fusion Middleware flagship on-premises products WebLogic 12.2.1.1, SOA Suite 12.2.1.1 and BPM Suite 12.2.1.1 are available for download! Find out more news from the Weblogic and Developers EMEA...

We share our skills to maximize your revenue!
Categories: DBA Blogs

ORA-31633: unable to create master table ".SYS_IMPORT_FULL_05"

Learn oracle 12c database management - Tue, 2016-08-02 10:22
 Today I encountered a problem while importing a schema into my local database. I have exported a schema from ORCL (lets say) using expdp command. I tried to import it to another database and I was getting this error. ORA-31633: unable to create master table
[oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2

Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges


I tried again and again same error. Then I checked may be it is missing some grants, I havegranted  sysdba privillage to SAM now and tried.
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant sysdba to SAM;

Lets try again:
[oracle@orcl dpump]$ impdp SAM/SAM  directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2

Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges.



Then I realized that it might be missing create table permission and granted that permission to user SAM.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> grant create table to SAM;

Grant succeeded.

Now tried again :

[oracle@orcl dpump]$ impdp SAM/SAM  directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2


 Import: Release 11.2.0.4.0 - Production on Fri Nov 14 14:02:54 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SAM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SAM"."SYS_IMPORT_SCHEMA_01":  sam/******** dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE




Possible Solutions :
  •  It might be a simple missing of create table grant  if you are trying to import it through another user.
  • Try to check if there is already a table created (SYS_IMP*) with the name oracle is trying to create table.
  • Check if the job name being used in session is already created in database.


















Categories: DBA Blogs

Reading a text in tag in a clob column

Tom Kyte - Tue, 2016-08-02 07:06
I have a column in DB which is of clob type. I need to extract the text in one of the tag using a sql query but i am getting a xml parsing error. xml in the clob col: <ns2:reference xmlns:ns2=""> <ns2:meta-data> <content-type>REFERENCE</c...
Categories: DBA Blogs

ora-12224 tns no listener

Tom Kyte - Tue, 2016-08-02 07:06
sir i change host name with ip address in user computer i got server ip address by oracle command select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual; result 127.0.0.1 i change this ip address with host orcl = (DESCRIPTION ...
Categories: DBA Blogs

Issue in generating bill using Oracle Programming

Tom Kyte - Tue, 2016-08-02 07:06
I want to generate newspaper bill according to the following criteria. In a newspaper publisher, the cost of newspaper on Tuesday and Thursday is Rs.3/- and on other days it is Rs.2/-. So my requirement is to generate billing of newspaper as per t...
Categories: DBA Blogs

Links for 2016-08-01 [del.icio.us]

Categories: DBA Blogs

Oracle 12c: Indexing JSON In The Database Part I (Lazarus)

Richard Foote - Tue, 2016-08-02 00:42
One of the very cool new features introduced in Oracle Database 12c Rel 1 is the ability to store JavaScript Object Notation (JSON) documents within the database. Unlike XML which has its own data type, JSON data can be stored as VARCHAR2, CLOB or BLOB data types, but with a JSON check constraint to ensure the […]
Categories: DBA Blogs

Named Parameters w/REF CURSOR

Tom Kyte - Mon, 2016-08-01 12:46
Hi Tom, This question is about the named parameter syntax. I've successfully called functions using this method: v_order_id := CREATE_CUSTOMER_ORDER ( p_customer_id => p_customer_id, p_total_amount =...
Categories: DBA Blogs

HASH JOIN Vs SORT MERGE

Tom Kyte - Mon, 2016-08-01 12:46
Hello Asktom Team, Hope you could throw some light on this. We have two Oracle 11g instances where one MERGE statement has radically different execution plans. One finishes in less than a minute and one times out with ORA-01652: unable to exten...
Categories: DBA Blogs

Distribution of DOP

Tom Kyte - Mon, 2016-08-01 12:46
I'm using Oracle 12c. I'm calling mutiple procedures from Python in parallel (usually 4-5). I make sure nothing else is running at that time. All the target and source tables has Parallel 32 Clause . I'm using hints ENABLE_PARALLEL_DML, PARALLEL in...
Categories: DBA Blogs

Count groups when next value = current value plus one

Tom Kyte - Mon, 2016-08-01 12:46
I have this table: create table test (myname varchar2(1), dts_id number); With this data: insert into test (myname, dts_id) values ('A',1); insert into test (myname, dts_id) values ('A',2); insert into test (myname, dts_id) values ('A',3); in...
Categories: DBA Blogs

Pivot table row to column covert

Tom Kyte - Mon, 2016-08-01 12:46
Hi Tom, Please consider below scenario. Table A ID Attribute Value 1 'X' 3 1 'Y' 4 2 'X' 5 3 'X' 6 3 'Y' 7 3 'Z' 8 3 'A' 9 Expected output ID X Y Z A 1 3 4 nul...
Categories: DBA Blogs

Please give the sql to get query performance in oracle database, i don't want to use statspack.

Tom Kyte - Mon, 2016-08-01 12:46
I was SQL Server DBA over 1 year, now i am oracle DBA so i used to use below query against sql server database to get query performance. <code>SELECT TOP 5 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.obje...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs