Tom Kyte

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

Query regarding join order in ANSI

Wed, 2016-08-10 16:46
I wrote below two queries, one written using ANSI syntax and other using conventional syntax. While writing these, I expected them to be logically equivalent and return same number of records (whatever that may be). When I actually ran these queries,...
Categories: DBA Blogs

After import/export rowdependecies is lost

Wed, 2016-08-10 16:46
Hello, We did dupm and load of our schema using impdp. And few months later we noticed that all tables that in original database where created with rowdependencies in new database are created without without rowdependencies. 1. Is this possib...
Categories: DBA Blogs

When does uncommited data rollback from data file?

Tue, 2016-08-09 22:26
Dear Team, DBWR write flush data from db buffer cache at the time ? Checkpoint occurs ? Dirty buffers reach threshold ? There are no free buffers ? Timeout occurs ? Tablespace OFFLINE ? Tablespace READ ONLY ? Table DROP or TRUNCATE ...
Categories: DBA Blogs

Create a database trigger (Dynamic) on a table

Tue, 2016-08-09 22:26
Hi Tom, I have a requirement where I have to create a table level trigger dynamically. Here are more details : A user will be provided an option to choose the columns in a table XYZ which will be tracked for any changes - inserting, updati...
Categories: DBA Blogs

excessive log generation

Tue, 2016-08-09 22:26
hi - we have a 11.2.0.4 database in production that we saw was generating too many logs even when the database is dormant. We initially thought it was due to rman backups but now we are seeing that if we issue one log switch it is causing too many lo...
Categories: DBA Blogs

SMTP ACL gets dropped ocassionally

Tue, 2016-08-09 22:26
Hi, I have a schema which uses the ACL like below to send mails.The ACL gets dropped ocassionally and application using teh schema is unable to send the mails.We need to manually add the ACL again and everything gets back to normal.Can anyone expe...
Categories: DBA Blogs

Query Mysql database from oracle

Tue, 2016-08-09 22:26
Hi!, I am trying to query a mysql database from oracle. I have configured everything and everything is working fine but when i query any table of mysql database from oracle, i dont get the columns having number data type. In hs file i have follwing ...
Categories: DBA Blogs

ORACLE Query to return First Row for a DataSet

Tue, 2016-08-09 22:26
I'm writing a query which would return first row of a set of grouped data. I tried using OVER PARTITION BY clause, but somehow I'm not getting the desired result : select row_number() OVER(PARTITION BY leafv , value_group , l1d ,l2d ,l3d ,l4d ...
Categories: DBA Blogs

Mutating table

Tue, 2016-08-09 22:26
Hi i was supposed to make script that will find data in table and based on these insert into same table. When i was doing this operation while using temporary table to prevent mutating table error(it did not worked and i know my before insert trigge...
Categories: DBA Blogs

Migrating from smallfile tablespace to bigfile

Tue, 2016-08-09 04:06
hi - we recently migrated our 11.2.0.4 database from a non rac to a rac system, so the tablespaces came as smallfile tablespaces. this is our platform database and a 24x7 oltp system. what is the best way to move them into bigfile tablespaces with mi...
Categories: DBA Blogs

Script to compare data in all tables in two different databases, if the table is present in both the databases.

Tue, 2016-08-09 04:06
Hi, I am looking for a stored procedure to compare the data in all the tables in two different databases. I have 2 databases DB1 and DB2. From DB1, a Dblink is created to access DB2. First step is - to find all the tables that exists in...
Categories: DBA Blogs

Restrict Access on Active DG Primary DB

Tue, 2016-08-09 04:06
In our environment we have a production server which handles all reads/writes and we have an active DG which we use to offload backups as well as read only reporting on. We have already established a mechanism to handle password resets when an end u...
Categories: DBA Blogs

Number of Execution per snapshot too high for rman sql

Tue, 2016-08-09 04:06
Hi Team, I have been seeing many executions of below query in our database. begin sys.dbms_backup_restore.createRmanOutputRow( l0row_id => :l0row_id, l0row_stamp => :l0row_stamp, row_id => :row_id, row_stamp =>:row_stamp, txt=> :txt, sameline ...
Categories: DBA Blogs

Trigger to allow insertion only on Sunday

Tue, 2016-08-09 04:06
I have employee table. I want to create a trigger that will not allow insertion in the table on sunday.</b> Tell me the program please.. Thank you.
Categories: DBA Blogs

JSON from Relational Data

Tue, 2016-08-09 04:06
With all the new JSON Features, is there a way to take queries over relational data (ie. normal Oracle tables) and on the fly generate JSON objects? More and more vendors are using REST based apis that process JSON (a key one for us is Oracle Sales ...
Categories: DBA Blogs

Performance imact of adding 200+ columns to a table

Mon, 2016-08-08 09:46
Hi Team, I have doubt on number of columns on a table. currently we have 66 columns in a table and we want to add extra 204 columns in that table. My question is it will impact the DB performance and lead to row chaining or not? The DB block si...
Categories: DBA Blogs

SQL query sometimes taking longer (most of the time working good)

Mon, 2016-08-08 09:46
This is the duplicate (unable to edit the old) question where Connor Sir has asked to add code tags but as I was not aware of code tags. Here it is... This is to ask some suggestion about a query which is infrequently taking minutes->hours time...
Categories: DBA Blogs

Why Scalar sub query is reducing my query cost to 50%

Mon, 2016-08-08 09:46
Hi Tom, Please have a look: Below are the 2 queries with the same result set but differing in the cost. When i used scalar sub query instead of inner join its cost is low when compared with the other. Could you please explain. Please u...
Categories: DBA Blogs

Getting data from Multiple Users

Mon, 2016-08-08 09:46
Hi Tom, Please find the below problem statement and kindly provide the solution. Problem Statement: I have a schema and that schema contains a table T1. T1 table contains 2 columns i.e. UserId and CityId. T1 ------------- USERID CITYID -...
Categories: DBA Blogs

RMAN restore into a new database

Mon, 2016-08-08 09:46
Hi.. 3 days before I took Level 0 and Level 1 backup from my database( name:sample) by using RMAN .Yesterday my target database was crashed.So I created another new database(sample1). Now I want to use those backup files and restore that all data in...
Categories: DBA Blogs

Pages