DBA Blogs

Using with clause and double function in where clause causes non joined rows to be processed

Tom Kyte - Thu, 2016-11-10 06:06
Hi, I seem to have stumbled upon Oracle behaviour I can not explain, so I've come to the magical place called Ask Tom for guidance. I have created the following example to make it as short as possible while still reproducing my problem: 2 tabl...
Categories: DBA Blogs

Partition an Exisiting Table

Tom Kyte - Thu, 2016-11-10 06:06
I am trying to partition an existing table (Range partition) by a date column (BEGTIME). Currently the table is 7.5 GB in size. Our business moves monthly and wanted to partition into months. Also table holds data pertaining to forecasts up to Dec 21...
Categories: DBA Blogs

Query regarding primary key , foreign key relations and inserts

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, I have a two tables Table1 (id_pk number, name_fk varchar(10)) Table2 (name_pk varchar(10), id_fk number) id_pk : Primary key of table1 name_fk : foreign key referencing name_pk of Table2 name_pk : Primary key of table2 id_fk : Fo...
Categories: DBA Blogs

Complicated query to analyse utilization period

Tom Kyte - Wed, 2016-11-09 11:46
Dear Gents I have a query which is giving the available quantity in each location on specific dates: Location Tdate RunningQty CA02D003A 31-Jul-16 1152 CA02D003A 30-Sep-16 1092 CA02D003A 11-Oct-16 500 CA02D003A 13-Oct-16 0 CA02D003A 20-Oct...
Categories: DBA Blogs

Update one column in 58 millions records table

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, Need suggestions to improve performance. Following are the methods we tried 1. Using <b>merge</b> <code>MERGE INTO /*+ PARALLEL(tbl_temp,8) */ tbl_temp tcm USING (SELECT frn.customer_id, frn.risk FROM temp_new frn ) a ON (a.id = tc...
Categories: DBA Blogs

Unable to use the out type parameter in procedure through DB Link

Tom Kyte - Wed, 2016-11-09 11:46
I have created procedure with the our parameter as type CREATE OR REPLACE TYPE char_array AS TABLE OF varchar2(32000); create or replace procedure GET_INSERTS_PRC(V_TABLE_NAME IN VARCHAR2,OUT_inserts_TYP out char_array ); I can able to run...
Categories: DBA Blogs

Stale statistics

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, I've been asked to review performance on one of our databases, and one of the issues I found was the statistics have been locked since 2011 and are considered stale. My recommendation was to back them up (as a rollback point) and upda...
Categories: DBA Blogs

Sql Loader (NULLIF and NVL(TO_NUMBER) - NULLIF not yielding the correct result. The columns is not being set as NULL but loaded with 0.0.

Tom Kyte - Wed, 2016-11-09 11:46
I have the following Sql Loader Control file : POS_COST_PRC_FUND POSITION(173:202) DECIMAL EXTERNAL NULLIF V4_NULL='?' "NVL(TO_NUMBER(LTRIM(:POS_COST_PRC_FUND,'0')),0)", V4_NULL FILLER POSITION(203:203) CHAR, The column in Oracle is defined a...
Categories: DBA Blogs

APEX 5.0 ORDS 2- Tomcat 7 - Errors on tomcat log and performance issues

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, We have implemented Apex 5.0 as our new Developing tool which is a real great tool. The configuration is on HP/UX, tomcat, ORDS and APEX on Oracle 11g r2. We have 4 environnements (dev, qualification, preprod, prod). On the 3 first ...
Categories: DBA Blogs

Table dependencies

Tom Kyte - Tue, 2016-11-08 17:26
To find databse table dependencies I ran below sql, select rpad( '*', (level-1)*2, '*' ) || table_name table_name from ( select p.table_name , p.constraint_name primary_cons , f.constraint_name foreign_cons , f.r_constr...
Categories: DBA Blogs

finding regular expression

Tom Kyte - Tue, 2016-11-08 17:26
Hi i am trying to match a pattern in a string. It is web log data so basically i need to look as follows find a comma followed by two spaces followed by anynumber of characters other than a quote follwed by = Eg: is string is abc=1, name='1...
Categories: DBA Blogs

Casting complex object IN parameter of a procedure multiple time for inserting into multiple tables VS Looping through the object and inserting into all the tables

Tom Kyte - Tue, 2016-11-08 17:26
Hello Tom First, thanks for all the help and advice you provide to the Oracle world. I have a procedure which takes IN parameter as a complex object (Ex. Array Starts with Product Events which contains Engines(object) which in turn contains par...
Categories: DBA Blogs

Receiving the error: "Couldn't switch user" while executing a job

Tom Kyte - Tue, 2016-11-08 17:26
Hi Experts, I have a job under oracle scheduler that executes a chain. This chain has 3 steps: Step 1: executes a stored procedure Step 2: executes a unix shell and Step 3 executes another stored procedure. The JOB used to work fine but suddenl...
Categories: DBA Blogs

Data Archival

Tom Kyte - Tue, 2016-11-08 17:26
Hi Tom, Couple of questions here There is requirement of data archival, one option i can find is create separate database and load data using dblink. Is there any efficient way to do archival? Post archival there is a requirement where c...
Categories: DBA Blogs

Regarding high CPU usage for index query

Tom Kyte - Tue, 2016-11-08 17:26
Hi As part of a query execution we observed high CPU usage. The query is using the indexed column in where condition. Table : Select * from mytable where mycol1 <= 200000 and xxxx This table is having index on column mycol1. This tab...
Categories: DBA Blogs

Loading The XML like data into columns

Tom Kyte - Tue, 2016-11-08 17:26
Hi Tom, I have a data in a csv file like this: something_here, "{ "entityValue" : { "vlanId" : { "type" : "string" , "value" : "121a" }, "vlanDescription" : { "type" : "string" , "value" : "asdf" ...
Categories: DBA Blogs

I am trying to access a function stored in a package and it gives me invalid sql statement error

Tom Kyte - Tue, 2016-11-08 17:26
HERE IS MY <b>PACKAGE</b> AND ITS <b>BODY</b>: CREATE OR REPLACE PACKAGE TBOOK AS FUNCTION TBOOK1(JID VARCHAR2) RETURN VARCHAR2; END; CREATE OR REPLACE PACKAGE BODY TBOOK AS JID2 VARCHAR2(6); JID1 VARCHAR2(6); FUNCTION TBOOK1(JID VARCHAR2)...
Categories: DBA Blogs

Data retrieval from two databases

Tom Kyte - Tue, 2016-11-08 17:26
Hi Team, I have requirement - Consider : 1. Table1 is holding data current 5 years of data in primary database 2. Table1 is holding data older 5 years of data in other database(archived) is there any way we can manage records retr...
Categories: DBA Blogs

Copy table data From One DB to Another DB

Tom Kyte - Mon, 2016-11-07 23:06
Hi Team, I need to Copy table data From One DB to Another DB. One approach I can recollect from one of the conversation asked in asktom.oracle.com is that create database link and simply execute - insert into local_table select * from table@...
Categories: DBA Blogs

export import

Tom Kyte - Mon, 2016-11-07 23:06
Hi team, I wanted to know that when i export entire schema from one database and import into anothere database then- Objects like - tables,functions,triggers,procedures,dblinks,synonyms,public synonyms and many more. Which objects export dur...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs