Tom Kyte

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

In-Database Archiving

Wed, 2018-09-19 15:46
Hi, Currently i am using list partitioning based on a status column to classify the data as ACTIVE and EXPIRED. And then the corresponding partitions are exported and then dropped from Prod. The problem with this approach is the internal data m...
Categories: DBA Blogs

TDE Column vs TDE tablespace when to use

Wed, 2018-09-19 15:46
Hi, I have gone through the TDE column and TDE tablespace encryption. Most cases TDE tablespace option is found to be better compared to TDE column option. Wanted to know what advantage TDE column encryption gives or rather the use cases for TD...
Categories: DBA Blogs

In explain plan one of the tables in the query is not even scanned for joining , Do you know why ?

Wed, 2018-09-19 15:46
I am running a query like below explain plan for select count(1) from A , B where A.column1=1 and A.column2=3 and A.column3=b.column3(+) When I check the explain plan I can see the sort aggregate and other things ,However I do not see...
Categories: DBA Blogs

ERROR WITH IMPORT OPERATION IN DATABASE 11G RELEASE 1 AND DEVELOPER 10G

Tue, 2018-09-18 21:26
Hi Tom, I installed Oracle Database 11G in the folder (E:\DB11)and successfully imported a user to it. But when you install the developer 10G IN FOLDER(E:\DEV10). and TRY TO DO a user import into the database ORACLE 11G, i am receiving the follo...
Categories: DBA Blogs

Collection to retrieve data as pipelined taking more time even the query taking lesser time

Tue, 2018-09-18 21:26
Hi sir, Have facing issue with the time. The query which is used in dynamic cursor type like 'open for select" taking 550 ms but the output we used as collection passing as pipelined value. At that time it's taking more time like 50 s. Please give...
Categories: DBA Blogs

Need script to compare table data of same table in different oracld databases

Tue, 2018-09-18 21:26
Hi, I have a requirement where table1 exists in 2 diiferent oracle dbs . I need to write a shell script or pl sql block which would compare every column of the table in db 1 and db2 and give following output from unix. It should be able to take any ...
Categories: DBA Blogs

Selection from union view by table identifier

Tue, 2018-09-18 21:26
Hi, I have a view like <code>create view V_TAB as (select 1 as id, value from TAB1 union all select 2 as id, value from TAB2 ); </code> I would expect Oracle be able to optimize the following query and execute a selection only on one tabl...
Categories: DBA Blogs

Oracle Forms V Oracle APEX Check List

Tue, 2018-09-18 03:06
Oracle Forms has some strength and is still the best BackOffice tool from ORACLE from my Point of view. Here are some issues that I miss with APEX. Maybe you already have these Options in 18.x.? Can you check this list: 1. 100% accessiblity for...
Categories: DBA Blogs

Display blob pdf

Tue, 2018-09-18 03:06
Dear, I have table lob_table( id number ,doc blob ,namefile varchar (200)) I would like to display blob doc who is pdf file and print it in sqldeveloper. i have create this procedure is it corrects ? CREATE OR REPLACE PROCEDURE PROC2 AS...
Categories: DBA Blogs

What privilege to view package body

Mon, 2018-09-17 08:46
Hi Tom: I have a problem when i grant the package privilege to the other user. A is a normal user which used in factory environment. user B is for app team which can not create anything. First I grant create any procedure ,execute any procedure...
Categories: DBA Blogs

Hardware resource planning

Mon, 2018-09-17 08:46
Hello, Thanks for taking up this question. I am interested in understanding how to optimize the hardware resources (cores, memory, disk space) required for Oracle without impacting performance. There are multiple virtual machines in a VMwa...
Categories: DBA Blogs

system user could login without password or incorrect password

Mon, 2018-09-17 08:46
hi all, recently i had an incident.. i just logged into the database as system using sqlplus when sqlplus prompted for username i put 'SYS AS SYSDBA' and when prompted for password,instead of entering my password i just hit the ENTER key and s...
Categories: DBA Blogs

Returning count of rows deleted using execute immediate

Sun, 2018-09-16 14:46
How to I get the number of rows deleted within PL/SQL using the EXECUTE IMMEDIATE command?
Categories: DBA Blogs

Parse string then flatten into columns

Fri, 2018-09-14 07:46
Hi, LiveSQL link not accepted by the form: https://livesql.oracle.com/apex/livesql/s/g88hb5van1r4ctc65yp4lq9gb I have this situation (see link): <b>ID String</b> Id1 Thing1: Sub1, <br>Thing2: Sub7 ,Sub8 , Sub9 <br>Thing3: Sub12 Id1 Thing...
Categories: DBA Blogs

Oracle View object - performance Issue with Outer Join including a WITH clause

Fri, 2018-09-14 07:46
Hi Tom ; Thank you , I've been using your site for 2 years now, resolved many issues based on your answers. Case Scenario : Customer having multiple addresses , only one is active ; some cases ALL the addresses of a customer could be inactive....
Categories: DBA Blogs

MATERIALISED VIEW ISSUE

Fri, 2018-09-14 07:46
Error starting at line : 12 in command - CREATE MATERIALIZED VIEW EMP_MV BUILD IMMEDIATE REFRESH FORCE ON COMMIT AS SELECT EMPID,EMPNAME FROM EMP Error report - ORA-12054: cannot set the ON COMMIT refresh attribute for the materializ...
Categories: DBA Blogs

Recommended partition size

Fri, 2018-09-14 07:46
We want to partition some tables using interval partitioning on the creation date. Partitioning is for manageability - we want to drop older partitions eventually - and partition pruning for improving performance. What is the Recommended partition si...
Categories: DBA Blogs

Identify the missing object in an ora-08103 error

Thu, 2018-09-13 13:26
We have a large cursor that runs nightly and on a random night we get the following error from the job that calls the cursor: 180911 180019 (APPS.GAINS_COMMON,1194) Begin export_data 180911 194348 (APPS.GAINS_COMMON,4735) BEGIN export_sla 1809...
Categories: DBA Blogs

Oracle locking a table while deleting set of rows

Thu, 2018-09-13 13:26
I have 2 scripts running at the same time deleting different set of rows from same table. Would this cause any locking issues or contention? Please advise. Thank you very much.
Categories: DBA Blogs

What SQL is currently running in the database

Thu, 2018-09-13 13:26
I am using an application that submits SQL queries to the Oracle database. These queries can have hundreds of bind variables in them. I want to be able to see the SQL that is running with the bind variables substituted. I have used this query to g...
Categories: DBA Blogs

Pages