DBA Blogs

Index creation taking more time

Tom Kyte - 9 hours 51 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

Tom Kyte - 9 hours 51 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

Tom Kyte - 9 hours 51 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

Tom Kyte - 9 hours 51 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

Big Data Preparation Cloud Service (BDP) 16.4.5. released

We are pleased to announce the release of Big Data Preparation Cloud Service (BDP) 16.4.5. This release has a significant number of bug fixes and some exciting new features.  Please review the...

We share our skills to maximize your revenue!
Categories: DBA Blogs

JSON query returning ORA-01460

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Links for 2016-12-08 [del.icio.us]

Categories: DBA Blogs

bash: /bin/install/.oui: No such file or directory

Learn DB Concepts with me... - Thu, 2016-12-08 21:30

 Problem:

[oracle@linux5 database]$ . runInstaller
bash: /bin/install/.oui: No such file or directory
[oracle@linux5 database]$ uname -a
Linux linux5 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013 x86_64 x86_64 x86_64 GNU/Linux


Solution:



[oracle@linux5 database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 20461 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4031 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-11-22_09-46-02AM. Please wait ...[oracle@linux5 database]$
Categories: DBA Blogs

Oracle Utilities ( SQL Loader) for Ubuntu

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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 ?

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs