Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 6 hours 21 min ago

Will open cursor hold up more tablespace when it is not closed?

Mon, 2016-07-18 01:26
Hi Tom, I am using oracle 11g and the tablespaces keeps growing, I have recently identified a issue with open cursors which is not closed when the session was closed. Will open cursor eats up all the spaces which leads to consume more temp spa...
Categories: DBA Blogs

ORA-01578: ORACLE data block corrupted (file # , block # )

Mon, 2016-07-18 01:26
Hi Tom, I m getting following error in my production data base alert log since last 5 month. Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_j000_26775.trc: ORA-01578: ORACLE data block corrupted (file # , block # ) ORA-01578: O...
Categories: DBA Blogs

ORA - 08103:Object No Longer Exists

Mon, 2016-07-18 01:26
Hi, I am getting an error Ora-08103: Object No longer exists when select queries are fired against a partitioned table having local Bit map indexes. But when the same process/job (which fires select query) is restarted the issue does not come up a...
Categories: DBA Blogs

Why the "Row Source Operation" ('reality exec') differs from the "Execution Plan" ('guess exec')

Sun, 2016-07-17 07:06
Hi, Team There's a procedure in our Oracle 11.2.0.1 DB which captures data from MS SQL Server through DB Link, since the context is quite long (cursors, pragma autonomous_transaction and loop included), let us take it brief like this: CREATE OR...
Categories: DBA Blogs

XML Update

Sun, 2016-07-17 07:06
Hi Tom, I need to update some attribute(XML) in oracle table whose datatype is XML. Is there any package provided by oracle which help me? Or if you can tell me anyway how can I update it.
Categories: DBA Blogs

clone(duplicate active Database)

Sun, 2016-07-17 07:06
I Want to clone a database from one computer to another computer, both computer connected with lan. The database which i have to clone is available on computer 2. on computer 1 i have created an instance using oradim utility On computer 2 <code>...
Categories: DBA Blogs

Add_months

Sun, 2016-07-17 07:06
Hi Guys, I am just a little bit confused regarding the following : select add_months(to_date('30/01/2016','DD/MM/YYYY'),1) from dual Result is : 29/02/2016, shouldn't it be 28/02/2016 ?? Thanks Mohannad
Categories: DBA Blogs

issue while adding new apps node in oracle EBS r12.2.5

Sun, 2016-07-17 07:06
Hi DBA Experts, I am facing following issue while adding new application node please help me to fix this issue. Executing command: perl /u02/applmgr/SR1225/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/adProvisionEBS.pl ebs-create-node -contextfi...
Categories: DBA Blogs

export to CSV

Sun, 2016-07-17 07:06
Tom, I need to export data from a table into a .csv file. I need to have my column headres in between "" and data separated by ','. also depending on the column values a row may be printed upto 5 times with data differing in only one field. ...
Categories: DBA Blogs

Finding transacted tables,

Sat, 2016-07-16 12:46
Hello, Using Oracle data dictionary, how to find out the list of tables that have undergone Insert/Update/Delete by a particular user account in the last 7 days? Also, if possible I want to know the number of transactions happened and the size of...
Categories: DBA Blogs

What is best way to collect GLOBAL STATS of a table with 4 billion records

Sat, 2016-07-16 12:46
Hi Tom I need to take global stats collection for a table with 4 billion records. This is a partitioned table but I need to collect STATS globally. This table was last analyzed in Sept 2015. As of now following is Table statistics Actual Number...
Categories: DBA Blogs

DENSE_RANK function

Sat, 2016-07-16 12:46
Hi Tom, I have a problem with DENSE_RANK function. Let's see an example: <code>CREATE TABLE test_rank (val number); INSERT INTO test_rank VALUES(1); INSERT INTO test_rank VALUES(2); INSERT INTO test_rank VALUES(3); INSERT INTO test_rank VALUE...
Categories: DBA Blogs

DROP TABLE performance/delay

Sat, 2016-07-16 12:46
Hello Tom, We have a new application which has serious performance problems, and a reason for that might be the Oracle DB. Unfortunately, tests could not find the root cause yet. Now I saw a very strange behavior of the DROP TABLE performance: ...
Categories: DBA Blogs

count of distinct on multiple columns does not work

Sat, 2016-07-16 12:46
Hi, I am trying to count the number of distinct combinations in a table but the query gives error. For example, create table t(a varchar2(10), b varchar2(10), c varchar2(10)); insert into t values('a','b','c'); insert into t values('d','e'...
Categories: DBA Blogs

Creating and Executing a stored procedure that dynamically builds a table getting ORA-06550: PLS-00103

Sat, 2016-07-16 12:46
Receiving ORA-06550: PLS-00103 error when trying to execute a procedure that dynamically creates a table. I have created a pl/sql script to dynamically create a table: declare l_tablename varchar2(30) := 'TEST_3_'||to_char(sysdate, 'YYYYMMDD')...
Categories: DBA Blogs

Average of input two Dates

Fri, 2016-07-15 18:26
How Can i create a oracle sql Function which can take two input Dates and Display their Average.For Ex:- input :-28-Aug-2016,4-Sep-2016 output:- 1-Sep-2016
Categories: DBA Blogs

Constraints

Fri, 2016-07-15 18:26
<code>Tom, Are the following a full list of all possible user constraint_types P - primary key? C - check? R - foreign key? U - unique ? And what are their full meanings? Are the dab constraint_types the same? Thanks Brian </code>
Categories: DBA Blogs

Schema migration + unknown table utilization

Fri, 2016-07-15 00:06
Hi team, I have pretty much an unanswerable question, but I thought I'd see what advice you can give anyway. I am working on a project trying to separate many legacy applications using shared schemas to their own self contained. There are a co...
Categories: DBA Blogs

IN vs OR clause

Fri, 2016-07-15 00:06
I have following query. This table has millions of rows and table is partitioned by date. select * from test.testing d where D.code in ('123','124','136','136'); Like we have to pass 100 values. What is the best way to get the results back...
Categories: DBA Blogs

Recover Procedure

Fri, 2016-07-15 00:06
Hi ask tom team, we need to restore a procedure to a one month previous version.. unfortunately. Database is in Archine log mode and we take daily backups. Is it possible ?
Categories: DBA Blogs

Pages