Tom Kyte

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

Open database read only

Tue, 2018-01-30 12:06
What is a command to change DB open mode from read write to read only without restarting the instance
Categories: DBA Blogs

How to find last inserted records from table

Tue, 2018-01-30 12:06
HI TOM, PLEASE EXPLAIN How to find last inserted records from table? IN ORACLE? THANKS BALA
Categories: DBA Blogs

Obtain all JSON keys from JSON columns

Tue, 2018-01-30 12:06
Hello, Is there a way to get all the keys that exist in a JSON column? JSON_VALUE function provides an easy way to access a value for a given key, but is there a way to obtain all the key names? Thanks, Gabi
Categories: DBA Blogs

Multiple SQL Profiles for one SQL

Tue, 2018-01-30 12:06
Is it possible to have multiple SQL Profiles for same query?
Categories: DBA Blogs

Database Performance Different Plans after Database Migration

Tue, 2018-01-30 12:06
I am facing the issue with database performance. Earlier, my database was running on XSeries Platform, Solaris OS. Then I migrated whole data and object to SPARC Platform. Both Servers have a bit different specs. For XSeries, data stores on S...
Categories: DBA Blogs

Re-enable of foreign key constraint takes a long time

Tue, 2018-01-30 12:06
I have two 11.2.0.3 databases on Solaris 11 64bit. They both have the same schemas. One is approx. 1GB and the other is approx. 10GB. On both databases, there is a parent and child table with similar number of records (~300k records each). Fr...
Categories: DBA Blogs

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

Pages