Tom Kyte

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

Index creation taking more time

15 hours 35 min ago
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

15 hours 35 min ago
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

15 hours 35 min ago
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

15 hours 35 min ago
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

Oracle Utilities ( SQL Loader) for Ubuntu

Thu, 2016-12-08 12:06
I need to install SQL Loader in ubuntu to access the Oracle 12c r1 on different box. My understanding is that Ubuntu is not certified for Oracle . Want to confirm my understanding . If yes , please provide the link to install instructions.
Categories: DBA Blogs

Double quotes found on the cloud which are not in the create script

Thu, 2016-12-08 12:06
Mr. Kyte... We have purchased a few instances of Oracle 12c in the cloud and most recently have encountered a curious double quoting in our tables. create table t_is_it_quoted ( is_this_text_quoted varchar2(20) ); when we look at...
Categories: DBA Blogs

Insert/update table based on condition

Thu, 2016-12-08 12:06
We have 2 tables one having 4 columns and second has 2 columns. Create table A(Currency_old varchar(20),Currency_indicator_old varchar(20),Currency_New varchar(20),Currency_indicator_new varchar(20)); Create table B (Currency_old varchar(20),C...
Categories: DBA Blogs

DML operations in before/after trigger

Thu, 2016-12-08 12:06
SQL> CREATE OR REPLACE package DB_ADMIN.TRI_pkg is 2 type t_dblogin is table of t1%rowtype index by pls_integer; 3 type t_audit is table of audit_tab%rowtype index by pls_integer; 4 5 v_dblogon tri_pkg.T_DBLOGIN; 6 v_audit tri_pkg.t_audit; ...
Categories: DBA Blogs

SQL Loader to load multiple tables from CSV input file

Thu, 2016-12-08 12:06
I have data file which has comma separated values : col1,col2,col3,col4,Key1,Value1,Key2,Value2,Key3,Value3............ Requirement is to load col1-col4 inTab1(col1,col2,col3,col4) AND second part of data is key/Value pair, number of key/value...
Categories: DBA Blogs

Non-pipelined table function with dynamic sql statement

Thu, 2016-12-08 12:06
Hello, Ask Tom Team. Would you mind telling me how I can create table function without pipelined option (I think that it unnecessary, because I have not a big data in it) in a package level using dynamic sql and table of records collection. I know ...
Categories: DBA Blogs

Can we update a locked row column in other session ?

Thu, 2016-12-08 12:06
Hi Tom, I have created one table for example like CREATE TABLE reservation ( Reservation_Id NUMBER, Reservation_number NUMBER ); INSERT INTO reservation (Reservation_Id, Reservation_number) VALUES(1, 100001); INSERT INTO reservat...
Categories: DBA Blogs

Change Decimal pointer

Thu, 2016-12-08 12:06
Hi, Please inform how can replace decimal operator dot(.) with comma(,) in oracle database Permanently I want to change it in database. Also no separator is required for numaric values.All decimal fields of table will accept comma(,) i...
Categories: DBA Blogs

How to use Clob parameter inside dynamic sql statement to execute immediate

Thu, 2016-12-08 12:06
Hello, Ask Tom team! There is my problem. i have a package procedure with some parameters: procedure updt(p_col varchar2,p_value clob) is v_update_clob clob; begin v_update_clob:= 'update table set :p_col=:p_value where cond = ''some_conditi...
Categories: DBA Blogs

Retrieving DBMS_OUTPUT.put_line from JDBC?

Wed, 2016-12-07 17:46
I'm using a Java application to process an incoming feed. The app inserts data into some Oracle tables, then runs an Oracle stored procedure. The Java application is called from a UNIX script, connects to Oracle using Oracle's thin client driver an...
Categories: DBA Blogs

index issue with our partitioned table ora14196 and question if plan OK?

Wed, 2016-12-07 17:46
Hello Tom I have an index issue with our partitioned table this table cw_tb_zvlist11 has about 500 mio rows inserted and the insert process is still active I see that unfortunality we created the primary index as a nonunique one. Now I tr...
Categories: DBA Blogs

adding column with default value 0

Wed, 2016-12-07 17:46
Hi Tom, I have read lot of posts which you have discussed on the subject of adding column with some default value. Thanks a lot for such a great service you render to oracle community. Unfortunately one of my friend informed me some bugs are as...
Categories: DBA Blogs

Pages