Tom Kyte

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

exception handling in select a query not pl/sql block

Thu, 2016-10-27 13:06
Query; update accounts set product_code=( select product from products); from the query the sub query return multiple rows. So i want to update it with null value. so how could i do this exception handling from this query??
Categories: DBA Blogs

Script for increase sequence by table max pk value

Thu, 2016-10-27 13:06
We have sequences created by table name format as seq_<table_name>. Somehow sequence is lower than pl value and need to fix by increasing sequence. Here I just simulate the situation as example below: </> ORACLE@hist > create table t1 (a_id num...
Categories: DBA Blogs

to find the list of procedures and their respective tables used inside the package

Thu, 2016-10-27 13:06
Hi Tom, Is there any possible way to find the list of stored procedures and their respective tables being used inside the package? I have tried using the below query, but i am getting the result as all the tables used inside the package (if i f...
Categories: DBA Blogs

Reduce database size

Thu, 2016-10-27 13:06
Hi Tom, My goal is to reduce my 7 TB database's size, is it possible with minimal downtime? Can I go with moving tables across tablespaces? Or shrink the tables? I have indexes occupying more than 80 GB, please suggest methods for indexes as wel...
Categories: DBA Blogs

ORA-00936 when querying spatial data

Thu, 2016-10-27 13:06
Im so thrilled to have come across your site! I need a pair of eyes to spot the issue. Im fairly new to Oracle databases. The following error appears in log files and I cant understand what is missing. <b>Caused by: org.springframework.jdbc.Ba...
Categories: DBA Blogs

Sql Plan Baseline issues

Thu, 2016-10-27 13:06
Hi, We are migrating from 10.2.0.4 database to 11.2.0.4 database. We identified one of the insert query taking longer time than it was in 10g. We changed the OFE to 10.2.0.4 at session level and ran the same query which completed in 8 secs....
Categories: DBA Blogs

OSB - Use os stored procedures

Wed, 2016-10-26 18:46
Hello, I'm working on a SOA project and we're using stored procedures to work with Oracle RAC in OSB. This keep the project clean, organized, avoiding XA and others benefits. The only bad thing is a little extra time to work with. We decided t...
Categories: DBA Blogs

alter session force parallel QUERY parallel

Wed, 2016-10-26 18:46
I have a job running with the error ORA-12842: Cursor Invalidated During Parallel Execution,and I re-run and it's complete. I check the MOS NOTE:1322894.1 to look up the cause,I see that it give the cause is : ORA-12842 can be reported to a user ...
Categories: DBA Blogs

timestamp of a inserted row

Wed, 2016-10-26 00:26
In a classic case of replication (forget the tool of replication here ) Oracle - 2 - Oracle create table abc (anum number, aname varchar2(30) , adate date default sysdate ) ; insert into abc values(1,'1test',sysdate); insert into abc val...
Categories: DBA Blogs

PL/SQL Programming

Wed, 2016-10-26 00:26
Write a PL/SQL block that uses looping to display the value of the number two (2) raised to a power. The power should be given as a local variable called lv_power. For example, if the variable is initialized to 5, the output would be 2 raised to th...
Categories: DBA Blogs

How to avoid external table's data source file be replaced when another query is using the old data source file

Wed, 2016-10-26 00:26
The external table's data source file is updated with some interval, if another query is running, replace the old data source file will cause 1)ORA-29913 and ORA-30653 if not setting reject limit UNLIMITED 2) data inconsistent, part data is old sou...
Categories: DBA Blogs

Query on Stored Procedure - MINUS clause

Wed, 2016-10-26 00:26
Dear All, Ques 1. Please advise if the use of minus clause is best when I am looking to exclude certain specific conditions or shall I be using Not IN? I am in situation where I know what to avoid, not sure what all to select and hence I needed...
Categories: DBA Blogs

When shutting down a container database

Wed, 2016-10-26 00:26
Hello, Some background info: We are working in a 12c multitenant environment on windows server 2012 R2. Container database contains about 20-25 pluggable databases. Typically we have to shutdown the container database to perform maintenan...
Categories: DBA Blogs

ORA-29532: Java call terminated by uncaught Java exception: java.lang.OutOfMemoryError

Wed, 2016-10-26 00:26
When Passing clob size more than 3 MB to java stored procedure I get java.lang.OutOfMemoryError. The data is in the JSON format which we try to deserialize into an object using a java json library in the java stored procedure. We have tried the ...
Categories: DBA Blogs

SP2-0743 and SP2-0042

Wed, 2016-10-26 00:26
Hi Sir, From the doc: SP2-0042 unknown command command_name - rest of line ignored Cause: The command entered was not valid. Action: Check the syntax of the command you used for the correct options. SP2-0734 Unknown command beginning com...
Categories: DBA Blogs

Case construct with WHERE clause

Tue, 2016-10-25 06:06
Hi Tom, I have a question and I don't know if this is possible or if i'm jsut doing something wrong because i get multiple errors like missing right paren, or missing keyword. I want to use the CASE construct after a WHERE clause to build an expre...
Categories: DBA Blogs

Missing Physical Reads

Tue, 2016-10-25 06:06
Hi Tom, Please find below the experimented done, sequentially. Scripts ---------- create table cust (cust_id number, last_name varchar2(20),first_name varchar2(20)); create index cust_idx1 on cust(last_name); SQL> set autotrace on; SQL> ...
Categories: DBA Blogs

Function with multi-dimensional array as parameter?

Tue, 2016-10-25 06:06
How would I define a function that takes a multi-dimensional array as an input parameter and returns json_tbl PIPELINED that has been defined as <code>CREATE OR REPLACE TYPE CIC3.json_t as OBJECT (JSON_TEXT varchar2(30000)); CREATE OR REPLACE TYPE ...
Categories: DBA Blogs

Depth of attributes,

Tue, 2016-10-25 06:06
Hello, I have a situation where my data (for a given sys_id) has values for multiple depths (level1 attribute, level2 attribute and so on). For a given sys_id, I have to select the rows that has the maximum depth. However, as an example, if a va...
Categories: DBA Blogs

Oracle Auditing - Syslog

Tue, 2016-10-25 06:06
Hi Guys, I have two questions with regard to Oracle database auditing via syslog. 1. When auditing via OS syslog, what is the ideal value for the AUDIT_SYSLOG_LEVEL parameter, where AUDIT_SYSLOG_LEVEL = facility.priority It is the priortity...
Categories: DBA Blogs

Pages