Tom Kyte

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

Covert Column to Rows Dynamically

Mon, 2018-10-01 02:46
Hi, I have situation when I am trying to convert rows into columns(Dynamically). Here is the scenario - 1. I have a function(userdefined) which takes in a SQL query as input and returns the result as a table type. 2. The result is ...
Categories: DBA Blogs

Insufficient privilege to access object SYS.DBMS_LOCK

Mon, 2018-10-01 02:46
Dear Tom, If we are using the dbms_lock package in anonymous block it is working fine.But it is not working inside the procedure. We have gone through the following link asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html and even tried with '...
Categories: DBA Blogs

Alter Table

Mon, 2018-10-01 02:46
Hi Tom , i want ALTER TABLE by adding columns to it , if the columns already exist int the table then it doesn't add , else adds the columns . how i can do that ? Regards, Adil
Categories: DBA Blogs

Insertion over db links creating extra rows than expected

Fri, 2018-09-28 19:46
Hi Tom, iam facing a weird issue . below is the scenario, My package creates insert statements for 4 tables which lie on someother oracle 11g db. tab1 tab2 tab3 tab4 The same 4 tables exists in 7 servers. (admin and cus1 to cu6). ...
Categories: DBA Blogs

HOW TO RESOLVE THE ERROR :- ora-01406 fetched column value was truncated

Fri, 2018-09-28 01:26
HOW TO RESOLVE THE ERROR :- ora-01406 fetched column value was truncated
Categories: DBA Blogs

Outer join with row archival

Fri, 2018-09-28 01:26
LiveSQL-Link: https://livesql.oracle.com/apex/livesql/s/hblhxmq40jtini45sivyqj4le <code> create table test_table (id number(10),name varchar2(10)) row archival; insert into test_table (id,name) values (1,'name1'); insert into test_table (id...
Categories: DBA Blogs

Find Closest Matching Single Record

Fri, 2018-09-28 01:26
I want a Query that should fetch a single record based on match conditions: table looks like <code> create table SERVICES ( srvc VARCHAR2(10) not null, location VARCHAR2(10), grp VARCHAR2(10), empno VARCHAR2(10), pric...
Categories: DBA Blogs

create JSON from fields

Thu, 2018-09-27 07:06
Hi, I have relation 'resources' in database with 2 fields, 'id' and 'data'. 'id' eg. 25 and 'data' eg. <code>{"href":null,"id":"25","publicIdentifier":null,"description":null,"category":null,"validFor":null,"name":null,"lifecycleState":null,"type":"R...
Categories: DBA Blogs

DBMS_JOB.SUBMIT doesn't do anything in Oracle 12c (12.2.0.1.0)

Thu, 2018-09-27 07:06
Hi guys, I have a problem when I try to start a stored procedure with DBMS_JOB.SUBMIT. I have the package "dafneMultithread": <code> CREATE OR REPLACE PACKAGE DAFNE.dafneMultithread AS PROCEDURE start_job(p_procedure_name IN VARCHAR2...
Categories: DBA Blogs

Query all tables and all columns for a specific value

Thu, 2018-09-27 07:06
Hi Oracle Masters, I wonder if oracle is capable of returning the table name and column name based on a specific value only. e.g. Which table and column in oracle DB that has a value of 'ORACLE'? This is mainly for determining the mapping of th...
Categories: DBA Blogs

LISTAGG .. WITHIN GROUP (ORDER BY ..) is ignored in UPDATE .. RETURNING lause

Thu, 2018-09-27 07:06
Consider this script (which I've also put on Live SQL: https://livesql.oracle.com/apex/livesql/s/hbfb7x0c47ubwjpygs3klr3wj) <code>CREATE TABLE t ( id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, category NUMBER(10) NOT NUL...
Categories: DBA Blogs

How do I find out if a particular trigger is executing or is being internally ignored from execution inn Oracle 11gR2?

Thu, 2018-09-27 07:06
Hi Tom, I am a frequent reader of AskTom. Your in-depth and precose answers are always great. I would like to know if there is any SQL (based on Data Dictionary, v$ or x$ views) that I can use to find out id a particular db trigger is executin...
Categories: DBA Blogs

Looping JSON array in FOR loop

Wed, 2018-09-26 12:46
Hello, My requirement is to perform various actions once I fetch the 'id' JSON element from the results JSON. Below JSON is generated from a third-party vendor for which I don't have a control to change the structure. The issue is, for some reason...
Categories: DBA Blogs

Statspack "SQL ordered by Elapsed" and ORA-1555

Wed, 2018-09-26 12:46
I got this in the alert log of a database: <code> Wed Aug 15 13:21:29 CEST 2018 ORA-01555 caused by SQL statement below (SQL ID: 4dhx1332z74nf, Query Duration=94491 sec, SCN: 0x000c.cdffd21c): Wed Aug 15 13:21:29 CEST 2018 SELECT /*+ FIRST_ROW...
Categories: DBA Blogs

Seeing dbms_output buffer from another session?

Wed, 2018-09-26 12:46
Hi, I have a sqlplus session that is hanging and I want to see what dbms_output is in its buffer. If I kill the session, sql*plus will never print the serveroutput, and I will lose whatever was in the dbms_output buffer. Is there some way I can s...
Categories: DBA Blogs

gc buffer busy acquire ion RAC on stress load

Wed, 2018-09-26 12:46
Hi, we have Oracle 12c (12.1.0.1 SE) RAC on 2-node Windows 2012 R2 OS. I am testing simple performance test using jmeter which starts 500 concurrent users ant each of then inserts a one row in table 1000 times. Jmeter makes a commit after each in...
Categories: DBA Blogs

Run class Java in Oracle from other databases

Tue, 2018-09-25 18:26
Hello Is it possible to create a Java class using JDBC and run a job, I have seen that it has how to execute Java classes in SQL Developer, I would like it to work in Oracle itself, not in SQL Developer
Categories: DBA Blogs

Overlapping of dates

Tue, 2018-09-25 18:26
I have two scenarios explained below: 1. Suppose say, I have an organization with start date as 01/JAN/2017 and end date as 31/DEC/2017. The speciality of my organization has the start date as 01/OCT/2017 and end date as 10/OCT/2018. So when the qu...
Categories: DBA Blogs

Simulation of long time for query parsing

Tue, 2018-09-25 00:06
Hi Tom, How can I force a long query parsing time for Oracle or how to prepare such a complicated query? I want to simulate a situation where Oracle parses a query for 30 seconds before execution. Within 30 seconds I want to modify the synonym ...
Categories: DBA Blogs

Unified Audit on 12.2 - partitioning

Tue, 2018-09-25 00:06
Hi there We have a customer on 12.2 and is about use Unified Audit, since we have alot of FGA audit due to GDPR (450 G in 3? mth) - we chose UA because of its partitioning capabilites, thus beeing able to delete offload. Questions: 1 I know...
Categories: DBA Blogs

Pages