Tom Kyte

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

Row visibility set in a package procedure EXECUTE IMMEDIATE 'ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE'

Wed, 2018-11-07 11:06
Hi Tom, I've got a table DAVE_ELOF set to row archival (alter table dave_elof row archival;). I want that a user can set row visibility via a package procedure setStudyVisibleYN(aStudyId number, aVisible_YN varchar2). Code snipp...
Categories: DBA Blogs

Parallelism in IOT's

Wed, 2018-11-07 11:06
I am trying to join (outer join) a number of IOT's all with the same key in Parallel Mode; and am expecting the optimizer to use a Merge Join in order to avoid sorts (as the documentation clearly says it should); but it does not. - I'm tracing the pr...
Categories: DBA Blogs

history of session for particular sql id

Wed, 2018-11-07 11:06
I have a query which takes times to execute. When a user connects to the system and I monitor database I also get the SQL statement which takes times. but when user complete his task and after days he talks to me report takes too much time. I analyz...
Categories: DBA Blogs

How to determine the faster of two queries based on the explain plan

Wed, 2018-11-07 11:06
Hello I have two SQL scripts that are producing the exact same result (returning approx. 30000 rows) but the first one does it for 30min. and the second one for 2min. The slower query has (by my understatnding) the better explain plan (because it ...
Categories: DBA Blogs

Hierarchy Validation - Incoming hierarchy vs Expected hierarchy

Wed, 2018-11-07 11:06
Hi Experts, I have a requirement to validate the hierarchical structure of flat files which are loaded into the database against an expected structure that is known. I have created some sample data to demonstrate what I would like to do: <code>...
Categories: DBA Blogs

Flashback data archive for auditing

Wed, 2018-11-07 11:06
Hi Tom, Greetings !! we would like to leverage the 18c Flashback Data archive feature for auditing in our environment. Currently its trigger based with some business logic implemented in it and only 30% of tables being audited and capturing all...
Categories: DBA Blogs

Which is better to use Direct SQL or Bulk Collect

Wed, 2018-11-07 11:06
I have a source table of almost 20 million records. I have to load another table from source table after some checks and validation. What would be the better way to transfer, Insert through Direct SQL or Use Bulk Collect. what is difference between...
Categories: DBA Blogs

How to prevent some firewall from terminating SQL*Plus sessions with long-running statements?

Wed, 2018-11-07 11:06
Hi Tom, In know that I should talk to the firewall admins to get things fixed but this is - unfortunately - out of scope. So here we go: We have a database running on some Linux server and some client application running on some W2k16 server. T...
Categories: DBA Blogs

Who last updated an Oracle object

Wed, 2018-11-07 11:06
Hello, My goal is to produce a list of all objects (procedures, packages, functions, triggers, and tables) that have changed in the database since they were first created. I have a query that compares "created" and last_ddl_time" in the vi...
Categories: DBA Blogs

Counter to indicate rate changes

Tue, 2018-11-06 16:46
Hi Tom! Glad to have you back. Oracle: 12.1.0.2 My challenge is as follows: The t_res table contains hotel room reservations that consist of a reservation id (res_id), date of stay (res_date), and a rate card (rate_id) which typically dic...
Categories: DBA Blogs

How to split big union all query

Tue, 2018-11-06 16:46
Hi Tom In my application uses SSRS tool and PLSQL .According the requirement I have category as a in parameter like 'AD','SA','SSV' and 'SOA' , so I have written single query with union all operator for all category. Now problem is my query has b...
Categories: DBA Blogs

Data pump import unable to import table that data pump export exported

Tue, 2018-11-06 16:46
I'm having trouble importing a table that export exported. The error I'm getting is "ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL", but according to the documentation "The partit...
Categories: DBA Blogs

Authorization with External User Access in Application Express

Mon, 2018-11-05 22:26
I need to be able to have external (business) users to be able to access only a few features of the site, for example, accounting can only see pages A and B while executives can see A, B and C. I need to be able to have multiple groups of people...
Categories: DBA Blogs

Execute package procedures in parallel

Mon, 2018-11-05 22:26
Hi all, For our customer, we start a process in the night to generate orders for 400 stores. Each store can regenerate its order during the day so the procedure is executed separately for each store. For each store we start a scheduled job with...
Categories: DBA Blogs

Insert Rows from one table to columns of other table.

Mon, 2018-11-05 22:26
Hi, Hope you are doing good and the Open World was exciting. I have got 2 tables, Table1 has the columns and data that has to be inserted as rows into Table2. I have shared an example of 5 columns, however the number is not constant. This can ch...
Categories: DBA Blogs

Upgrade DR to 12.2.0.1

Mon, 2018-11-05 22:26
Hi, The project is to upgrade 11gR2 RAC database to 12cR2(12.2.0.1) with minimal downtime. The approach is using standby database. Logical standby database cannot be done. Number of nodes in 11gr2 cluster 2. What i am doing >>> 1> P...
Categories: DBA Blogs

Query Builder does not Auto Join Tables in version 18.2.0.183

Fri, 2018-11-02 22:06
Your blog is terrific, BTW... When I drag and drop tables into query builder within SQL*Developer, the "chain link Icon" does not appear next to the table graphic. The tables have enabled foreign key constraints, however query builder will not sh...
Categories: DBA Blogs

Redo log and controlfile I/O contention.

Fri, 2018-11-02 03:46
Hi Tom, Can you explain to me how often an Oracle database needs to read from the control file? And can the placement of the control file on the same disk/diskgroup as the redo logs cause I/O contention? Problem context: We have a 3 node RAC...
Categories: DBA Blogs

DB Copy

Fri, 2018-11-02 03:46
Hi, Tom. I have two DB instance on different server. One DB is main, second db is "archive". We need copy only data from first db to second which contains CLOB and BLOB info, whole table. It's not copy all data from one to second. It's copy some da...
Categories: DBA Blogs

Manual dataguard

Fri, 2018-11-02 03:46
hi there we are using oracle database 11g (11.2.0.1.0 Standard Edition one) for our production db i have gone through the docs to setup automatic log apply which is available only for Enterprise Edition.. i saw the notes from the doc saying ...
Categories: DBA Blogs

Pages