Tom Kyte

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

dbms_scheduler

Thu, 2016-11-03 10:06
How dynamical set ARGUMENT value for a program that is called in the chain step ?
Categories: DBA Blogs

Meta data

Thu, 2016-11-03 10:06
Hi, I aasked below question to my friend Why oracle created views for all system tables? and he said to protect the base tables (i.e hiding name by creating view), iam not satisfied with that question because by plsql developer we can know t...
Categories: DBA Blogs

Proper way to compare difference between between 2 dates to a constant

Tue, 2016-11-01 21:26
Hi Tom, I am writing some code to check if the number of hours between two dates (in HH24MI) is equal to 0.5 hour, but the results vary depends on where the "24" factor is placed. Here is my testing script: >>>>> SELECT -- case 1: 1...
Categories: DBA Blogs

Performance tuning document

Tue, 2016-11-01 21:26
Dear Tom, Because of you i learn oracle. Could you please provide me Performance tuning documentation. Regards RAJ..
Categories: DBA Blogs

Schema

Tue, 2016-11-01 21:26
Hi tom , Can we create a schema without creating user to that schema to store objects?
Categories: DBA Blogs

Schema with ORM JPA

Tue, 2016-11-01 21:26
Hi there, please pointed me into the right direction. I have recently been assigened a small project - is to do with cateloging historical insurance complaints that filed by customers. my background is more to Java and have basic undestanding to...
Categories: DBA Blogs

Data Dictionary

Tue, 2016-11-01 21:26
Hi, I excuted below query SELECT * FROM dba_objects WHERE object_name LIKE 'DBA_OBJE%' then i noticed on thing that dba_objects view and synonym has same name,but oracle doesnot allow us to create any data base object with same name...
Categories: DBA Blogs

Whenever sqlerror - transferring text varuiable into shell

Tue, 2016-11-01 21:26
Hello, Tom. The description of WHENEVER SQLERROR instruction says, that one can define EXIT {variable} in it. is it possible to return the text variable? I need sql-script to return into shell-script (by which it's run) not onlu SQLCODE (or a pre...
Categories: DBA Blogs

Rowid of a table

Tue, 2016-11-01 21:26
Hi! Team, Is there a way whereby we can get all rowids of a table without querying the table but some dba views or other stuffs? Actually we have a procedure which calculates cost of goods based on FIFO method. We have a table where all inventory re...
Categories: DBA Blogs

LISTAGG

Tue, 2016-11-01 03:06
Hello, I need to create a list of distinct values. Here's my test case: create table t(id number,dt date,txt varchar2(1)); alter table t add constraint t_pk primary key(id); create sequence t_seq; insert into t values(t_seq.nextval,trunc(sysdat...
Categories: DBA Blogs

Impact on DB performance with enabling SQL Plan Baseline

Tue, 2016-11-01 03:06
Hi Tom, Our database version is 12.1.0.2, running on exadata platform. For stabilizing the performance, we are planning to enable SQL Plan baselines in our database. By default, the parameter "optimizer_capture_sql_plan_baselines" is F...
Categories: DBA Blogs

Database context needs to auto create every time the DB startup

Tue, 2016-11-01 03:06
I'm using 11.2.0.3 DB. I created a database context using the sql below. This works fine but if I restart the DB this context does not exists any more. I have to logon and run this command again. I'm not sure if this is intended functiona...
Categories: DBA Blogs

Find user who performed DDL

Tue, 2016-11-01 03:06
Hi, I need your help to find the name of the user who performed DDL on a specific table eg. table emp. How can i find this ? Note ,audit is disabled in my db. os:rhel 6 db:11.2.0.3
Categories: DBA Blogs

Meta data about Users

Mon, 2016-10-31 08:46
Hi tom, I would like to know the table name which contains the users details created in database. example user_objects meta data contain objects created by the user. thank you.
Categories: DBA Blogs

How to identify operational days between sets of dates and also non-operational periods (gaps)

Mon, 2016-10-31 08:46
I have a simple table with following data: (PK, StartDate, EndDate) for which I want to know what are the periods of operation and what are the non-operational periods. For example: StartDate EndDate 15-Apr-16 29-Apr-16 29-Apr-16 30-Apr-16 ...
Categories: DBA Blogs

Hard parse /Soft parse

Mon, 2016-10-31 08:46
How to identify a query executing with soft parse /Hard Parse? Thanks & Regards Krishna.
Categories: DBA Blogs

Hierarchical structure from children tables

Mon, 2016-10-31 08:46
Hi, I need to copy data from a source database to a remote destination database (via dblink). Currently, my procedure (that runs periodically once a day), acts in this way: 1) disables all foreign keys on the destination database; 2) copies t...
Categories: DBA Blogs

Select statement sort without order by clause

Mon, 2016-10-31 08:46
Tom, I am looking in the documentation where it states oracle doesn't guarantee the order of a select statement without an order by clause. I'm having trouble finding in to prove it to the higher ups. I know its a shot in the dark but do you know of...
Categories: DBA Blogs

DBMS_ASSERT

Sun, 2016-10-30 14:26
DBMS_ASSERT package is used in NDS to validate data and to prevent SQL injection attacks. What is the difference between QUALIFIED_SQL_NAME, SIMPLE_SQL_NAME and SQL_OBJECT_NAME? This is confusing. Please explain with practical examples.
Categories: DBA Blogs

Date range grouping for same price and stop sale option

Sun, 2016-10-30 14:26
Hi Tom, Please disregard my previous ticket as I am not able to edit and the query requirement has changed. Thanks for the link by I am struggling a bit. I have a table of daily rates for a service that contains a price and an Y/N field to sto...
Categories: DBA Blogs

Pages