Tom Kyte

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

migrating database

Fri, 2016-10-14 13:26
i have database in sql server 2008, now i want to migrate whole databse from sql server 2008 to oracle 11g. my question is how to migrate the sql server database into oracle 11g?
Categories: DBA Blogs

Export query output to one file and log to another file

Fri, 2016-10-14 13:26
How to export Sql query results(only rows) to one table and log(Number of rows selected) to another table.
Categories: DBA Blogs

WM_CONCAT vs LISTAGG

Fri, 2016-10-14 13:26
What are the technical differences between WM_CONCAT vs LISTAGG? I know WM_CONCAT is undocumented function. Is LISTAGG technically superior or do they do the same thing very differently?
Categories: DBA Blogs

Merging result set of two queries into one

Fri, 2016-10-14 13:26
Hello, I'm trying to concatenate/merge the results from two different queries into one. Query 1: select 10 id, 'ABC' r_ref, 123 r_amnt from dual union all select 10, 'BCD', 234 from dual; Query 2: select 10 id, 'CDE' p_ref, 345 p_amn...
Categories: DBA Blogs

Value List items that are NOT IN the Table

Fri, 2016-10-14 13:26
How to Select Values, in a Value List, that are NOT IN a Table? e.g. I need to find which of the following products are not on the Item Master:- 10, 20, 25, 70, 90. (this list is available only in a spreadsheet or word document) Assume they all e...
Categories: DBA Blogs

Extracting data from JSON in relational tables

Fri, 2016-10-14 13:26
Hi Tom Given the below JSON example, how do I know which notes and which occupants belong to which jobs (given that there is no unique identifier in the data itself? The relationship is defined by the JSON structure. Is there a pseudo column o...
Categories: DBA Blogs

write data from a table to a csv file and save in it a directory.

Thu, 2016-10-13 19:06
I need to write data from a table to a csv file and save in it a directory. I am using below code: create or replace PROCEDURE get_query_result_as_csv_file( in_query IN VARCHAR2, in_filename IN VARCHAR2) IS l_blob BLOB; ...
Categories: DBA Blogs

Double select from same parametrized view

Thu, 2016-10-13 19:06
Dear all, I'm trying to use parameterized view, and i need select from some view twice with different values of same parameter vie UNION ALL. Could you say, having considered the above example, is it possible that the both queries will use o...
Categories: DBA Blogs

Is there a way to force DML error logging without adding LOG ERRORS INTO clause to INSERT, UPDATE etc?

Thu, 2016-10-13 19:06
For INSERT statements run against a table with an associated DML error log table, I would like to force DML error logging for INSERT statements that do not include a LOG ERRORS INTO clause. For Example: CREATE TABLE "WSB"."TEST" ("CCHAR" VA...
Categories: DBA Blogs

Audit trace for table

Thu, 2016-10-13 19:06
Hi Tom, we came across the request from customers. There were some activities on table (let's name it T1), audit was not turned on from the creation of db. Customer raised request to provide some trace or audit for T1- all activities for pas...
Categories: DBA Blogs

IMPDP STATS TABLES

Thu, 2016-10-13 19:06
Dear Tom, Please help me with the below 1) I did full dump restoration from prod to test environment 2) Now I'm trying to import the prod stats to test environment 3) Created STAT_TABLE in the newly imported test environment like below beg...
Categories: DBA Blogs

How do i insert update and delete using db link

Thu, 2016-10-13 19:06
There are two instance on Unix server Instance A and Instance B i want to update one table on Instance B from Instance A What is the best way ?
Categories: DBA Blogs

DBMS_METADATA issues

Thu, 2016-10-13 19:06
Hi, I am trying to extract the DDL code for an index. Please have a look at the below code. The same returns BEGIN NULL; END; as the output. Please can you confirm if there is something wrong with the code? drop table a; create ta...
Categories: DBA Blogs

SQL query to find employees earning more than department average

Thu, 2016-10-13 19:06
My question.. how to write query to display the employee who are getting more salary then the average salary to the department_number from department table. thanks in advance...
Categories: DBA Blogs

how to improve the SQL performance of an 8 node Exadata Data Warehouse RAC

Thu, 2016-10-13 00:46
Hi Tom! Last day I work with an 8 node Exadata Data Warehouse to tune performance for the slow SQL,and I found that the table is not gather statistics and the execution plan is not the best, it executes with the TABLE ACCESS STORAGE FULL,the table...
Categories: DBA Blogs

change running code's execution plan

Thu, 2016-10-13 00:46
Hi Tom, Suppose a package is executing in prod environment, currently proc A is running and after that Proc B will be executed in next 45 mins. I have just spotted some problems in proc B execution plan(which was generated earlier). I cant chang...
Categories: DBA Blogs

Oracle

Thu, 2016-10-13 00:46
Hi Guys, I am using linux to run the query. I have one query like select * from Emp; I want to spool the results into one file(this file has only exported data without number of rows selected message) and what ever the log i.e number of lines ...
Categories: DBA Blogs

How to replicate a nested table

Thu, 2016-10-13 00:46
I would like to know if there is any easier way to replicate a nested table. Say for a normal table, we can just do CREATE TABLE a_bkp as SELECT * FROM a; Is there a similar easy way to create replica of existing nested table ?
Categories: DBA Blogs

Comments

Thu, 2016-10-13 00:46
Can someone please help me with query for how can I extract the 'comment' section of all the tables ? Used this query to get all the table names: Select table_name, owner, tablespace_name from dba_tables; Used below query to gather the comm...
Categories: DBA Blogs

INDEX MONITORING USAGE

Thu, 2016-10-13 00:46
Hi All, It seems that only indexes that are listed in the explain plan are monitored by index monitoring feature. But indexes that are used for constraint checking might not be listed in explain plan and in this case the index monitoring featur...
Categories: DBA Blogs

Pages