Tom Kyte

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

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

About BLOB and BFILE

Sat, 2017-10-21 08:46
Hi Tom. We are designing an office automation application and we want to store image of letters and documents.Our organization is very large (it has 100 departments) and We have to use replications. Now we want to know is BLOB a proper type for sto...
Categories: DBA Blogs

Getting RMAN-05502 when trying to duplicate from backup

Sat, 2017-10-21 08:46
I have very little experience with Oracle. We are a SQL Server shop with one legacy Oracle 11.2 database that serves an obscure but critical function. As part of our testing routine, this database needs to be copied from our production instance into ...
Categories: DBA Blogs

ORA-04091: table is mutating, trigger/function may not see it

Fri, 2017-10-20 14:26
Hi, I am getting "ORA-04091: table is mutating, trigger/function may not see it" in my trigger. Please find the test case details as follows: <code>create table test(bug_number number, subject varchar2(50),ANALYZED_BY varchar2(50)); insert...
Categories: DBA Blogs

Neat and optimized code

Fri, 2017-10-20 14:26
I need the logic to derive the direction and line hour direction is Inbound if either one of org and dest or both falls under the jrny_in list and the rt list direction is outbound if either one of org and dest or both falls under the jrny_out ...
Categories: DBA Blogs

Performance - Index tablespaces with block size greater than data tablespace ?

Fri, 2017-10-20 14:26
Hi, I would like to know if it would it be good practice to create index tablespaces with block size larger than the data tablespace? Recently, a performance consultant company guided us to maintain indexes on tablespace with block size of 16K,...
Categories: DBA Blogs

Change a DBMS_SCHEDULER job's next run date

Fri, 2017-10-20 14:26
I have a job scheduled via DBMS_SCHEDULER. It is scheduled to run weekly. It has been running fine for some time now. All I want to do is alter the next_run_date. I cannot find any way to do this in the documentation. I can alter the original start d...
Categories: DBA Blogs

Dynamically Create Database Link

Fri, 2017-10-20 14:26
I have several scripts that are hard wired when creating a database link and all works well. The time has come though to take them to the next level and expand the user community. To do that the CREATE DATABASE LINK statement needs to be dynamicall...
Categories: DBA Blogs

Receive http requests with more than 32k

Fri, 2017-10-20 14:26
Hi Tom, on more question :) I'm using the utl_http package to exchange xml-data between different oracle databases. Until now i've requested the mod_plsql (until 11g) or the apache (from 12c) on the other database, posted the data with utl_http....
Categories: DBA Blogs

Pages