DBA Blogs

Insert Text as header footer PDF BLOB

Tom Kyte - Sat, 2017-03-18 13:06
Hi, I have a business requirement to stamp every PDF which is stored as blob in Oracle 12c with a vendor name as header and footer. We should be able to pass the vendor name as argument to the PDF and it will be placed into the header and foote...
Categories: DBA Blogs

Wait in DBA_HIST_ACTIVE_SESS_HISTORY

Tom Kyte - Sat, 2017-03-18 13:06
Hi, I am trying to get to the wait event from a entry in DBA_HIST_ACTIVE_SESS_HISTORY where event for wait is "enq: TX - index contention". In the row I see P1,P2 and P3. Object id is "-1" . It is a INSERT query. How to get the details of the inde...
Categories: DBA Blogs

ORA-04021: timeout occurred while waiting to lock object ORA-06512: at "SYS.DBMS_STATS",

Tom Kyte - Sat, 2017-03-18 13:06
Hi, I am performing the below tasks by using ODI(Oracle data integrator): Step-1: Truncate the Table1 Step2: Truncate table 2 Step-3: Drop the INdex's on Table1 & Table 2 Step4: Insert the data into Table1 Commit Step-5: Insert the data i...
Categories: DBA Blogs

point in time clone

Tom Kyte - Sat, 2017-03-18 13:06
Hi Tom, I started hot backup rman at 9am and it got completed at 2pm,In the middle there was update query running more than 5 hrs which is started @10am and completed by @5pm (at end committed) so if I want to clone this database with this fu...
Categories: DBA Blogs

is there any performance impact if a table columns shares different tablespaces?

Tom Kyte - Sat, 2017-03-18 13:06
Hi Tom, is there any performance impact if a table columns shares different tablespaces? e.g. <code> CREATE TABLE DATA ( DATA_ID NUMBER, PROPERTY_DATA CLOB, PROPERTY_DATA_BIN BLOB, BIN_TYPE VARCHAR2(10 BYTE), SOURCE_INST...
Categories: DBA Blogs

executable jobs in scheduler

Tom Kyte - Sat, 2017-03-18 13:06
Hello, Tom! Tell me plz how i can copies file from server to another computer throught executable jobs ? Code of my job: begin dbms_scheduler.create_job ( job_name =>'tstcmd', job_type =>'executable', job_action =>'D:\Copy_export_data.bat'...
Categories: DBA Blogs

Can Sys object have 2 paswords ?

Tom Kyte - Sat, 2017-03-18 13:06
Hi Tom, we have an instance of oracle running on one of our client server . But I found a strange thing, that I can login into the oracle server using 2 different password and it let us do that . Is it possible that Sys object have two passwo...
Categories: DBA Blogs

how to reclaim space from table fnd_lobs?

Tom Kyte - Sat, 2017-03-18 13:06
I want to reclaim the space from the table "fnd_lobs", which measures 100 gb, but it actually measures 50 gb, I have not found any method to recover the other 50 gb. they are looking for in google, and in a few forums, there was a user who said that...
Categories: DBA Blogs

performance problem

Tom Kyte - Sat, 2017-03-18 13:06
Hi Tom , Good Afternoon . I have one stored procedure which is running for long time . Could you please suggest how to optimize the code . There are having four tables. 1.staging_product 2.stagin_retail 3.product 4.curr_retail The pr...
Categories: DBA Blogs

bulk update

Tom Kyte - Fri, 2017-03-17 18:46
Hi Tom, I have table called customers with data below SQL> select * from customers; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- --------...
Categories: DBA Blogs

Unsuccessful addition of column with NOT NULL constraint ends with creation hidden columns SYS_C[...] on Oracle12c

Tom Kyte - Fri, 2017-03-17 18:46
Hello! Recently I've encountered a problem with adding new column to existing, not empty table. New column had a NOT NULL constraint, so ALTER TABLE command failed with ORA-01758 (that's clear). But having looked at DBA/USER_TAB_COLS dictionary ...
Categories: DBA Blogs

SQL Query to split rows based on dates

Tom Kyte - Fri, 2017-03-17 18:46
Could you please help me with a SQL that would generate the desired output? <code> create table user_tmp (vname varchar(15) , external_pgm varchar(1), eff_date date, exp_date date); insert into user_tmp values('JOHN','A', '01-JAN-1...
Categories: DBA Blogs

How can we identify a session in GV_$SESSION with DBMS_SESSION.UNIQUE_SESSION_ID ?

Tom Kyte - Fri, 2017-03-17 18:46
Actually (one node RAC) I can identify a session in V_$SESSION with AUDSID = "sys_context('USERENV', 'SESSIONID')". We are switching to multiple node RAC and I have to use GV_$SESSION and DBMS_SESSION.UNIQUE_SESSION_ID to get a unique id across al...
Categories: DBA Blogs

Convert BLOB file to readable format

Tom Kyte - Fri, 2017-03-17 18:46
Hi, There is one table which has data type as BLOB for one of the columns. We need to open that file and see the context. Please suggest, how can we convert that file and make it in readable format? Thanks.
Categories: DBA Blogs

java.sql.SQLRecoverableException: ORA-01034: ORACLE not available

Tom Kyte - Fri, 2017-03-17 18:46
I try to make connection to oracle db on Linux from java application. T got the following error. What is the cause and hot to resolve it? java.sql.SQLRecoverableException: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not ex...
Categories: DBA Blogs

Links for 2017-03-16 [del.icio.us]

Categories: DBA Blogs

Two identical queries with same parameter values have different execution plans ..

Tom Kyte - Fri, 2017-03-17 00:26
Hello and thanks for your time. We noticed an odd behavior of Oracle 12.1 query plan selection for the same query. The query generated from .Net Entity Framework has an inefficient query plan than if we run the same query in SQL Developer. When we...
Categories: DBA Blogs

Can I set the basis for SYSDATE within a session?

Tom Kyte - Fri, 2017-03-17 00:26
When writing code that deals with time, it would be very useful to be able to "set" the starting point for SYSDATE within a session. For example, suppose I want to select one set of data if the query runs in March and a different set if it runs in...
Categories: DBA Blogs

Update a nested column with a database tool like Oracle sql developer

Tom Kyte - Fri, 2017-03-17 00:26
Hello , i have another question about nested tables. When i created the table projects with the column project_name and categories. categories is the nested column. Can i easy update the column categories (viz : i write direct in the column usin...
Categories: DBA Blogs

Accessing Nested Tables Elements

Tom Kyte - Fri, 2017-03-17 00:26
Hello , i want to have multiple value per cell and i used nested tables. After creating my table projects with a the nested column categories, i insert some rows. but when i do select * from tables , i have the error unsupported data type by the ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs