Tom Kyte

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

Gather Stats while doing a CTAS

Fri, 2019-12-06 17:53
Can you please provide your opinion on the below point. This is what I have noticed. When we create a table using a CTAS, and then check the user_Tables, the last_analyzed and num_rows column is already populated with accurate data. If it is so, ...
Categories: DBA Blogs

How can application control to explicitly call OCIStmtPrepare2 rather than OCIStmtPrepare when using pro*C

Fri, 2019-12-06 17:53
Our application got an ORA-25412: transaction replay disabled by call to OCIStmtPrepare. Oracle Version: 12.2. The Oracle runs in RAC mode. After searched on the internet, we found below explanation: <i>This call(OCIStmtPrepare) does no...
Categories: DBA Blogs

'BEFORE CREATE ON SCHEMA' trigger apparently not firing before Create Table

Fri, 2019-12-06 17:53
In Oracle 8.1.7 instance set up with characterset US7ASCII <code> Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option JServer Release 8.1.7.4.0 - Production SQL> create table t1 (c1 varchar2...
Categories: DBA Blogs

Left Joining Four Tables without duplicates from right tables or Cartesian product!

Fri, 2019-12-06 17:53
I am running the query below to get data from 4 tables, but the problem that data is fetched as Cartesian product, in other words, items from tblEdu is being duplicated with items from tblTrain <code> SELECT tblpersonal.*, tbltrain.*, tbledu.*,...
Categories: DBA Blogs

Observation regarding Interval partitioning

Mon, 2019-12-02 17:52
<b>Hi, <b>I had some observation regarding Interval partitioning when I was looking into one issue,</b> Below is the use case </b> For ex : We have two tables <code>CREATE TABLE TEST_GURU_1 ( ENAME VARCHAR2(500), EMPDATE DATE ) ...
Categories: DBA Blogs

Parallel execution of procedure like multithreading in java

Mon, 2019-12-02 17:52
Hi All, Consider below sample Data model: Application is maintaining information of different countries - States -Cities (each of this is individual tables). At the end of quarter we are doing assessment and calculating different metrics at countr...
Categories: DBA Blogs

A stored procedure taking 98% CPU time as a percentage of Elapsed Time

Mon, 2019-12-02 17:52
A stored procedure taking 98% CPU time as a percentage of Elapsed Time. Please provide us the fix and recommendations to check
Categories: DBA Blogs

Password change of remote database user by dblink but showing error as "ORA-06550: line 1, column 8: PLS-00352: Unable to access another database &#x27;SYS_DBALINK"

Mon, 2019-12-02 17:52
HI,i created a form in Oracle Apex and when i click the submit button then Stored Procedure runs that changes the remote database user password over dblink but iam getting the error as "ORA-06550: line 1, column 8: PLS-00352: Unable to access another...
Categories: DBA Blogs

insert records into multiple tables

Mon, 2019-12-02 17:52
Hai bro, How to insert a records for multiple tables with out using INSERT ALL and with single INSERT statement only, is that possible ?
Categories: DBA Blogs

DDL related parse, execute, fetch

Mon, 2019-12-02 17:52
Dear Mr. Tom, When we issue a DML it undergoes PARSE EXECUTE FETCH process and the things happen in Database Buffer Cache. Kindly tell me when we issue a DDL then PARSE EXECUTE is understandable but what about FETCH? What it will...
Categories: DBA Blogs

Plan changes when json_arrayagg function got added

Mon, 2019-12-02 17:52
Team, could you please help us to understand why the plan changes when json_arrayagg function got added? <code> demo@PDB1> create table t as select * from all_objects; Table created. demo@PDB1> alter table t add constraint t_pk primary ...
Categories: DBA Blogs

transportable DBF Import in 12c

Mon, 2019-12-02 17:52
i'm trying to import transportable data files to Oracle DB 12.2 . These files are exported from as transportable from Oracle DB 11.1.i recieve the following error. ORA-39123: Data Pump transportable tablespace job aborted ORA-19721: Cannot find dat...
Categories: DBA Blogs

How to extract table data into CSV file dynamically using generic procedure

Mon, 2019-12-02 17:52
Hi, Need help on how to generate the CSV file for the given tablename dynamically using PLSQL procedure. I understand we can use UTL_FILE oracle package to generate the CSV file however I would like to know how we can create generic script which ...
Categories: DBA Blogs

Multiple Schema Oracle Wallet

Mon, 2019-12-02 17:52
Dear AskTom, I have a shell script that connects as several different users to the same database. From Oracle: You can store multiple credentials for multiple databases in one client wallet. You cannot store multiple credentials (for logging i...
Categories: DBA Blogs

Nightly process slowing down.

Thu, 2019-11-21 11:50
Hi. We have a process that runs every night that is beginning to slow down and we need some help to find the resources to analyse the problem. In our setup, unfortunately both transaction schemas and warehousing (statistics) schemas are kept on...
Categories: DBA Blogs

Golden Signals for performance

Thu, 2019-11-21 11:50
Hello Tom I am a member of an site reliability team (SRE) and we are trying to develop SRE "golden signals" for an Oracle 11g/12c database. These signal are: 1) Throughput 2) Latency 3) Response Time 4) Error rate (not sure about this one...
Categories: DBA Blogs

Difference between DBRM and IORM

Thu, 2019-11-21 11:50
Dear Sir, Please help me to know below points. 1)difference between DBRM and IORM? 2)difference between ACFS,ADVM and DBFS? Thanks Pradeep
Categories: DBA Blogs

SQL*Loader-503 in 19c for UNC names

Thu, 2019-11-21 11:50
Hi TOM, next Month we are migrating from Oracle 12.1.0.2 to Oracle 19, so I installed the new Oracle Client 19c on my PC. Using SQLLDR with (all in one line) sqlldr userid=user/passwd@server control=\\server\share$\folder\controlfile.ini ...
Categories: DBA Blogs

future of dba

Thu, 2019-11-21 11:50
Dear, I am Core Oracle DBA. I do not have experience in RAC,Golden Gate. Sometimes I worried about future of oracle dba in upcoming years in India for all industry like medium and large company. Can you give some recommendation or new technology n...
Categories: DBA Blogs

questions about sampling

Thu, 2019-11-21 11:50
<b>a. how does sampling work internally ?</b> meaning: how is it determined by Oracle which blocks are read by a sample-query and which not ? <code>create table t as select level i, rpad('a', 1000, 'a') s from dual connect by level <= 1e5; cre...
Categories: DBA Blogs

Pages