Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 week 5 days ago

Select from view with where clause

Fri, 2019-02-08 13:26
Hi, I cant find so I'd like to ask: I have view myView: <code>create view myView as select * from myTable;</code> and what is the difference between <code>select * from myTable where myTable.id = 12345;</code> and <code>select * from ...
Categories: DBA Blogs

Guidelines for chossing Database Vs Schema. Also One Schema on multiple schema on Oracle Exadata

Fri, 2019-02-08 13:26
Hi, This is my first experience with Oracle Exadata. Trying to provide solution for client. 1) Do you have any guidelines for when to go for database against schema. Pros and Cons 3) We have to load data from 100+ sources so any guidelines f...
Categories: DBA Blogs

Log Recovery in Dataguard alert log

Fri, 2019-02-08 13:26
Hi all, I am using Oracle 11g Active Dataguard in maximum performance mode. I notice 2 general pattern of log recovery in the database alert log. ===================== Pattern1 <code>RFS[7]: Selected log 11 for thread 2 sequence xxxxx Arc...
Categories: DBA Blogs

Database migration

Fri, 2019-02-08 13:26
Hi All, We have 70 TB Database sitting in Solaris Sparc . We are migrating oracle from Solaris to RHEL 7. i am looking for best approach for moving data from Solaris to rhel with minimal downtime. Could you please let me know what would be ...
Categories: DBA Blogs

Pivot on a query with multiple data columns

Thu, 2019-02-07 19:06
I have an SQL query written against view sys.dba_audit_session to retrieve initial logon and final logoff times for users across a range of days. So the basic query is <code> SELECT DISTINCT username, action_name, ...
Categories: DBA Blogs

Parsing through a Long Character with 255 characters and Stripping out Words

Thu, 2019-02-07 19:06
Hi, I have a long character field for every one of my 10,000 rows and I need to write a PL/SQL that goes through every row a parses this long field for any WORD (Mix of Alpha and Numeric) up to 6 characters and spits it out and Save it to a differ...
Categories: DBA Blogs

Generating output file using spool is very slow when compared to UTL_FILE

Thu, 2019-02-07 19:06
Hello Team, We have to extract huge data from EBS tables. We have tried two approaches Approach1 (Using SQL and Spool command) --------- We have created a script to generate the output using SPOOL command but this is taking 12 hours of time ...
Categories: DBA Blogs

Identify when a function is executed in a SQL Query or in a PL/SQL procedure

Thu, 2019-02-07 19:06
Is there any way to identify when a pl/sql function is executed in SQL Query and when is executed in a procedure or PL/SQL anonymous block? (I don't want to pass any parameter for manual identification) The main reason I need that is when a functi...
Categories: DBA Blogs

How to convert ROWS to COLUMNS

Thu, 2019-02-07 19:06
Hi, I have data as given below in table <code>create table chart( SERIES varchar2(10), LABEL date, VALUE number(5,2) ); insert into chart values('A',to_date('13-DEC-18','DD-MON-YY'),83.55); insert into chart values('B',to_date('13...
Categories: DBA Blogs

Index creation slow - lots of direct path write/temp waits

Thu, 2019-02-07 00:46
I have a table on which I'm trying to index a column. This happens as part of an ETL process, however I have run this process directly to reproduce. The table in question has about 15M rows and I am trying to index a single column. create index x...
Categories: DBA Blogs

Created file using UTL_FILE. How to prevent file from being picked up before it has been fully created

Thu, 2019-02-07 00:46
We have an integration where the file is being created in oracle ebs using utl_file utility. The file is placed on the server where biztalk pings every 60 sec and picks it up. In one scenario the file was picked up before it was written out resulting...
Categories: DBA Blogs

When are partition statistics beneficial?

Thu, 2019-02-07 00:46
Tom, When are partition-level statistics used by the optimizer in 9iR2 and 10g? The only thing I've found in the docs so far (trying to avoid RTFM) is that they're used when a query fit the form SELECT ... FROM MYTABLE PARTITION (PTN_NAME). Specif...
Categories: DBA Blogs

oracle database parameters

Thu, 2019-02-07 00:46
Dear Tom, I'm trying to test the oracle 12C reporting system, in which i will dump the oracle database transaction database backup to report system. For better performance of completed reporting system, what are the parameters i need to set perfe...
Categories: DBA Blogs

GROUP BY returns incorrect number of rows

Thu, 2019-02-07 00:46
Hi A bit of an odd one - possibly a bug. <code>DROP TABLE xxibpc_test; CREATE TABLE xxibpc_test AS WITH data AS (SELECT LEVEL l_no FROM dual CONNECT BY LEVEL <= 10000) SELECT 1 instance_number ,1 + l_no session_id ,10...
Categories: DBA Blogs

Replacements for OrdDoc

Wed, 2019-02-06 06:26
I have been using OrdDoc to store files and attachments for my applications. It looks like Oracle has deprecated this and will no longer support it in its next release (https://mikedietrichde.com/2018/08/10/oracle-multimedia-is-deprecated-in-oracle-1...
Categories: DBA Blogs

CDC on Views

Wed, 2019-02-06 06:26
Hi Tom, I have a view created daily on a detailed table set ( n number of tables based on the load) as below CDR_Detail_20190204_view CDR_Detail_20190205_view CDR_Detail_20190206_view To generate a report , i need to pick all the daily views(...
Categories: DBA Blogs

Can I force query to use the varchar index if the condition uses a number value

Wed, 2019-02-06 06:26
Hi, I've got the following sql statement: <code>SELECT C.APPLICATION_NUMBER, D.AGREEMENT_TYPE, C.SUPPLIER_REFERENCE FROM APPLICATION_DATA D, APPLICATION C WHERE C.APPLICATION_NUMBER =:b1 AND D.APPLICATION_ID = C.APPLICATION_ID;</code> ...
Categories: DBA Blogs

Windows Oracle11g to Linux Oracle 12c

Wed, 2019-02-06 06:26
Hi, hope you're doing great? I wonder if you can help me by giving me some advices or pointing me to some useful docs. In fact, we are planning to migrate a financial application with most of its business logic is written in Pl/SQL, from Windows Orac...
Categories: DBA Blogs

Performance degradation after database restoration

Wed, 2019-02-06 06:26
HI ALL, I HAVE A PROBLEM HERE WE are running an application(FOR REPORTING ONLY) with Oracle database 11.2, Data for this oracle database is getting from SQL server ( users are inserting data into SQL server first and finally with the help o...
Categories: DBA Blogs

Max number of processes

Wed, 2019-02-06 06:26
hi - we recently had a code change in our production database. Since then we see that every 2 days we have hundereds of inactive sessions in the database and maximum number of processes is reached. We increased this number a couple of times and every...
Categories: DBA Blogs

Pages