DBA Blogs

ORA-06548: no more rows needed

Tom Kyte - Wed, 2016-08-03 19:46
Hi Team, Below is the sql used SELECT * FROM TABLE(pkg.fn_api('PR_LOAD_PL_PROT_TO_SITE','ROLE_ABBR') Below is the code snippet of pkg TYPE t_parm_val_txt_rec IS RECORD ( o_parm_value_txt VARCHAR2 (4000 BYTE) ); TYPE t_...
Categories: DBA Blogs

how to find bottleneck sql's in a package in oracle without using dbms_profiler or dbms_hprofiler? is there any alternatives?

Tom Kyte - Wed, 2016-08-03 19:46
how to find bottleneck sql's in a package in oracle without using dbms_profiler or dbms_hprofiler? is there any alternatives?
Categories: DBA Blogs

Waiting for parallel query to finish

Tom Kyte - Wed, 2016-08-03 19:46
Is there any way I could know the status of a parallel query i.e. the query has finished the job. I am trying to use few parallel updates and inserts in a VB6 application and finally a truncate. The truncate query causes "ORA-00054: resource busy a...
Categories: DBA Blogs

TRIGGER FIRE

Tom Kyte - Wed, 2016-08-03 19:46
HI TOM, Which will fire default first statement level or row level trigger?
Categories: DBA Blogs

Advanced Queuing & PL/SQL Notification & Oracle User

Tom Kyte - Wed, 2016-08-03 19:46
I have a queue and i register a PL/SQL - Routine fpr this QUEUE. When the routine is executed it will run under user SYS. I need that the procedure run under my user (e.g. ICIS_SERVICES). How i can reach this? Here my code example: =====...
Categories: DBA Blogs

INACTIVE sessions keeping a handle on CPU

Tom Kyte - Wed, 2016-08-03 19:46
Hi! We had an issue recently where there was a spike in CPU, and this caused an issue with the end users. We got it sorted by killing off some sessions that were running that shouldn't be. This fixed the issue, and we are working on the route caus...
Categories: DBA Blogs

Need some help developing sql analytical query to find consecutive rows changing between two values

Tom Kyte - Wed, 2016-08-03 19:46
Hello, I have master table having information for Trouble_Ticket_ID, Customrer_Circuit_ID,Location and details table B having the following information:Trouble_Ticket_ID,User_ID,Status, Newgroup. I need to calculate the no.of time the ticket h...
Categories: DBA Blogs

Select count(*) on a billion records table.

Tom Kyte - Wed, 2016-08-03 01:26
Hello Tom, Thanks for all the great help. I am writing a simple query which is Select count(*) from wsh_exceptions. Currently this table contains 1,091,130,564 records. So because of this more number of records in this table, select count(*) ...
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs