Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 2 weeks 5 days ago

Pivot with list of rows

Thu, 2019-09-26 06:46
We have a table which contains db_name and usernames. In the output we need list of users per DB i.e. number of columns will be equal to distinct db_name. sample output format: <b>DB1 DB2</b> USER1 USER4 USER2 USER5 USER3 Database version:...
Categories: DBA Blogs

confuse at the order of execution plan table

Thu, 2019-09-26 06:46
As we were told that "The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is executed first." howe...
Categories: DBA Blogs

Converting data types in where clause

Thu, 2019-09-26 06:46
Hi Tom, My question is regarding when a query is not having the right datatype in the where clause Example: -- Create table <code>CREATE TABLE mytable ( mynumber varchar2(20), primary key(mynumber));</code> -- Insert some rows <...
Categories: DBA Blogs

Resetting a live sequence

Thu, 2019-09-26 06:46
A sequence was about to finish, so I had make it bigger. I work on database 11.2 so I had to use a workaround described here (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1119633817597). The procedure shown in LiveSQL was ru...
Categories: DBA Blogs

Strange behavior in analytic functions with partitions

Thu, 2019-09-26 06:46
Hello, I have met strange behavior that I can't understand. I have this table <code>CREATE TABLE test_table (id NUMBER(10,0) NOT NULL, register_date DATE DEFAULT sysdate NOT NULL, row...
Categories: DBA Blogs

Issue in generating Custom Reference ID and Mapping with Form's field

Thu, 2019-09-26 06:46
Building an app for blocking a demo calendar for particular product setup. I wanted to create a custom reference ID ( CURRENT_MONTH-CURRENT_YEAR-SEQUENCE like SEPT-2019-003) which will be the Primary Key (Column BOOKING_REF) for the table (table ...
Categories: DBA Blogs

Inserting without a full list for field names

Thu, 2019-09-26 06:46
We have an issue when we perform an insert like this <code> INSERT INTO STS_RESP_LOG(STS_REQ_LOG_SYSTEM_ID,HSTRY_FLG, SNGL_STS, FRQNCY, CRT_DATE ) VALUES ( ?, ?, ?, ?, sysdate); </code> ?...
Categories: DBA Blogs

RMAN MAXPIECESIZE VS SECTION SIZE

Thu, 2019-09-26 06:46
HELLO , i've made some test to try to parallelizethe best the backup of bigdatabase and i wanted to know if it's possible to parallelize the backup of a backuppiece (multiple backupset) on mulitple channel with maxpiecesize because our SBT media is ...
Categories: DBA Blogs

anytype from java for anydataset

Thu, 2019-06-06 10:06
Hello TOM :) I try to make anytype in java stored procedure and use it to create anydataset in PL/SQL. But I get error ORA-22625. Why this error occures and how can I fix it? I do it according to guides, for example https://docs.oracle.com/databas...
Categories: DBA Blogs

Best performance of Top N by X

Thu, 2019-06-06 10:06
I have the following 2 tables: <code>CREATE TABLE accounts( id NUMBER unique not null, account_name VARCHAR2(30) ); CREATE TABLE log_data( account_id NUMBER not null, log_type NUMBER, log_time TIMESTAMP, msg CLOB );...
Categories: DBA Blogs

PLS_INTEGER versus NUMBER versus "dynamic types"

Thu, 2019-06-06 10:06
It has been suggested to me that I use PL/SQL declarations like PROCEDURE foo ( p_id IN PLS_INTEGER )... instead of PROCEDURE foo (p_id IN NUMBER ) ... or PROCEDURE foo (p_id IN mytable.my_id%TYPE ) I've always preferred the last option, si...
Categories: DBA Blogs

Using MERGE to update data 2 times

Thu, 2019-06-06 10:06
I am having a Full dump of 1m records arriving every day.I need to obtain this set into STG. There is soft delete records thus when I compared between Full dump(source) and STG(target), the records in STG always larger than source. I have researc...
Categories: DBA Blogs

Function comparing dates in a range

Thu, 2019-06-06 10:06
I have a function that will extract records from a databaase with specific dates and times. One of the parameters passed is a date. This functions runs twice a day On of the parameters passed is AM or PM). My functions works for morning (times are ...
Categories: DBA Blogs

Pivot with dynamic dates column

Thu, 2019-06-06 10:06
Hi Tom, I want to show the absence of my people in an APEX form / editable grid. The absence is shown for every day and the day column should be generated automatically. Current SQL code: <code>with rws as ( select person.name, absence....
Categories: DBA Blogs

Synchronizing database sequences during manual data replication

Thu, 2019-06-06 10:06
Hi Tom, Experts, I am in a need to replicate manually all objects from one schema to another (manually, because my schemas, both source and target, reside in the cloud and I have no access to any OS level utility, nor sql*net access to the databas...
Categories: DBA Blogs

Filter Incremental Data in Oracle DB

Thu, 2019-06-06 10:06
Hi TOM, I have requirement to filter differential/Incremental data in Oracle Database. Primary key that I use is composite key (combination of 2 columns). They are in such a shape that I am unable to prepare 'where' condition that can help me to id...
Categories: DBA Blogs

Connection Storm with Inactive sessions on ORACLE RAC

Thu, 2019-06-06 10:06
Hi All, We recently migrated from Solaris to AIX and moved to Oracle RAC : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for...
Categories: DBA Blogs

Using Oracle best way

Thu, 2019-06-06 10:06
Hi, We have set of Mainframe programs which connects to Oracle database and queries set of tables. Our application and DB servers are in 2 different locations (which we cannot change at the moment), which i believe is causing considerable delays w...
Categories: DBA Blogs

Loading email content into oracle table

Fri, 2019-05-17 21:46
Hi Tom, I have an interesting requirement, I want to load complete emails ( example outlook) in oracle tables. => When a mail content ( preview) is greater than 4000 than store in attachement table with name "long content" and store complete...
Categories: DBA Blogs

Ora-12560: TNS: protocol adapter error

Fri, 2019-05-17 21:46
I use a single instance 12.2C 64-bit Oracle database on a window server 2012R2. suddenly this error <i>ORA-12560: TNS:protocol adapter error</i> began to show when I try to enter the sqlplus. whatever I have searched the internet a lot for a soluti...
Categories: DBA Blogs

Pages