Tom Kyte

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

how the SQL query is processed

Sun, 2017-09-24 15:06
How the SQL query is processed in detail
Categories: DBA Blogs

Range constraint

Sun, 2017-09-24 15:06
Hi! First i want to thank you for the many Responses that where usefull for me. I need a constraint or trigger that controls if the row inserted or updated contains a range of numbers that is already contained in another row of the same table. ...
Categories: DBA Blogs

oracle plsql data migration

Sun, 2017-09-24 15:06
Hi Tom, i need to write a plsql program, Suppose there are 10 sup tables with different columns in each table and 1 target table which have columns same as combined all source columns. i need to write a procedure which will extracts the data from...
Categories: DBA Blogs

temp undo sizing

Sun, 2017-09-24 15:06
Hi team, I have One question - 1. Suppose i have 1TB DB Size so What our recommendation in term of -sizing of database memory. -Temporary tablespace -Undo tablespace Thanks
Categories: DBA Blogs

Convert sys_guid to number

Sun, 2017-09-24 15:06
Hi Tom I read some articles about to_number(sys_guid()), but You suggest not to use it. Why Apex QuickSQL suggests to_number(sys_guid()) for unique object-ID ? Example create or replace trigger country_biu before insert or update ...
Categories: DBA Blogs

Query taking more time to split,compare against lookup table and to insert

Sat, 2017-09-23 02:26
We have a dimension table in which one column has mutlipul values like an Array. I have to get all this individual values and should compare against a lookup table, if the value does not exist in the look up table then i have to insert this value ...
Categories: DBA Blogs

TNS No Listener issue

Sat, 2017-09-23 02:26
When i am trying to connect to one of our databases on Toad/SQL developer/SQLPLUS, getting the issue that the ORA-12541 TNS No Listener. steps to take: verify that the port specified in the connect descriptor is correct in tnsnames.ora verify t...
Categories: DBA Blogs

ORA 1017 error while connecting to the database as SYS using SQL developer

Sat, 2017-09-23 02:26
I changed the SYS user password in database I recreated the password file using Oracle Doc ID 1029539.6 Now when I try to login to the database using SQL developer it gives the ORA 1017 error Am I missing any step here ? please advise ... ...
Categories: DBA Blogs

How to see SGA usage for all active and inactive sessions i.e SGA used by each process / session

Sat, 2017-09-23 02:26
Recently , I ran into a System Global Area (SGA) related alert as below : SGA USAGE INST_ID TOTAL_MB USED_MB FREE_MB FREE_PCNT 1 168836.35 105033.66 43802.71 29 And these alert kept on showing frequently for pas...
Categories: DBA Blogs

Protect access to certain data from the DBA

Sat, 2017-09-23 02:26
This question has probably been asked many times, but I cannot find a solid solution. We are required to restrict access (select/insert/update/delete) to certain database tables even from the DBA. As far as I know, only Database Vault can do this rel...
Categories: DBA Blogs

Does Foreign Key constraint (No Validate) impact on performance?

Sat, 2017-09-23 02:26
We have imported data from PROD to UAT environment. While import foreign key constraints enabled as No Validate. Both the tables parent table and child tables have huge number of rows. How can we tune the queries referencing to these two tables, s...
Categories: DBA Blogs

Tom Kyte Blog

Sat, 2017-09-23 02:26
Hi, What has happened to Tom Kyte's blog http://tkyte.blogspot.com/. Can you add me to white list of that blog ? Will be very great-full. Thanks Fahd
Categories: DBA Blogs

Combination of selected columns from one table to be made primary key column value in other table

Sat, 2017-09-23 02:26
Hi, I have a cust_bug_test table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows: <code>create table cust_bug_test(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER NOT NULL PRIMARY KEY); insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) valu...
Categories: DBA Blogs

BULK COLLECT and FOR-UPDATE Clause

Sat, 2017-09-23 02:26
Hi Tom, When we use the FOR-UPDATE clause in cursors, we lock the rows which we intend to work on. Also, we use BULK COLLECT to process huge amount for rows to save time. Let's considering a scenario that a cursor is fetching rows for bulk pro...
Categories: DBA Blogs

Insert Large set of data from View to Table faster

Fri, 2017-09-22 08:06
<b></b>Hi Tom, Good morning. We have a dynamic view which is growing day by day due to the combinations are increasing. SO we have decided to create a table from the view , for which we have created a procedure which runs fine but it takes lo...
Categories: DBA Blogs

java stored procedure retrieving file from FTP

Fri, 2017-09-22 08:06
Hi Tom, I've made java stored procedure that retrives file from FTP. The java program works well on local system but on database it hangs and not working and I should stop the OracleService to stop it. The below is what I did for making the jav...
Categories: DBA Blogs

TNS error about 'TNS-12535 and TNS-00505' in alert log file of my Oracle 11.2.0.4.0 ?

Fri, 2017-09-22 08:06
Hi,teams I found that there has a plenty of TNS error about 'TNS-12535 and TNS-00505' in alert log file of my Oracle 11.2.0.4.0. part content as follows, <code> ...... *********************************************************************** Fata...
Categories: DBA Blogs

selecting rows from a table

Fri, 2017-09-22 08:06
Hi, I have a cust_bug_test table with 2 columns(ROOT_CAUSE, BUG_NUMBER) like as follows: create table cust_bug_test(ROOT_CAUSE VARCHAR(250), BUG_NUMBER NUMBER NOT NULL PRIMARY KEY) insert into cust_bug_test(ROOT_CAUSE, BUG_NUMBER ) values('Thi...
Categories: DBA Blogs

Test to be performed after Critical Patch Application

Fri, 2017-09-22 08:06
Hello Oracle Masters, Thanks for taking the time and answer our questions! Is there any kind of test or validation to run after applying the critical patch update? I have applied the Critical Patch Update - July 2017 and would like to know i...
Categories: DBA Blogs

how to traverse the tree in reverse direction?

Fri, 2017-09-22 08:06
dear Tom, In a hierarchical query how do I traverse the tree from the branch level to the top? Say in the emp->manager case, given the empno, I want to find his manager, manager's manager etc. thanks,
Categories: DBA Blogs

Pages