Tom Kyte

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

Difference between SQL and PL/SQL

Mon, 2016-11-21 17:06
Hi, What is the Exact Difference Between SQL and Pl/SQL?How This Both are Integrated to Achieve the data from database? Thank you.
Categories: DBA Blogs

INSERT with APPEND hint

Mon, 2016-11-21 17:06
Hi, I need to Copy tables data From One DB to Another DB. There are some tables having row count around 99,000,000,000. One approach I can recollect is - insert /*+ APPEND */ into local_table select * from table@database_link; But APPE...
Categories: DBA Blogs

DDL rollback

Mon, 2016-11-21 17:06
Hi, I have some transactions in a procedure contains DDL Commands,I want to roll back whole the transactions done if happens anything wrong.So i would like to know is ROLLBACK of DDL is Possible in Oracle? Thank you.
Categories: DBA Blogs

Synchronising BEFORE and AFTER UPDATE triggers

Mon, 2016-11-21 17:06
Can a BEFORE UPDATE trigger on table A wait in a loop on a update to table B (by a Java batch program which also wants to read table A) before exiting and causing the AFTER UPDATE trigger on table A to commence?
Categories: DBA Blogs

ORA-00600: internal error while using rowid in merge statement.

Mon, 2016-11-21 17:06
Hello, Below Merge statement is giving internal error why? whats wrong with the code? what is the issue with rowid here? [If I use ID instead of rowid, it is giving valid error(unable to get stable set of rows)] ---Error: merge into t1 ...
Categories: DBA Blogs

Examining a new database

Mon, 2016-11-21 17:06
Every now and then, a new application with Oracle database comes to us. The manual is usually only end-user orientated. Seldom we get cubes, packages or frameworks for Cognos. A full database description is even more rare. What would be the fastest,...
Categories: DBA Blogs

Apex 5 get value from external?

Mon, 2016-11-21 17:06
Good Day, I have a Apex application I wrote where a form is completed, submit is pressed and a process kicks of a perl script as external procedure. The form requests IP Address and SNMP Community string only. What I want to achieve is to get Apex...
Categories: DBA Blogs

Error when restoring DB via RMAN

Mon, 2016-11-21 17:06
Hi Tom, we were making DB move procedure. NOTE: Source DB - uses ASM architecture. Target DB - will use filesystem. Backup of DB via RMAN (Linux OS, machine1) Restore of DB via RMAN (Linux OS, machine2) 1) backup was made RMAN> backup ...
Categories: DBA Blogs

Using Types in Read Only databases results in error when invoked for the first time

Mon, 2016-11-21 17:06
Following is a query that results in an error -- "ORA-16000: database or pluggable database open for read-only access", when invoked for the 1st time. But subsequent calls to the query are successful. The query runs successfully as long as its in t...
Categories: DBA Blogs

ORA-01555 caused by SQL statement below

Mon, 2016-11-21 17:06
Hi Tom, Sometimes we get ORA errors in alert file with message as: .... ORA-01555 caused by SQL statement below (SQL ID: gc8312bb07hav, Query Duration=11848 sec, SCN: 0x0000.35f6a778): WITH CORETABLE( DATAID, MAJOR, MINOR) AS ( SELEC...
Categories: DBA Blogs

DML operations inside Compound triggers

Sun, 2016-11-20 22:46
As suggested I use COMPOUND TRIGGERS, Please find requirements & the below code below. 1. IF DELETING, any record created in USER_INFO table may not be deleted. Attempting to delete a record from this table is captured, create an entry in AUD...
Categories: DBA Blogs

TDE

Sun, 2016-11-20 22:46
Under what conditions would an ORACLE.SECURITY.TS.ENCRYPTION.xxxxx not be present in the wallet after enabling tablespace encryption? All previous attempts to enable TDE on other databases resulted in the following entries - the most recent did not i...
Categories: DBA Blogs

RMAN Bakcup failure

Sun, 2016-11-20 22:46
Hi Tom, we are facing problem to rman backup failure or archive backup failure. This is not daily failure. but need to understand the exact problem and solution. rman failure with following error: channel ORA_DISK_1: starting piece 1 at 18-...
Categories: DBA Blogs

Why I get different results when comparing scalar type collection populated in PLSQL but not when it is populated in SQL

Sun, 2016-11-20 04:26
Hi guys, I'm running on Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production I started getting very strange results when doing collection data comparison within PLSQL. I've managed to narrow down the issue to a test case bel...
Categories: DBA Blogs

Pipelined function does not handle collection function calls properly

Sun, 2016-11-20 04:26
Hi, When using a pipelined function that calls another function, returning a collection I get wrong results. Here is an isolated test case <code> select * from v$version; create or replace type an_array as table of varchar2(32767); / cre...
Categories: DBA Blogs

Will PL/SQL language support real OO programming in the future?

Sun, 2016-11-20 04:26
There are always a lot of discussion & argument regarding whether to implement business rules inside the database, (i.e., close to the data, using PL/SQL) or outside the database (e.g. in the mid-tier, using Java or other language). I used to support...
Categories: DBA Blogs

Behavior of Oracle VPD considering Stored Procedures

Sun, 2016-11-20 04:26
Hi Tom, I have a particular question related to Oracle VPD (Virtual Private Database) behavior in case of Stored Procedures. I tried searching in Oracle Documentation as well as in the questions answered in AskTom, but couldn't find anything that...
Categories: DBA Blogs

Dynamic Fields

Sat, 2016-11-19 10:06
Hi, I am ramesh developing the web application using java and oracle.Our requirment is user can add the fields dynamically for ex: Step 1: user enters name,age we will store that value in parent table. ID COLUMNS_ 1 name ...
Categories: DBA Blogs

ORA-04021: timeout occurred while waiting to lock object on SYS.DBMS_AQADM.CREATE_QUEUE

Sat, 2016-11-19 10:06
Hi, i have a problem during SYS.DBMS_AQADM.CREATE_QUEUE. After 15 minutes Oracle writes : ORA-04021: timeout occurred while waiting to lock object ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3647 ORA-06512: at "SYS.DBMS_AQADM", line 130 In ord...
Categories: DBA Blogs

History of User privileges held

Sat, 2016-11-19 10:06
In Oracle 12c or 11g, is it possible to fetch the history of user privileges held by a schema? Say from last two months, I might have granted different roles and privileges. I can get the current privileges the schema holds, but is it possible to kno...
Categories: DBA Blogs

Pages