Tom Kyte

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

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

how to query and monitor the memory and CPU usage of the database

Mon, 2017-10-23 15:46
I'm maintaining a oracle db which version is 10.2.0.1, a lot of time, i just know how to query and monitor the memory and CPU usage on OS level, now I want to query and monitor the memory and CPU usage of the database on db level , could you pleas...
Categories: DBA Blogs

performance tunning

Mon, 2017-10-23 15:46
Hi Team, Currently we have seen huge spike for our database (non-RAC ) . This spike was due to log file sync that was around 900 session , blocker of this sessions was log file parallel write . whereas their was no blocker for log file paralle...
Categories: DBA Blogs

Deinstall Enterprise Manager System (12c) for a fresh install.

Mon, 2017-10-23 15:46
Hi, I have installed the simple configuration (oem 12c) and want to deinstall it so I can freshly install for the "small" configuration (under Advanced configuration in the GUI). Note: somebody made a mistake to install the "simple configuration...
Categories: DBA Blogs

Procedure that deletes an input from table with foreign key constraints

Sun, 2017-10-22 21:26
I have written a procedure that has as input the CUSTOMER_ID. Then the procedure deletes the corresponding customer from the table CUSTOMERS. <code>CREATE OR REPlACE PROCEDURE DELETE_CUSTOMER (CUSTOMER_ID NUMBER) AS TOT_CUSTOMERS ...
Categories: DBA Blogs

Parameterized dynamic query

Sun, 2017-10-22 21:26
Hi, I'm trying to improve the performances of some queries in my production environment. The current scenario is: 1) retrieve some data from "<b>ParentTable</b>". The query, in software code, is composed dynamically, based on the specified s...
Categories: DBA Blogs

SQLcl works worse with pipelined function than SQL*Plus

Sun, 2017-10-22 21:26
Let's imagine, we have a pipelined function like shown in this example https://oracle-base.com/articles/misc/pipelined-table-functions#pipelined_table_functions My aim is to show, wether that function really return data row by row, rather than at o...
Categories: DBA Blogs

Fragmentation

Sat, 2017-10-21 08:46
Can tables which only experience inserts and updates and not delete be fragmented.
Categories: DBA Blogs

Join with where clause

Sat, 2017-10-21 08:46
Hi there, I have an example below which I'm not sure how oracle execute the where clause. Select a.id, b.column_A, b.column_B, b.column_C from A left join B on a.id = b.id where b.column_C = 'Yes' My question is that do oracle execute the...
Categories: DBA Blogs

Are results from SELECT with no order by and with ROWNUM predicate used in WHERE condition stable?

Sat, 2017-10-21 08:46
Consider the following simple scenario: PREPARATIONS: 1) Let's say we have a table USERS with one COLUMN NAME: create table USERS(NAME varchar(100)); 2) Let's put some values there: insert into USERS(name) values('User1'); insert into USERS...
Categories: DBA Blogs

difference of explain plan, autotrace and tkprof

Sat, 2017-10-21 08:46
I have confusion about explain plan, autotrace and tkprof. When explain plain showing to us the prediction about how optimizer will work to produce result and tkprof show the real thing that happen, so how about autotrace. In autotrace there are two ...
Categories: DBA Blogs

Pages