Tom Kyte

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

DBMS_ASSERT

Sun, 2016-10-30 14:26
DBMS_ASSERT package is used in NDS to validate data and to prevent SQL injection attacks. What is the difference between QUALIFIED_SQL_NAME, SIMPLE_SQL_NAME and SQL_OBJECT_NAME? This is confusing. Please explain with practical examples.
Categories: DBA Blogs

Date range grouping for same price and stop sale option

Sun, 2016-10-30 14:26
Hi Tom, Please disregard my previous ticket as I am not able to edit and the query requirement has changed. Thanks for the link by I am struggling a bit. I have a table of daily rates for a service that contains a price and an Y/N field to sto...
Categories: DBA Blogs

Performance issue with the Query

Sat, 2016-10-29 20:06
Dear Tom, We have recently Upgraded form 11g to 12c. We have a small application(it is exactly same as SQL Developer) through which we can select a particular table from the list and we can view the data. All tables has less than 10...
Categories: DBA Blogs

Identifying SQL Queries that are consuming Shared Pool Memory

Sat, 2016-10-29 01:46
Hello Tom, Is there a way to identify SQL queries that are consuming the majority of the shared pool for e.g Session #1 is using 56 kb session #2 is 10mb I would want to identify each query and the memory value it's consuming. Thanks in advan...
Categories: DBA Blogs

Efficient method in Oracle to continuously poll data from one database and move to another

Sat, 2016-10-29 01:46
Hi, I have a scenario where I have to continuously( in every 1 hour) poll updated data from tables of one database and move to another database. What do you think is the standard approach. (anything similar to DTS in Sql server in Oracle) or would...
Categories: DBA Blogs

getting error 0382

Sat, 2016-10-29 01:46
I have made stored procedure and executing through trigger.So my requirements is like that if we create a new issue first statement should execute and if we return the same issue number then 2 nd statement should execute. I have make like this but...
Categories: DBA Blogs

Pagination query and optimization

Sat, 2016-10-29 01:46
Hi Tom, I've been reading through a few posts regarding pagination. I'm using the following query template in my application, to implement a "previous", "next" feature to page through a search resultset: (J2EE front-end calls a stored proc returni...
Categories: DBA Blogs

Automatic conversion of cursor for loop into set based operation

Fri, 2016-10-28 07:26
Hi, We all know that doing things row-by-row ("Cursor For Loops") is a bad idea rather than a set-based approach, however I have read in a number of places that in certain circumstances Oracle will convert a cursor for loop into a set-based operatio...
Categories: DBA Blogs

Spanish trasnlation for amount in words

Fri, 2016-10-28 07:26
Hi Tom, I have a requirement by a client that the amount in number currently prnting on checks needs to be converted to spanish, ex one hundred us dollars should be printed as cien dolares americanos Could you please suggest how can w...
Categories: DBA Blogs

How can i incorporate a text to the existing values of a column

Fri, 2016-10-28 07:26
Hi, The query is as below, I have a table in which i have the column named as V_QTN_NAME, here in this column the values are of different in each row. Now i am trying to differentiate the values by incorporating the text 'OLD'to the existing va...
Categories: DBA Blogs

Instance and SGA relation

Fri, 2016-10-28 07:26
Hi Tom, I would like to know how many SGA Created inside instance when data base starts? Here instance mean instance only i.e to which we mount database. Also please let me know if you answer the questions posted in reviews,perhaps i wil get ...
Categories: DBA Blogs

Insert statement getting locked in database.

Fri, 2016-10-28 07:26
Hello Sir, I have a very simple question and I have searched all around but could not find it, can insert statements be locked by other DML sessions ?? And how ? Reference :- I have a process that updates/inserts multiple records into one table ...
Categories: DBA Blogs

RMAN Incremental

Fri, 2016-10-28 07:26
Hello Team, We have oracle 12c standard running on centos. Basic backup strategy is developed without using asm/ catalog database as suggested in requirements. Backup Plan is as below: Requirement says full backup has to run every night with ...
Categories: DBA Blogs

Users and Roles

Fri, 2016-10-28 07:26
Tom, in your apps 1)Do you normally have an Oracle user for each application user...or a table you create of the users for your app. I was creating real Oracle users w/forms and now use my own tables of users for web enabled apps...but I noticed t...
Categories: DBA Blogs

redo 12c and temporary table

Thu, 2016-10-27 13:06
Hello there, I am quite curious why redo is still generated when I set temp_undo_enabled=true, having parameter compatible 12.1.0.2.0. I expected redo to be close to 0. The same result I got years ago, when I first introduced with 12.1.0.1 I ...
Categories: DBA Blogs

Left join with filter condition is not working as supposed

Thu, 2016-10-27 13:06
Hello, One of my colleagues asked a question like "when I insert query result to table, I found records that meet my criteria, but when I put the same filter on the same query without inserting rows to table, I did not get those records". After li...
Categories: DBA Blogs

exception handling in select a query not pl/sql block

Thu, 2016-10-27 13:06
Query; update accounts set product_code=( select product from products); from the query the sub query return multiple rows. So i want to update it with null value. so how could i do this exception handling from this query??
Categories: DBA Blogs

Script for increase sequence by table max pk value

Thu, 2016-10-27 13:06
We have sequences created by table name format as seq_<table_name>. Somehow sequence is lower than pl value and need to fix by increasing sequence. Here I just simulate the situation as example below: </> ORACLE@hist > create table t1 (a_id num...
Categories: DBA Blogs

to find the list of procedures and their respective tables used inside the package

Thu, 2016-10-27 13:06
Hi Tom, Is there any possible way to find the list of stored procedures and their respective tables being used inside the package? I have tried using the below query, but i am getting the result as all the tables used inside the package (if i f...
Categories: DBA Blogs

Reduce database size

Thu, 2016-10-27 13:06
Hi Tom, My goal is to reduce my 7 TB database's size, is it possible with minimal downtime? Can I go with moving tables across tablespaces? Or shrink the tables? I have indexes occupying more than 80 GB, please suggest methods for indexes as wel...
Categories: DBA Blogs

Pages