Tom Kyte

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

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

Index creation taking more time

Sat, 2016-12-10 00:46
Hello Tom, The index creation is taking more time. Previously the index creation used to take around 1 min and now it has increased to 10 - 20 min. The indexes are dropped and recreated on a daily basis during our night load. Due to this, our ni...
Categories: DBA Blogs

Deleting records using bulk collect throws ORA-01555 snapshot too old error

Sat, 2016-12-10 00:46
I am using the below code to delete data using bulk collect create or replace procedure DEL_BULK_ABC AS declare CURSOR C_GetDelRec IS SELECT rowid row_id FROM ABC.XYZ a WHERE EXISTS (SELECT 'X' FROM ABC.CH_TEMP b ...
Categories: DBA Blogs

cdb/pdb for upgrades

Sat, 2016-12-10 00:46
hi - we are preparing to upgrade our databases from 11g rel 2 to 12c. we have around 50 databases between non prod and prod and 100s of schemas. Trying to determine the best candidates for cdb/pdb. are there any criteria to make that determination ? ...
Categories: DBA Blogs

WHEN clause in SQL Loader control file

Sat, 2016-12-10 00:46
Hi Team, We have a bank statement datafile provided in SWIFT940 standard format that has Cyrillic characters . On the basis of first 3 characters of lines we decide what information is provided in the line hence using POSITION is the only solution...
Categories: DBA Blogs

JSON query returning ORA-01460

Fri, 2016-12-09 06:26
We have a problem with a JSON query retruning ORA-01460. I thin it's because it's referring to an actual parameter formally defined as varchar2. This string is > 4000 (about 4400 chars). I see this error can be replicated by referring to a large ...
Categories: DBA Blogs

condition after join vs where

Fri, 2016-12-09 06:26
Hi Tom, Good day. Which of this queries is better in performance specially when joining with multiple tables and huge data ? example. 1.option 1 SELECT h.name, h.je_source, l.entered_dr, l.accounted_dr FROM gl_je_headers h INNER JOIN...
Categories: DBA Blogs

Need alternative for PIVOT for dynamic date values

Fri, 2016-12-09 06:26
Hi Tom, I need a alternative for PIVOT, i need to pass the date values dynamically. Date should fetch from current month first day to last day. Result should be count of employees assigned to a shift on each day of the month. Please help me on t...
Categories: DBA Blogs

Use pragma

Fri, 2016-12-09 06:26
Hi Tom, I have a stored procedure where I have to check values of several parameters and if they are empty I have to return -1. Oracle cannot use return statement. I cannot use function because it return control to stored procedure. If I use pragma,...
Categories: DBA Blogs

Pages