Tom Kyte

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

Index Clustering Factor and Insert

Tue, 2016-12-13 02:06
HI Connor, I followed the below article by Tom, http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52asktom-1735913.html Lets ignore the CHAR, VARCHAR, and NUMBER context, and focus only on the clustering factor. Then, what woul...
Categories: DBA Blogs

APPEND hint in MERGE without INSERT clause

Tue, 2016-12-13 02:06
Hello, from the performance point of view: does it make sense to add the APPEND hint into the MERGE statement in that case there is only an UPDATE clause? If yes, what is the benefit? Thank you, Zdenek
Categories: DBA Blogs

Is there any way to convert an complex object type to JSON in 12c

Tue, 2016-12-13 02:06
Hello Tom We have a procedure which has an complex object type as output (we didn use Refcursor as we had lot of reusable codes in case of using object). This data is populated in Application layer for display and the same is moved back to a JSON...
Categories: DBA Blogs

ORA-02070: database does not support in this context

Mon, 2016-12-12 07:46
Hi, I am facing the below exception - ORA-02070: database does not support in this context Here is the query I am using - INSERT INTO table_1 (a,b,c) SELECT table_name , partition_name , subpartition_name FROM user_subpartitions; ...
Categories: DBA Blogs

Problem with PL/SQL, Cursor and trigger

Mon, 2016-12-12 07:46
Hi, I have some little problem with my exercises: i have these two tables CREATE TABLE PRENOTAZIONI ( CodPrenotazioni NUMBER(5) PRIMARY KEY, DataPrenotazioni DATE DEFAULT SYSDATE NOT NULL, CodProg NUMBER (3) NOT NULL, CodUtente VARCHAR2 (20) N...
Categories: DBA Blogs

Date Validation without function

Mon, 2016-12-12 07:46
Hi There, I've a string column and i would like to validate it to date between 01 Jan 1930 to 01 Jan 2030, Please help me. Date might be anything like below 1226547879 basdhbcus489 34^&dsulch56
Categories: DBA Blogs

Globally unique integers

Mon, 2016-12-12 07:46
My application uses NUMBER columns as primary keys, and uses sequences to populate the column (via a trigger with the typical "if :new.customer_id is null then select seq_customer_id.nextval into :new.customer_id from dual" construct). Now, I woul...
Categories: DBA Blogs

Most efficient way to UNPIVOT a table with large # of columns

Mon, 2016-12-12 07:46
Database is 11.2.0.4. At this point, cannot use full featured FLASHBACK ARCHIVE with CONTEXT... but it would have the same issues I think <b>ENVIRONMENT</b> I have a change logging table (e.g. CHANGESTABLE) that is basically the mirror image col...
Categories: DBA Blogs

ORA-01460: unimplemented or unreasonable conversion requested in /var/www/html/rest/price/resolve/ResolvePrice.php

Mon, 2016-12-12 07:46
when i pass the hardcoded values $PRODUCT_NUM_ARR and $MEMBER_NAME through an oracle bind variable to execute a stored function,it works fine and i get the result. But when i pass the same values from an array i get the ORA error. I have found diffic...
Categories: DBA Blogs

Limit DOP of automatic statistics gathering

Mon, 2016-12-12 07:46
Tom - we run a 11.2.0.4.0 RAC database with Automatic DOP enabled. The 2 rack nodes have 16 CPU cores each (32 with Hyperthreading) and 128GB of RAM. The database did run smoothly for over a year. The OS is Redhat Enterprise Linux 6.5 on both nod...
Categories: DBA Blogs

RMAN BACKUP PERFORMANCE

Sun, 2016-12-11 13:26
Hi,guy! How can I improve my rman backup speed? Please more details! Thanks a lot!
Categories: DBA Blogs

Package Specification varaible

Sun, 2016-12-11 13:26
Hi I had defined the few global variables in the Package Specification.One of the main procedure is initializing some values into those variables. In the main procedure we are calling different sub procedures. But unfortunately some times global...
Categories: DBA Blogs

Partitioning Strategy

Sun, 2016-12-11 13:26
Hi Tom, This is my first time that I'm posting a question to you. I have been huge fan of your answer and humor sometimes :) Keep it up !! Q) I have a schema that contains around 50 tables, 5 tables contain around 1.5 billion rows and rest 45 ...
Categories: DBA Blogs

how to fetch sqlid from old transaction_id

Sun, 2016-12-11 13:26
Hi, We have Goldengate 11gR2 for Unidirectional replication from Oracle(11.2.0.4) to Oracle(11.2.0.4) database. Both source and target are RAC environment. We received below warning message on Goldengate, letting us know why one of the extract ...
Categories: DBA Blogs

Operating System Block Size

Sun, 2016-12-11 13:26
Good Evening, I would like to bring up a topic that comes up every time I work on a brand new Unix database server, but I never do anything about it. I mention it to the system administrators, but basically get ignored. The reason I am not very ...
Categories: DBA Blogs

Group by function

Sun, 2016-12-11 13:26
Hi Tom , Please see the below query : select financial_transaction_nk , max(financial_transaction_dim_key) from FTD where FTD.financial_transaction_nk in (select financial_transaction_nk from FTD where financial_transaction_dim_key in (s...
Categories: DBA Blogs

Mirroring Oracle Database

Sun, 2016-12-11 13:26
My company is in Indonesia. Having branches separates in all province. We use Oracle Database. All the branches must having internet connection to connect the application. But there is one branch, the province is so deep inside. Sometimes the interne...
Categories: DBA Blogs

Multiple DB instances

Sun, 2016-12-11 13:26
Dear Sir How to login / access a particular DB instances through connect /as sysdba when the server has multiple database instances (SIDs) like APP_QA, APP_DEV, APP_UAT. Thanks & Regards
Categories: DBA Blogs

Encode function

Sun, 2016-12-11 13:26
Hi , i'm trying to send sms using database , and my problem is in double encoding . i want to send Russian letters , English working for me fine . only the way of that is to translate the text using http://www.freeformatter.com/url-encoder.html#ad...
Categories: DBA Blogs

TEMP space usage for DBMS_STATS.GATHER_TABLE_STATS

Sun, 2016-12-11 13:26
Hello AskTom Team, We have a following code snippet after a batch process which times out with ORA-01013: user requested cancel of current operation and consumes lot of TEMP space. Our customer complained to us they are running out of 1TB or tem...
Categories: DBA Blogs

Pages