Tom Kyte

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

Random date between two date ranges

Mon, 2018-07-23 15:06
I need to pick one random date per month from July 2017 to June 2018. I have the below mentioned code which is only selecting them for entire 2017 Calendar year whereas I need it from July 2017 to June 2018. Any advise? with tst as ( Select lev...
Categories: DBA Blogs

table delete,truncate

Mon, 2018-07-23 15:06
Hi Team, In Our production environment When are checking the report is not working, after to analysis we found that there was master table has been truncated or deleted by somebody. Now we are trying to identify the sql_text against that table....
Categories: DBA Blogs

PL/SQL procedure and HTTPS request

Fri, 2018-07-20 14:06
Hi Tom, I'm trying to invoke a RESTful service (using HTTPS and JSON objects) from a procedure. The version of Oracle installed is: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2...
Categories: DBA Blogs

group records by interval of 3 seconds

Fri, 2018-07-20 14:06
Hi Tom, I am not able to find the exact answer to my question although I think I might need to use LAG and LEAD: I have records like this: Time Amount 11/22/2003 12:22:01 100 11/22/2003 12:22:03 200 11/22/2003 12:22:04 ...
Categories: DBA Blogs

enable trace for specific schema

Fri, 2018-07-20 14:06
Hi, We are having slowness issues so, I would like to activate the trace for 1 hour in my database for an specific schema, I have reviewed in several websites and I have these steps: 1- ALTER SESSION SET TRACEFILE_IDENTIFIER = "TEST_TRACE"; 2- A...
Categories: DBA Blogs

add subpartitions to partitioned table using alter table

Fri, 2018-07-20 14:06
Hi Tom, I have a list based partitioned table as shown below. create table t( col1 number, col2 date, data_id varchar2(10), archived char(1)) partition by list (data_id) (partition a_data_id values ('A'), partition b_data_id values ('B') )...
Categories: DBA Blogs

"Table access by index rowid" is slower than "table access by user rowid"

Fri, 2018-07-20 14:06
Hi Tom, Anonymous procedure having a type for table rowid on one column for a billion plus record table, i open a cursor selecting rowid of the table for rownum less than 200000, then i fetch a cursor bulk collecting from the table by limiting ever...
Categories: DBA Blogs

Select for update skip locked failing

Fri, 2018-07-20 14:06
Hi, I have a scenario where I have to get first 10 rows from filtered and ordered result set for update. As we are on multi user environment we are trying to use SELECT FOR UPDATE SKIP LOCKED. Let me put it in a simple example Scenario-1...
Categories: DBA Blogs

Rowtype variable in dynamic SQL

Thu, 2018-07-19 20:06
How to pass below variable in dynamic sql? v_emp_rec org.emp%rowtype; I am getting ORA-01008: not all variables bound error for below code. declare v_sql varchar2(1000); begin v_sql:='demo_pkg.demo_rates(p_rec=>v_emp_rec);'; execute ...
Categories: DBA Blogs

Line Level Logging using controlled FND_LOG\DBMS_OUTPUT statements vs DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Thu, 2018-07-19 20:06
Hi Tom, I have used DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for understanding the exact line where error occurred in a package which is exposed using a service outside. However one of my client feels I should write diagnostic message after certain set ...
Categories: DBA Blogs

hierarchical query with date consideration

Thu, 2018-07-19 20:06
How to include an effectivity date range check in hierarchical query? Such that a loop may exist if the date range is excluded, but considering the range a loop would not. <code>with output_tab as (select 'P1' as output_id, 100 as output_key, to_...
Categories: DBA Blogs

SYS_CONTEXT('userenv','module') behaviour in Database Vault

Thu, 2018-07-19 01:46
Hello Tom, I have implemented DB Vault on a 12.2.0.1.0 Oracle database. I created a Vault policy to block adhoc access to application schema using DB tools like Toad etc. The policy should allow only application connection to DB from application s...
Categories: DBA Blogs

Triggers on materialized views

Wed, 2018-07-18 07:26
Are triggers on materialized views supported by oracle? If so, is a good practice to use them?
Categories: DBA Blogs

IN & EXISTS

Wed, 2018-07-18 07:26
Tom: can you give me some example at which situation IN is better than exist, and vice versa.
Categories: DBA Blogs

Initializing Contexts after getting a connection from a ConnectionPool

Wed, 2018-07-18 07:26
I understand best practice is to initialize a connection from a connection pool by clearing the Application Context. However,there are multiple namespaces. Should every namespace be cleared? How does one find the name of every namespace?
Categories: DBA Blogs

DDL for objects

Wed, 2018-07-18 07:26
Hi, I wanted to get the DDL for all objects in a database schema. I'm aware of DBMS_METADATA.GET_DDL to get the DDL from a PL/SQL block but was facing the bellow issue: The return type is HUGECLOB and I need it in a directly viewable form simil...
Categories: DBA Blogs

Oracle Database Performance tuning using Application Developer ( Application user)

Wed, 2018-07-18 07:26
This question is from the context when App-server and Oracle Database is hosted on a vendor cloud. They are in a separate container and application will be in a separate container. Example, App-server and DB on Oracle cloud and GUI on customer's ...
Categories: DBA Blogs

Force logging

Tue, 2018-07-17 13:06
Hi Tom, As I have my dc and dr database are working fine and both are in sync. Today I got the force logging mode is no logging So we have planned to make it force logging enable. For that 1.how can we do on primary 2. It is required any downt...
Categories: DBA Blogs

SQL*Plus copy command errorring on CLOB

Fri, 2018-07-13 17:26
I am trying to move select records from one database to another using the copy command and I am getting this error below. Is there a way around this using the copy command? Thank you, A.J. SQL> copy from saturn/******@test insert SFRAREG ...
Categories: DBA Blogs

Global synonym not used

Thu, 2018-07-12 23:06
Hello, I need some help understanding the synonym behavior. We have a table in production database with a global synonym, which is being referenced by other objects in the database. As part of a weekly process that runs every weekend, we drop this...
Categories: DBA Blogs

Pages