DBA Blogs

Find rows inserted yesterday

Tom Kyte - Thu, 2016-08-04 14:06
Hi, I have a table with 3 columns emp_id,last_name and date_of_birth. And each day data loading starts after 12 am . I have to generate the report on data loaded before today's load (exclude loaded today after 12 am). I was asked this questio...
Categories: DBA Blogs

PLSQL INTERVIEW QUESTIONS

Tom Kyte - Thu, 2016-08-04 14:06
HI TOM, PLEASE EXPLAIN WITH AN EXAMPLE ...BREIFLY I KNOW ONLY LITTLE BIT 1)How to debugg your code? 2)How to trace error handling? 3)Can you alter procedure with in package? 4)Is it possible to open cursor which is in package in another procr...
Categories: DBA Blogs

ORA-01000 - How to close open CURSORs?

Tom Kyte - Thu, 2016-08-04 14:06
Hi, I have a cursor leak at the application level (Java), how can I close open cursors? If I Kill the session, does this close the related cursors? Also, if I use DBMS_SHARED_POOL.PURGE to flush the query behind the issue, does this close the ...
Categories: DBA Blogs

Sessions disconnected automatically.

Tom Kyte - Thu, 2016-08-04 14:06
Hi Tom. I can't find how my database is disconnecting the inactive sessions because when I ran the following statements: select * from user_resource_limits a where a.resource_name in ('IDLE_TIME','CONNECT_TIME'); IDLE_TIME UNLIMITED CONNE...
Categories: DBA Blogs

related to stored procedure

Tom Kyte - Wed, 2016-08-03 19:46
why we can't call stored procedure from sql select? and why stored function called from sql select statement has only in variables? and can sys_refcursor exhibits dynamic sql?
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs