Tom Kyte

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

conceptual question regarding ODBC driver

Tue, 2018-01-30 12:06
Hi Tom: I used to work as a analyst who developed SQL queries mainly using SQL developer and SSIS, and recently I have moved to a new team which I have slightly different role , leading me to some questions I never consider before. I am not so sur...
Categories: DBA Blogs

Performance issue while processing Huge XML file

Mon, 2018-01-29 18:06
I'm seeing performance issue while processing the xml file which has more then 10K records, it's working fine if the file has 100 records. Below is the sample procedure I'm using.. <code>++++++++++++++++ CREATE OR REPLACE PROCEDURE process_xml_fi...
Categories: DBA Blogs

Audit logon and logoff of specific users eg sys and system

Mon, 2018-01-29 18:06
Hi, I need to write a trigger to audit logon and logoff activities of specific users 'SYS' and 'SYSTEM'. I have one trigger but its not inserting records into the table mentioned in the trigger. Please, help me to fix the issue.Your help will be...
Categories: DBA Blogs

How to unpivot table data with out displaying column names

Mon, 2018-01-29 18:06
Hi Tom, Am working on Oracle DB and below query <code> select t1.id as dbid, t2.mid as askid, t3.m2idd as amid from table1 t1, table2 t2, table3 t3 where t1.actid = t2.senid and t2.denid = t2.mkid ...
Categories: DBA Blogs

SQL query that returns the difference between the latest

Mon, 2018-01-29 18:06
Problem Statement [question redacted] Comment from Connor, Jan 29 1018 ================================ You can see from our initial answer that were thrilled that you provided us a complete test case. However, when things seem too good to b...
Categories: DBA Blogs

SQL Query Optimization

Mon, 2018-01-29 18:06
We have a table called PROTOKOLL, with the following definition: <code> CREATE TABLE "PROTOKOLL" ( "UUID" CHAR(36 BYTE) NOT NULL ENABLE, "VERSIONSNUMMER" VARCHAR2(20 BYTE), "TYP" CHAR(1 BYTE) NOT NULL ENABLE, "BENUTZER_ID" VARCHAR2(25...
Categories: DBA Blogs

Creating Indexes on Custom Table

Sun, 2018-01-28 23:46
Hi Tom, I have below query <code> select a.order_number from xxdm.XXARX_INV_SOURCE_TBL_28_jan1 a,XXARX_INV_CNV_TBL_28th_jan1 b where a.trx_number = b.trx_number and a.customer_number_source = b.customer_number_source and a.trx_number = :t...
Categories: DBA Blogs

Temporary Table Questions

Sun, 2018-01-28 04:26
Tom, I have a few questions abt temporary table: 1. Which type of temporary table is better to use in a multi-user applications, a global temporary table with on commit preserve rows or a global temporary table without on commit preserve r...
Categories: DBA Blogs

How to capture user and encrypted password to be used in alter user statement after duplication of database

Sat, 2018-01-27 10:06
We perform a duplicate database from prod to test weekly and have a script that captures the current user permissions on dev via the dbms.metadata.get_ddl package that generates DDL to another file to be used after the duplication to re-create any us...
Categories: DBA Blogs

SQLcl spool numeric field as Text

Sat, 2018-01-27 10:06
Hello Tom, I love SQLcl for spooling data to a CSV-File for using in Excel. But i have trouble with comma inside a field. This is my SQL-Statement: <code> SET LINESIZE 3000 SET TRIMSPOOL ON SET PAGESIZE 0 SET SERVEROUTPUT ON SET VERIFY...
Categories: DBA Blogs

Identifying PL/SQL function call from SQL

Fri, 2018-01-26 16:06
Hello, Firstly, thanks for fantastic job You're doing - i (and i hope many others) really appreciate it. I wanted to ask if there is an automated way to identify PL/SQL functions called from SQL. I would like to prepare list of "candidates" for ...
Categories: DBA Blogs

How to find sql_id of PL/SQL

Fri, 2018-01-26 16:06
Dear Team, How to find the sql_id associated with execution of a particular pl/sql procedure on a given day in past to see problem. means how can i find pl/sql sql_id and its last execution date and time. Thanks Pradeep
Categories: DBA Blogs

Performing updates to multiple columns from two tables

Fri, 2018-01-26 16:06
Hi, I have two tables. Table A and Table B. I'm trying to perform an update on 1 column from table A and 2 columns from table B. I can of course do this with two separate update statements. But I would like to know whether it's possible to perform...
Categories: DBA Blogs

Error ORA-29861 on XDB.XMLIndex

Fri, 2018-01-26 16:06
Hi! I have a problem with XDB.XMLIndex. First of all, I create table: <code>CREATE TABLE DBF_LOG_TST ( DBF_HEADER CLOB, DBF_DATA XMLTYPE ) XMLType COLUMN DBF_DATA STORE AS BASICFILE CLOB (TABLESPACE LOB STORAGE (INITIAL...
Categories: DBA Blogs

What do I need to import Oracle dmp file from Oracle RAC into an offline standalone laptop? What application(s) is necessary?

Fri, 2018-01-26 16:06
Aloha Tom, I used to work with Oracle (years ago), I've been working on virtualization and SQL server from past 15 years, now I have a task at my new job that I cannot figure out what would be best and required for it. They have exported Oracle RAC...
Categories: DBA Blogs

Running Total

Fri, 2018-01-26 16:06
Hello Sir, Could you give a simple example of running total on emp table FOr example, running total of sum (sal + comm) department wise? thanks
Categories: DBA Blogs

Pausing scheduled jobs

Fri, 2018-01-26 16:06
I am trying to determine a way where we can schedule time periods for when select dbms_scheduler jobs will automatically get "paused". I came up with a procedure (see LiveSQL Link) that works fine when run as an anonymous block. It successfully dis...
Categories: DBA Blogs

Before/after insert,update,delete trigger working on SQL*Plus but not in SQL Developer

Thu, 2018-01-25 21:46
Hello, So I have this very basic trigger script <code>set SERVEROUTPUT ON; create or replace trigger bi_superheroes before insert ON superheroes for each row ENABLE Declare v_user varchar2(20); Begin Select user into v_user from dual; ...
Categories: DBA Blogs

How to determine the exact cause of this error: ORA-07445

Thu, 2018-01-25 21:46
Hello, I am receiving the following error in the alert log of my DB: Errors in file /u01/app/oracle/admin/oraprod/udump/oraprod_ora_239365.trc: <b>ORA-07445: exception encountered: core dump [unknown] [SIGILL] [Illegal opcode] [0x000000000] [] []...
Categories: DBA Blogs

Find Column Size

Thu, 2018-01-25 21:46
Hi, Is there an accurate way of determining the size of a column in a table. Mutiplying the DATA_LENGTH from dba_tab_columns for that column with the number of rows in the table makes sense? Actually, we got request from App team that they want...
Categories: DBA Blogs

Pages