Tom Kyte

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

How to Restore ASM Based OCR After Complete Loss of the CRS Diskgroup on Linux/Unix Systems for the 18.c version

Fri, 2020-05-22 02:26
I have seen tho note: "How to Restore ASM Based OCR After Complete Loss of the CRS Diskgroup on Linux/Unix Systems (Doc ID 1062983.1)" This note: "APPLIES TO: Oracle Database - Enterprise Edition - Version 11.2.0.1.0 and later" In the step ...
Categories: DBA Blogs

Making a transparent application database switchover with Data Guard

Fri, 2020-05-22 02:26
Hello, Ask TOM Team. I have a 2-node RAC production database (18.6) with Data Guard configured (using data guard broker). My app's connection strings are pointing to scan-name\db-service. What are the steps to make a database switchover withou...
Categories: DBA Blogs

Strange Behaviour with Oracle_Home and trailing slash

Fri, 2020-05-22 02:26
Hi, I have a server hosting several instances based on the same oracle engine 19.3 (19.0.0.0). As an example, let's consider DB1 and DB2. We connect to the server with a user account member of dba group. In the same user session : <code> e...
Categories: DBA Blogs

Clarification about A-Time column in execution plan

Fri, 2020-05-22 02:26
Hi Experts, I've the following execution plan. As you can see, the "A-Time" of operation 3 is bigger than its parent. Operation 2 behave in the same way with it's parent. Furthermore operation 0 has only 0.03 second. How can this happens? The "A-T...
Categories: DBA Blogs

View pdf file (saved in db server directory)through form or report in oracle ebs

Fri, 2020-05-22 02:26
I have this file named for example" contarct1.pdf" that is saved in the database server directory I can retrieve that directory path an details when i query from "dba_directories" view I need to view this pdf file to user with any of the two op...
Categories: DBA Blogs

Scheduler Jobs not starting at the requested time

Fri, 2020-05-22 02:26
Solaris 11.4 Oracle 12.2 Hi, we have about 70 Schedueler jobs defined, which are starting in intervalls from 1 minute to about 1 month. Sometimes (about 2 to 3 times a weeks) there are time-windows of about 1 to 60 minutes , in which none of th...
Categories: DBA Blogs

Date Partitioning a table

Fri, 2020-05-22 02:26
Hi Tom I have a system I am working on that will require old data to be removed every week or so. The customer does not want any down time. Is it possible to create a partitioned table that I could define 31 or so partitions and load data in a dif...
Categories: DBA Blogs

Want to pass schema name as dynamically mode

Thu, 2020-05-21 08:06
My question is over here that I just wann to pass 'Scott' schema name as dynamic,because schema name has changed every 2 month like 'scott.1' and ' scott.2' and so on. Select prod_id,prod_name from product A,scott.product_details B where A.prod_...
Categories: DBA Blogs

Keep changed data in a separate table

Thu, 2020-05-21 08:06
Hi, Application team have a requirement to keep data changes on particular tables (All dml's) for 4 days to generate reports. Initially I found below 3 options - 1. Create a trigger for DML's on tables which will insert changed data in another...
Categories: DBA Blogs

Partial indexing using multiple function indexes or precomputed json object

Wed, 2020-05-20 13:46
I have an orders table with a status flag showing whether that order has been completed. Most orders in the tables are completed: probably only 5%ish of the table are incomplete. I know I can build a partial index by using a function based index whi...
Categories: DBA Blogs

Optimize a Package

Wed, 2020-05-20 13:46
Hello Experts, We have a package with object of 50 variables. Package will be invoked with 400 to 500 records with input object and We are looping through this 400 to 500 records and do some manipulation and insert into 4 tables which has sequence...
Categories: DBA Blogs

INDEX SCAN ON TIMESTAMP COLUMN

Wed, 2020-05-20 13:46
Hi Tom, PFB query structure: <code>select /*+ gather_plan_statistics */ count(*) FROM CLPT INNER JOIN SL ON CLPT.SHIPPER_LOT_ID = SL.ID AND CLPT.CLIENT_ID = SL.CLIENT_ID WHERE CLPT.CLIENT_ID = 3104 and CLPT.STATUS = 1 AND CLPT.CR...
Categories: DBA Blogs

Peculiar partitioning method and automatic multiple column list partitioning.

Tue, 2020-05-19 19:26
Hi I am a great fan of Oracle partitioning. In our environment there is however few shortcomings - unability to partition data by more than one date column - unability to subpartition data by non fixed number of partitions. (without any human...
Categories: DBA Blogs

Few of the internal users are under USERS scheama

Tue, 2020-05-19 01:06
Hi, we can see few of the internal schema users maintained by oracle database are under USERS tablespace instead of SYSAUX, can we change default tablespace on the fly for these schemas? Oracle Internal schema users: <code> USERNAME ...
Categories: DBA Blogs

Creation of Dynamic Region OR Dynamic Interactive Report

Tue, 2020-05-19 01:06
Before understand question mind I have one table with 3 columns one is id second is description and third is sql query, i created one Apex page with two region one is static region and one is Interactive Report Region. In Static region i have one ...
Categories: DBA Blogs

Reset of the master encryption key of the database.

Tue, 2020-05-19 01:06
Database(non-CDB) version - 18.8.1.0.0 TDE is enabled, Wallet_location is in ASM, software keystore is configured and auto-login is created. Only tablespace encryption is enabled at the moment. Backup of the encryption keys (ewallet.p12 & cwallet....
Categories: DBA Blogs

performing a select on a table using the contents of owa_util.ident_arr

Tue, 2020-05-19 01:06
my apologies a test case via LiveSQL is uploaded, but maybe not workable due to use of compiling package for mod_plsql in OAS 9 (working to move to ORDS). also, it is not so clearly providing what URL I may give here for your easy reference. I ha...
Categories: DBA Blogs

Packages and Oracle types and PGA

Tue, 2020-05-19 01:06
Hi, I Have a package, getting the data from the multiple table once and put it in the one user defined type and then using the data from that type i have using another 4 or 5 types to manipulate the data as per business logic and send the outputs....
Categories: DBA Blogs

PLSQL Package size

Tue, 2020-05-19 01:06
Hi, I am having a package with 10 Procedures in it. I am calling all the procedure's in the package in async call from different sessions. Is there any memory/performance impact on this.
Categories: DBA Blogs

connection settings optimization for a satellite channel between client and server (ping > 1.5 seconds)

Tue, 2020-05-19 01:06
Hello. Please if you can give any advice for a specific situation: a client (PL SQL Developer, Toad, or any other development tool) connects over a VPN to a server thru a satellite channel (quarantine and remote work). No big amounts of data tran...
Categories: DBA Blogs

Pages