Tom Kyte

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

Tuning SQL with MIN and MAX functions

Fri, 2017-10-27 11:26
<code>create table test_dept (dept_id number, dept_name varchar2(50), dept_region number); alter table test_dept add constraint pk primary key (dept_id); create table test_emp (emp_id number, sal number, dept_id number , constraint fk f...
Categories: DBA Blogs

AWR report

Fri, 2017-10-27 11:26
Hi Tom, Can you please explain how to look AWR report in simple term as i am very new to this. Thanks alot.
Categories: DBA Blogs

FGA Auditing - How to avoid false positives when FGA is on a table column and that column is in a view and other view columns are selected?

Fri, 2017-10-27 11:26
I'm new to FGA auditing and have created an FGA policy for SELECT on table T column SSN. This works well. Any user that specifically selects column T.SSN, generates a row in my audit table. (I'm using DB 11.2.0.4) select T.SSN from T; -- generate...
Categories: DBA Blogs

All about the DUAL table

Fri, 2017-10-27 11:26
Hi Tom Feels good to post a question after quite some time. Look at the following : SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------...
Categories: DBA Blogs

AskTom Architecture

Fri, 2017-10-27 11:26
Hi Tom, Thanks for this great resource! I am sure there are many others out there like myself who spend several hours a day reading the questions and your answers. Since so many people seem to be very curious about the architecture of your site...
Categories: DBA Blogs

DBMS PARALLEL EXECUTE : Chunk details

Fri, 2017-10-27 11:26
Hi Chris/Connor, I am executing chunks using procedure p_process_chunk below. Can you help: Is there any way i can get chunk Id(56, 57, etc) task name & Job Name (ex. TASK$_371_1, TASK$_371_2, etc) in p_process_chunk (i need this for logger pe...
Categories: DBA Blogs

Identifying implicit conversions

Fri, 2017-10-27 11:26
Tom, I have recently taken over support of a poorly written application that is suffering performance problems (bet you've heard that before!) - One of the conditions that I have identified is excessive full table scans and or index range scans ...
Categories: DBA Blogs

High Watermark on a table

Fri, 2017-10-27 11:26
Hi Tom, If my delete query doesn't delete any record, will it have any effect on HWM? I mean, everything will remain same as before my delete command, please confirm.
Categories: DBA Blogs

ORA-27103: internal error

Wed, 2017-10-25 22:46
Hello; could you help me with problem of memory, I try describe it. I have application under tomcat on application server, application generates select to database server, after few secconds application logs error ORA-27103: internal error A...
Categories: DBA Blogs

trigger, to get an audit

Wed, 2017-10-25 22:46
I have a table, where there is a column which is updated from multiple sources. I would like to find out the source which is updating this column. for example, it can be a SP or direct application. So is there a possibility of having a trigger, to...
Categories: DBA Blogs

Regarding the Complex Hierarchical Query

Wed, 2017-10-25 22:46
Hi Tom, Could you please help me to develop a Query. My requirement is given below. I have a following table called PRODUCT and the structure is given below. CREATE TABLE PRODUCT(ID NUMBER, NAME VARCHAR2(50), PARENT_ID NUMBER, PRODUCT_TYPE V...
Categories: DBA Blogs

apply archive logs through OPEN RESETLOGS

Wed, 2017-10-25 22:46
Hello Tom I am a little confused about following excerpt from "Backup and Recovery User's Guide": Because the database does not apply an archived redo log to a data file unless the RESETLOGS SCN and time stamps match, the RESETLOGS requirement ...
Categories: DBA Blogs

Downloading the Database for the Oracle SQL developer / VMware fusion

Wed, 2017-10-25 22:46
Hi Tom, I have followed the instruction provided on the how to download data base for OracleSql developer. I am using Mac, so i was not a able to use XE without linux/Microsoft etc..Therefore I have chosen the 3rd step to download the DeveloperDay...
Categories: DBA Blogs

Can Oracle allocate more PGA than PGA_AGGREGATE_TARGET parameter ?

Wed, 2017-10-25 22:46
Hi Tom, Can Oracle allocate more PGA memory than PGA_AGGREGATE_TARGET parameter ? If yes, is there a rule for that ? Thanks, Pierre
Categories: DBA Blogs

Using dbms_metadata.get_ddl - getting rid of carriage returns

Wed, 2017-10-25 22:46
I am currently using dbms_metadata to get VIEWS DDLs (in order to re-create them following a FROMUSER-TOUSER import; compilation errors due to in-text schema names). However, the generated file (spool) is full of carriage-returns which makes mandator...
Categories: DBA Blogs

SORT AGGREGATE resets the cost

Wed, 2017-10-25 04:26
<code> SET lines 777 SET pages 10000 SET trimspool ON --Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production CREATE TABLE t(ID INTEGER PRIMARY KEY,flag VARCHAR2(4000)); INSERT /*+append*/ INTO t SELECT LEVEL,LPAD(...
Categories: DBA Blogs

Materialized View Fast Refresh from heterogeneous database via oracle gateway

Tue, 2017-10-24 10:06
Ora Masters, We pull a huge volume of data from source database (MySQL) to Oracle database (11.2.0.3) using Oracle gateway (12.2.0) through a remote database link. But the issue is that we receive following error when the MV is tried to refresh ...
Categories: DBA Blogs

How to configure Apex

Tue, 2017-10-24 10:06
Hi. We have Oracle database 12c installed and I can see below Apex schemas as well. APEX_030200 APEX_040200 APEX_PUBLIC_USER FLOWS_FILES Below query also returns 'VALID' value. SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX'; Can ...
Categories: DBA Blogs

Export time doubled after adding encryption

Tue, 2017-10-24 10:06
Recently, we added encryption directives to database exports. We noticed that the export time doubled. In your experience, is this expected behavior? Database1: Before 54 mins After 1 hr 40 mins Database2: Before 30 mins After 58 mins ...
Categories: DBA Blogs

Disk I/O monitoring sql query

Tue, 2017-10-24 10:06
Dear Tom, My team is trying to write a query for measuring Disk I/O latency in milli seconds. my team is looking to query v$system_event, but this view has accumulated values of metrics from the startup time of the instance. Please advise which ...
Categories: DBA Blogs

Pages