Tom Kyte

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:...

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...

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 <...

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...

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...

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 ...

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> ?...

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 ...

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...

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 );...

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...

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...

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 ...

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....

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...

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...

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...

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...

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...

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...