Tom Kyte

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

Creating partition on json column in Oracle Database

Mon, 2019-01-07 12:26
Hi, Is there a way to create partition on oracle table based on values or keys from JSON column?
Categories: DBA Blogs

Create sequences inside procedure

Mon, 2019-01-07 12:26
Hi, Is it possible to create Sequences within a procedure using execute immediate dynamic SQL, just as we can create other objects like table/index etc ? If not what is the workaround for creating sequences within a procedure by executing the ...
Categories: DBA Blogs

Select for update in SQL Package with PRAGMA AUTONOMOUS_TRANSACTION

Thu, 2018-12-27 01:46
Hello, I have a package with some functions used to synchronize the operation performed by a number of servers. When a server select an ID and perform operations on it the other servers can not select the same ID. To achiev this i use a SYNC fla...
Categories: DBA Blogs

performance stats for all entries in query log records (audit trail)

Mon, 2018-12-24 00:26
I need to find these stats : cpu time execution time io count app id proc id of procedure used in query for all queries which are being logged dba audit trail (XML) I have looked into V$SQL and dba_hist_sql stat but these contain only sn...
Categories: DBA Blogs

Join of huge tables taking to much of time

Mon, 2018-12-24 00:26
<u></u> Hi Team, <b>Background:</b> We have 40-45 applications who have SQL server as a database (fake name : <<SQLAppDB>>). Now, this <<SQLAppDB>> is having some views which point to Oracle's Views via linked server connection (fake name : <<Or...
Categories: DBA Blogs

java.sql.SQLException: SQL string is not Query - While updating BLOB column

Mon, 2018-12-24 00:26
Greetings: We are facing below exception, while executing a query "UPDATE TRN_WSF_SERVICE_AUDIT SET REQUEST=EMPTY_CLOB() WHERE TRN_WSF_SERVICE_AUDIT_SID = 75002202" here REQUEST is a CLOB datatype. Detailed error ----------------------------...
Categories: DBA Blogs

session id for sys user

Mon, 2018-12-24 00:26
Hi Tom, Wondering your thoughts on why the session id of sys user is hard coded to 429496729. SQL> select sys_context('userenv','sessionid') from dual; When you run this connected as sysdba it always returns 429496729. The same select using sid i...
Categories: DBA Blogs

Control distribution scripts with "whenever sqlerror"

Fri, 2018-12-21 17:26
My company uses distribution scripts for deploying database updates to multiple environments. I need to control the scripts such that an "error" (defined as resulting in an "Ora-#" code) abends the script, but a warning (e.g. Warning: compiled but w...
Categories: DBA Blogs

Automatically archive data based on timestamp

Fri, 2018-12-21 17:26
Hi Tom I need to perform data archival in Oracle 12c, after which the archived data must be accessible by the application, when queried for. Is there a way to automatically archive data which are >1 year old based on the 'updated_date' column for ...
Categories: DBA Blogs

Data types

Thu, 2018-12-20 23:06
Hi Tom Tom I am sure you have answered my question several times as " floats are synonyms for numbers. But I have a situation at work where my colleague wont agree with me that there is no difference between oracle float and number datatype with ...
Categories: DBA Blogs

join example, new vs old syntax

Thu, 2018-12-20 23:06
Hi Tom, I know in oracle9i we have the cross join and full outer join. Do they exist for 8i if so can you point me to the documentation. If they don't can you give an example/breakdown of how to rewrite in 8i. Thanks in advance,
Categories: DBA Blogs

Show previous YTD if query is ran in January else Current YTD

Thu, 2018-12-20 23:06
I'm looking for the logic to filter data Previous YTD if the query is ran in January else filter Current YTD. Any help would be greatly appreciated. Thank you Marc
Categories: DBA Blogs

SQL Plus doesn't consider special characters in filenames

Thu, 2018-12-20 23:06
<b>There are files that contain special characters (i.e. $, #) in the name, and SQL Plus cannot find them. </b> <code>$ sqlplus -L user/password@host:port/SID @/path/to/file/create$spec_view.sql SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec...
Categories: DBA Blogs

ORA-29283: invalid file operation

Thu, 2018-12-20 23:06
Hi Tom, Thank you for being with us and for all your support over the years. From last few days I am getting an error while writing file from my application i.e Exception Info: ORA-29283: invalid file operation. But if I run same procedure f...
Categories: DBA Blogs

Unique constraint on function based index

Thu, 2018-12-20 04:54
Hi TOM, I try to create an unique constraint using FBI, it fails with "ORA-00904: : invalid identifier" error. It looks like "alter table" doesn't accept functions in expressions for unique constraints - <code> create table ctest (a integer, b i...
Categories: DBA Blogs

Scheduling backup

Thu, 2018-12-20 04:54
Schedule ?backup Oracle database? as following: 1- Saturday backup incremental 0 at 22 :15 2- Rest of the week incremental 1 at 23:00
Categories: DBA Blogs

How to unload table data to csv file in old oracle version, the fastest way

Wed, 2018-12-19 10:46
We are currently using Oracle Database 8i. We have multiples table and around 600 millions of rows. We use SQLPLUS to export rows to csv with nested queries. Configuration: <code> set term off set feedback off set linesize 32767 set hea off...
Categories: DBA Blogs

Recursive query to get base tables for views, but from a list of views

Wed, 2018-12-19 10:46
Hi, I appreciate that similar questions have been asked before, but I am struggling with the next step if you like. I want to get all the base tables (recursively) used by a list of views. From various posts here I know how to do this for a sin...
Categories: DBA Blogs

Transfer data from one data file to another data file,so that I can drop a data file without losing data

Wed, 2018-12-19 10:46
Dear Team, My table space has 28 data files each with size of 30g(approx). Below the query I used to find out each data file size and free space in that data file. <code> select t1.file_id,total,free from (select file_id,trunc(bytes/1024/10...
Categories: DBA Blogs

PL/SQL Parallel function performance

Wed, 2018-12-19 10:46
Hi, I was trying to optimize one of the batch update queries (given below) in my application <code> UPDATE schema1.TEST_RECORDS PARTITION (PARTDEF) gr SET gr.purge_status_cd = 'PURGE_PENDING', gr.purge_date = SYSDATE WHERE EXISTS ( SELECT 1 F...
Categories: DBA Blogs

Pages