Tom Kyte

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

How to connect to the to database using unix termianl

Mon, 2016-12-19 04:46
Hi , I wanted to connect to oracle database using unix terminal. The Idea is that I have installed ubuntu as a virtual machine and I wanted to run procedures using ubuntu and the result of which should be seen in the terminal. Is is possible to...
Categories: DBA Blogs

Problem in EXPDP AND IMPDP with virtual column

Mon, 2016-12-19 04:46
Hey all I have create table with virtual columns like the following CREATE TABLE employees ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), salary NUMBER(9,2), comm1 NUMBER(3), comm2 NUMB...
Categories: DBA Blogs

PLSQL: Best/Alternate way to implement FAST Refresh for better performance

Mon, 2016-12-19 04:46
In my present db implementation, my db does not has any data/table. All the data it gets is from other sources using dblinks and then populate Materialized Views. These MVs in actual being used by my db to serve customer requests. To implement these ...
Categories: DBA Blogs

format disrupted upon using it

Mon, 2016-12-19 04:46
Hi Tom (or Chris or Connor), While i was trying something out i came upon a strange feature when using to_char. I have 2 columns in a with clause, a value and a format. Then I use these 2 in a to_char function. The strange thing is that the for...
Categories: DBA Blogs

Query to format the output

Fri, 2016-12-16 03:26
Hi Team, Hope you are doing well.. I have table like below ACC_NO DATE_OPENED PRODUCT_TYPE =================================== UN1 02-SEP-16 A UN2 02-OCT-16 B UN3 12-DEC-16 C UN4 22...
Categories: DBA Blogs

Inline views

Fri, 2016-12-16 03:26
Hi Chris/Connor, I have written below SQL to get Difference of Debit & Credit sum (DB version we are using is 11g). Problem here is I have used two inline views (with similar joins only difference is Bill_Amt_Sign = 1 & Bill_Amt_Sign = 2). Is ...
Categories: DBA Blogs

Sql Tuning

Fri, 2016-12-16 03:26
Hi , Below SQL is executed for 4hours in user's db : <code> SELECT * FROM ( SELECT Lovalias.*,rownum Rno FROM (SELECT * FROM ( SELECT A.* FROM SMVW_USER_ACCOUNTS A, STTM_CUST_ACCOUNT B WHERE A.CUST_AC_NO=B.CUST_AC_NO(+)...
Categories: DBA Blogs

ERROR: ORA-12560: TNS:protocol adapter error

Fri, 2016-12-16 03:26
Hi, I have my db on machine x1 with Service_name: abc I am on a different machine where I have client installed as Administrator and I have placed my TNS entries in :Network\Admin\tnsnames.ora from command prompt if I say Sqlplus scot...
Categories: DBA Blogs

Find out who is making direct updates to tables

Fri, 2016-12-16 03:26
Hi, There have been a couple of instances where a user has made direct updates to an EBS table. Is there any way (other than turning on audit) to find out who made these changes. We created a database trigger on these tables but it captures only w...
Categories: DBA Blogs

Ansi join mistery

Fri, 2016-12-16 03:26
Oracle 11.2.0.4 Test case: create table t1 ( a number, x number, y number ); create table t2 ( b number, x number ); create table t3 ( c number, y number, z number ); create table t4 ( d number, z number ); This query works: SELECT * ...
Categories: DBA Blogs

select constant value per group of rows

Fri, 2016-12-16 03:26
Hi Tom Having tow tables with a 0-to-many relationship, I'm looking for a query joining these tables that generates me, inter alia, a constant value per row in the first table. In the example on LiveSQL I would expect the values c_value beeing th...
Categories: DBA Blogs

Case statement syntax issue

Fri, 2016-12-16 03:26
select case when extract(month from birth_date) > 3 then (select * from people where to_date('3009'||(extract(year from birth_date)+1),'ddmmyyyy') < trunc(sysdate)) case when extract(month from birth_date) < 3 then (select * ...
Categories: DBA Blogs

RUNNING SQL CODE

Fri, 2016-12-16 03:26
Hi, it has been a while since I've tried to obtain the SQL code currently running in a Procedure, or just right after it has run (exactly like SQL%ROWCOUNT). How can I retrieve the SQL source? I'd LOVE to be able to save the source to a Log t...
Categories: DBA Blogs

Isnt with-statement not supported by Oracle-support or is just not recommended ?

Fri, 2016-12-16 03:26
I've seen in one of the client, the practice of writing the similar query as multiple layers of unions select table1.col_list, table2.col_list from table1, table2 where table1.col1 =table2.col1 union all select table1.col_list, table2.col_lis...
Categories: DBA Blogs

DRCP Connection Broker Info

Thu, 2016-12-15 09:06
Hi Tom, I'm a java developer, actually we are planning to implement DRCP for upcoming project. Regarding Database Resident Connection Pooling (DRCP) Actually UCP documentation provided the information like "how we can connect to Connection Br...
Categories: DBA Blogs

Rename a table with append

Thu, 2016-12-15 09:06
Hi, I have table a and say my user1 is inserting data into table a so my table a is having 100 rows I have table b and say my user2 is inserting data into table b so my table b is having 50 rows now I have user3 and I have table ab w...
Categories: DBA Blogs

Partitioning Decisions

Thu, 2016-12-15 09:06
Hi, we have table with below structure CREATE TABLE NSK_DETAILS ( ITEM VARCHAR2(50 CHAR), LOC VARCHAR2(50 CHAR), COUNTRY VARCHAR2(50 CHAR), SYS_DATE DATE ); I have data categorized into two country types New Zealand and Australia....
Categories: DBA Blogs

Identifying latest partition 2

Thu, 2016-12-15 09:06
Hi Chris, I tried using one of your suggestions as sub-query but seems the " partition for " does not support it. select partition_name from ( select distinct 'SCHEMA.'||table_name from dba_tab_partitions where table_owner= 'SCHEMA' and tab...
Categories: DBA Blogs

Which feature/option could be used for a specific database event?

Thu, 2016-12-15 09:06
Hi, the system shall log database security events in accordance with the Australian Government Information Security Manual (ISM:2015) Control 0987 event list: Database logging requirement Access to particularly sensitive information Addition of...
Categories: DBA Blogs

dequeue delay

Thu, 2016-12-15 09:06
Hello Tom, I have the folloging problem. My application accepts messages into queue and than dispatches (using subscriber agent) them to other system. Incomming messages can come in batches of 100 pieces in a short time and I want to dispatch them...
Categories: DBA Blogs

Pages