Tom Kyte

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

expdp several tables in join query

Wed, 2018-08-29 07:26
I need to expdp the 5 tables data which satisfy the below query,I had read some documents on MOS and found that the doc 'Export/Import DataPump Parameter QUERY - How to Specify a Query (Doc ID 277010.1)',which show the use of QUERY parameter,but the ...
Categories: DBA Blogs

check the 2nd line of a file

Wed, 2018-08-29 07:26
Hello, I need to check if a file content is written on a single line or it is slit in multiple line. For example, I want to make difference between the following 2 structures: 123'456'789' vs. 123' 456' 789' My only idea is to check...
Categories: DBA Blogs

to_date(varchar2) compared to datetime

Wed, 2018-08-29 07:26
I have VARCHAR2 DateTime string. I want to convert it into DateTime and compare it with DateTime in the where clause. to_Date(VARCHAR2 field, 'MM/DD/YYYY HH:MI:SS AM')>= '09/10/2008 08:31:10 AM' If I just do SELECT to_Date(VARCHAR2 field, 'MM...
Categories: DBA Blogs

How to achieve Data Synchronization for oracle database between AIX and linux servers?

Wed, 2018-08-29 07:26
Hi Tom, We are having Oracle databases running on AIX servers and Planning to build parallel landscape for these databases on linux servers. The main objective for us is to keep data synchronized between these two platforms in real time. ...
Categories: DBA Blogs

Archive Table data and restore when needed

Tue, 2018-08-28 13:06
We have a table named DS_AUDIT_ENTRY where logs of audits are recorded. It contains many years data. Now we want to keep 6 months data only and archive old data. Also if required we have to fetch the old data whenever asked. How can I perform this a...
Categories: DBA Blogs

Loading PDFbox jars to oracle DB.

Tue, 2018-08-28 13:06
Hi, I am trying to add specific text to a pdf file using the apache pdfbox utility. I have loaded following jars on to DB using following command. loadjava -verbose -u user/pwd@ccoadev -jarasresource -genmissing -grant public -resolve pdfbox...
Categories: DBA Blogs

Getting JSON from odata into json table, with a special character '@'

Tue, 2018-08-28 13:06
Hello Tom, The odata-JSON string created by f/e https://www.odata.org/getting-started/ what is provided through a web-service needs to be inserted into a table. The json looks like: {"@odata.context": "serviceRoot/$metadata#People"} How can i ext...
Categories: DBA Blogs

INST_DRTLD_MISMATCH cause version count very high

Tue, 2018-08-28 13:06
Hi, Recently I faced a problem , my 9i database had a latch free wait event on statpack report I found a SQL consume almost full of my shared pool All of the hash value are same ,So I query v$sql_shared_cursor view to find out why T...
Categories: DBA Blogs

Performance issues with Views

Tue, 2018-08-28 13:06
Hi, I have a situation where I have created multiple views to control the access on tables using roles of users. And now users are writing queries on top of multiple complex views, And facing performance issues. Is there anyways to avoid views and go...
Categories: DBA Blogs

Temporary Tables

Tue, 2018-08-28 13:06
Hi All, I'm a recent convert from PL/SQL to SQL Developer & Oracle. As such, I used #temptables a ton and was highly discouraged from writing 'super SQL' scripts that tried to do everything in one query. Now, I'm learning that just the opposit...
Categories: DBA Blogs

Tunning Between clause

Mon, 2018-08-27 18:46
i am trying to tune a query which contains between clause in Oracle 11g. i have table employee(id number, join_dt date, end_dt date) which has 10 million records. and it has index on join_dt,end_dt first run, dbms_stats.gather_table_stats(owne...
Categories: DBA Blogs

FOPEN to sub folders

Mon, 2018-08-27 18:46
Hello, I am trying to find a way to write a file into the sub folder of an Oracle Directory. I can write into the base of the oracle directory but not into the sub folders. To keep it simple, This is what we have that currently works, after that i...
Categories: DBA Blogs

ORA-00600: internal error code, arguments: [156057], [], [], [], [], [], [], [], [], [], [],

Mon, 2018-08-27 18:46
Hi Tom, Our database is oracle 11.2.0.3. My customer met an error "ORA-00600: internal error code, arguments: [156057], [], [], [], [], [], [], [], [], [], []," when he did 'select * from UPL_SECTOR'. UPL_SECTOR is a table he created by himself...
Categories: DBA Blogs

Move historical data between databases

Mon, 2018-08-27 18:46
Hello Tom See how you could optimize moving records (historical by date) from one table in a production database to another table in another historical database in an automatic way. Could you support me in Oracle Partition? It could be used ex...
Categories: DBA Blogs

Spreadsheet Upload

Fri, 2018-08-24 17:26
Hi there, If there is a way to upload the spreadsheet data in our existing application? If possible please send your answers. Regards, Aravindan Prem
Categories: DBA Blogs

JET Line Chart - Step Handling

Fri, 2018-08-24 17:26
I have a problem generating the vertical lines in a line chart. E.g. take this query: with nums as ( select rownum as rnum from dual connect by rownum < 300) select rnum/9 as x, sin(2*rnum/30) as y from nums In the X-Axis, my tick mar...
Categories: DBA Blogs

Doing joins on denormalized data in tables

Wed, 2018-08-22 10:26
Hi Team RDBMS version: 12.2 OEL: 7.5 Trying to evaluate new table structure to query data, which I've heard is very widely used these days in e-commerce companies at scale. Though most of those companies might be using NoSQL solutions for the ...
Categories: DBA Blogs

Get difference between 2 date columns in HH:MM:SS

Wed, 2018-08-22 10:26
I am trying to get difference between 2 date columns in HH:MM:SS. Currently I have the below query which is returning the difference in HH:MM:SS. The problem with the below query is even if there is a difference of 1 day and 13 hours, it is just givi...
Categories: DBA Blogs

Documentation claims that multiset conditions also work with varrays, which they don't

Wed, 2018-08-22 10:26
According to the documentation, a lot of multiset conditions should also work with varrays, which they don't (at least not in the SQL language): https://docs.oracle.com/database/121/SQLRF/conditions006.htm#SQLRF52138 I've created a Stack Overflow...
Categories: DBA Blogs

QUERY CLAUSE for 2 join tables

Wed, 2018-08-22 10:26
Hi Tom, I have 2 SQL scripts giving lots of rows :- ---Export Data for fy17 filename: w_camp_hist_f_2016.dmp select count(*) from olap.w_camp_hist_f,olap.w_day_d where w_camp_hist_f.ld_dt_wid=w_day_d.row_wid and w_day_d.petc_year = 2016; ...
Categories: DBA Blogs

Pages