Tom Kyte

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

tuning array join

Fri, 2016-07-01 07:06
hi, i write relating the 71th statment of the following link. https://livesql.oracle.com/apex/livesql/s/dif16pb1eyri6hmzxxr1wg1zb i've to speed up this cursor: <code> cursor v3(c1 redd_tbTotali, c2 redd_tbPatrim) is select a1.prod...
Categories: DBA Blogs

SQL query running longer

Fri, 2016-07-01 07:06
I have three tables T1, T2 & T3. T1 has about 29 million rows T2 has 25 million rows T3 has about 9 million rows The below query takes more than an hour to run. I have tried indexes on the join columns, temp tables, hints but nothing helps. is...
Categories: DBA Blogs

Partition By and Group By

Fri, 2016-07-01 07:06
Hi Tom, I know the purpose of Partition by is to divide the data into groups and Group By is to retrieve result of some aggregate functions. What is the main difference between partition by and group by?
Categories: DBA Blogs

Date Function Filter

Thu, 2016-06-30 12:46
I have table having date with two different formats in the same column. When I'm querying like <b>extract(year from to_timestamp(colmnname, 'DD/MM/YYYY hh24:mi'))</b> its giving me as<b> date format picture ends before converting entire input string<...
Categories: DBA Blogs

Query/Disk aggregated figures not matching execution plan in SQL trace for a single SQL statement.

Thu, 2016-06-30 12:46
Hi Tom, My question is, not sure I'm hitting some sort of bug though I can't find anything on mos, why the query-current-disk aggregated figures do not match the execution plan cr value multiplied by the number of iterations or fetch calls: SEL...
Categories: DBA Blogs

Regarding the Oracle SQL Tuning Report

Thu, 2016-06-30 12:46
Team, I recently had one issue with one of my production database where we had an application upgrade completed. Once the upgrade completed , one of the select query stared performing very slow. I have tried debugging using the sqltrpt.sql , but thi...
Categories: DBA Blogs

Security

Thu, 2016-06-30 12:46
how to shadow user's tables' structure for other users. I have two users (schemas) - USER1 and USER2. USER1 has roles CONNECT and RESOURCE, but if i run 'DESC USER2.TABLE_NAME' i can show user2's table's structure. How can I forbid to show table'...
Categories: DBA Blogs

Avoid triggers

Thu, 2016-06-30 12:46
Hi Tom I have table A which goes through various DML and owned by different team, we want to capture a similar data with all the DML that's happening. we want to avoid triggers as this is owned by different team , is there a solution to capture wit...
Categories: DBA Blogs

querying data from backup file

Thu, 2016-06-30 12:46
Hi Tom / Connor, As a regular DBA Activity, everyday Production DBs are scheduled for backup and the backup files will be stored in archive location for a retention period depending on business requirement. Now my query is, I just want to pic...
Categories: DBA Blogs

how to store output of dynamic sql into a cursor.

Thu, 2016-06-30 12:46
Hi tom, I want to know how to declare a cursor which contains the data obtained by dynamically executing a query,where the query was taken from another cursor. CURSOR c1 IS SELECT QRY from QUERY_MASTER WHERE Q_ID IN (1,2); where QRY(an SQ...
Categories: DBA Blogs

How to export xml data ( clob column ) to a csv file

Thu, 2016-06-30 12:46
Hi, Let say, I have a table employee ( id number, name varchar2, notes clob ). I use dbms_util to write the data to a file as csv. The id (number) and name( Varchar2) columns are writing to csv file as expected. But when it comes to notes (clo...
Categories: DBA Blogs

Title on Report.

Thu, 2016-06-30 12:46
Hello All- May be another set of eyes!! How do I get the title to show on the spool output report file? Below is my report lay out. Thanks! <code> set heading on SPOOL U:\...\tt.txt ttitle left " Test report " skip 1 COLUMN...
Categories: DBA Blogs

covert string data to columns

Thu, 2016-06-30 12:46
Hi, I need some help to converting the string data from string to coulmns wise, i have the data in one column like : #1#5#3#2#5#7 #5#3#5#4#3#2 #1#7#5#3#6 #3#5#2#1#5 #6#5#4#7#1#2 and i need to each numberic should be columns wise like this...
Categories: DBA Blogs

Transpose the data in oracle

Wed, 2016-06-29 18:26
Hi Tom, I have a table (TABLE1): CREATE TABLE TABLE1 ( STDNO NUMBER(10), SUBJ VARCHAR2(5), MARKS NUMBER(10) ); with this data in it: INSERT INTO TABLE1 VALUES (1, 'A', 50); INSERT INTO TABLE1 VALUES (1, '...
Categories: DBA Blogs

Transposing the data from rows to columns and to split the columns into new line during particular data intervals

Wed, 2016-06-29 18:26
Hi Tom, Can you suggest me the solution for transposing the data from rows to columns, and to split the data into new column during particular interval. Here is the sample tables and data that i am working on and the desired output as well: ...
Categories: DBA Blogs

shell script to send two outputs to single file

Wed, 2016-06-29 00:06
Hi Tom, I need to prepare one shell script, which contains start and end time of the script(which is another shell script) and one database query. In this script, i am good to execute the db query and send output to file.In the same way,i want t...
Categories: DBA Blogs

Convert rows to columns

Wed, 2016-06-29 00:06
Hi Tom, I have a table: CREATE TABLE TEACHER (NAME VARCHAR(20), PRO VARCHAR(20)); with this data in it: INSERT INTO TEACHER VALUES ('A','PROFESSOR'); INSERT INTO TEACHER VALUES ('B','DOCTOR'); INSERT INTO TEACHER VALUES ('C','ENGINEER');...
Categories: DBA Blogs

Moving Constraints from one table to another

Wed, 2016-06-29 00:06
Hi asktom team, I want to convert a NON - PARTITIONED table to LIST - partitioned table. So to do that I am doing the following: FOR EXAMPLE take ORDERS table: -Renaming the TABLE ORDERS to ORDERS_OLD. -Creating a table ORDERS same as ORDER...
Categories: DBA Blogs

What are possible meanings of error OCCI ORA-12560: TNS:protocol adapter error?

Wed, 2016-06-29 00:06
Hi, The Oracle error messages are not always very descriptive. Searching the internet I found couple reasons why we can get ORA-12560 error: 1) Oracle database instance is not started 2) ORACLE_HOME is not set (for version 10) What are other ...
Categories: DBA Blogs

Peformance issue with user_tab_partitions query

Wed, 2016-06-29 00:06
Hi, We have the below query which is taking lot of time....while reading the partition name for the given high_value of table table_1. Here P_SEQ is the input value that we are passing through a procedure to this query. SELECT to_char(x.parti...
Categories: DBA Blogs

Pages