Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 hour 33 min ago

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

No Exection Generated in 10046 Event Trace

Tue, 2017-10-24 10:06
Hi Tom, I'm using the event 10046 to trace other sessions. But sometimes I find that there is not execution plan information included in the result trace file. Here is an example: 1. From my session, I executed dchen@SAPU> exec sys.dbms_syst...
Categories: DBA Blogs

Re-ordering selective rows in a alpha column

Mon, 2017-10-23 15:46
Hi Masters, The example below makes an attempt to swap the A before B when they appear together whilst ignoring the nulls in colC as well as still following the original order from colA but comes unstuck with id = 3333 and 4444 where for a few exa...
Categories: DBA Blogs

Materialized View or dblink - which one is better and why

Mon, 2017-10-23 15:46
We have an ETL process that has to get data from remote db (Oracle 12c). For some reasons, we cannot give direct access on the remote tables to the ETL process. We are considering two options: Option 1. On local db, create dblink to remote db, cre...
Categories: DBA Blogs

UNUSABLE index after Partition maintenance operations

Mon, 2017-10-23 15:46
Team, Was reading through this documentation. <u>http://docs.oracle.com/database/122/SQLRF/CREATE-INDEX.htm#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE__BABCHJDH</u> <quote> When an index, or some partitions or subpartitions of an index, are cr...
Categories: DBA Blogs

Insert a text file into oracle table , the file is in oracle directory

Mon, 2017-10-23 15:46
I want to insert a text file into a table . But the file is in oracle directory something like /tmp/test.txt , Is it possible , a procedure with a parameter which will take the file path as parameter and fetch the file from server and copy it to th...
Categories: DBA Blogs

Update same table in parallel using batch number

Mon, 2017-10-23 15:46
Hi, I have this statement, create table TWO nologging parallel 15 as select a, b, c, pkg_xyz(l,m,n) d from ONE; As you can see, there is a package call in the select query. There are 7 million rows in table ONE. It is taking approx 3....
Categories: DBA Blogs

Tablespace in EM Express

Mon, 2017-10-23 15:46
Hi Tom, I need to ask you for help, I am new to Oracle and trying to understand some aspects of db administration. I have one of the exercises to create tablespace using EM Express, however I should navigate to storage-> tablespace but under the s...
Categories: DBA Blogs

Pages