DBA Blogs

Challenging Block Corruption Questions

Tom Kyte - Thu, 2017-02-02 23:46
Good Day, I have yet another area where I ask for your help in supplementing Oracle documentation. This case is in regards to block corruptions. This has always been one of those items that I have been hesitant to implement. My guess is that < ...
Categories: DBA Blogs

Determine machine name for application server from Oracle database

Tom Kyte - Thu, 2017-02-02 23:46
A front end is hosted on an application server which connects to Oracle database. How to determine the machine name of this application server from database. From SYS_CONTEXT and v$session we do get client machine name (which is machine name of...
Categories: DBA Blogs

Recursive Function Calling in PL|SQL

Tom Kyte - Thu, 2017-02-02 05:26
How to call a recursive function in pl\sql function xyz(i_birthdate varchar2) return number as y varchar2(25); x number; o_ret_date varchar2(25); I have written code to generate files which takes i_birthdate as input(it includes some curs...
Categories: DBA Blogs

Performance - How to retrieve large data( time series data) faster from Oracle table

Tom Kyte - Thu, 2017-02-02 05:26
We have table as follow : CREATE TABLE PRICE_DATA ( "ID" VARCHAR2(10 BYTE), "TYPE" VARCHAR2(1 BYTE), "P_DATE" DATE, "VALUE" NUMBER(18,6), "IS_CORRECTED" VARCHAR2(1 BYTE) DEFAULT 'N', PRIMARY KEY ("ID", "TYPE", "P_DATE") ENABLE ...
Categories: DBA Blogs

Return nodes with non-null elements from XMLTYPE

Tom Kyte - Thu, 2017-02-02 05:26
Hi I have an xml document that looks something like: <code> <books> <book> <title>To kill a mocking bird</title> <price>5.75</price> <outofstockdate>2017-01-01</outofstockdate> </book> <book> <title>Swallows a...
Categories: DBA Blogs

Updating row multiple times without getting ORA-01779: cannot modify a column which maps to a non key-preserved table

Tom Kyte - Thu, 2017-02-02 05:26
I'd like to do a set based update but getting ORA-01779 with this; create table source (id number, old_val varchar2(20), new_val varchar2(20)); create table target (id number primary key, val varchar2(30)); insert into target values (1,'abc,de...
Categories: DBA Blogs

Regarding DBMS_JOB failure

Tom Kyte - Thu, 2017-02-02 05:26
Hi Tom, I have written a procedure and called another procedure in it which is updating one column in the table. when I am running the standalone procedure it is working fine. But when the same procedure is scheduled through dbms_job.submit the...
Categories: DBA Blogs

Download excel file from blob column

Tom Kyte - Thu, 2017-02-02 05:26
Hi There, I need to download excel file stored in blob column. Is there a way I can download the file without any formatting issues. Thanks!
Categories: DBA Blogs

Partition in 11.2.0.4.0

Tom Kyte - Thu, 2017-02-02 05:26
Hi Chris/Connor, Could you please have a look at below card statement tables which holds billions of data (DB version 11.2.0.4.0). We are in process of Archivng it to different DB and keeping only 5 yrs of data in primary DB. Approch we decide...
Categories: DBA Blogs

Oracle Support do not agree with you what migrated rows don't cause any extra work in a FTS

Tom Kyte - Thu, 2017-02-02 05:26
Hello! In my SR 3-14102569461 i I was faced with the fact that migrated rows very slow down FTS on my table. By Oracle support considers that this is normal behavior and tell me ti find and elliminate all chained rows. I this, support in wrong h...
Categories: DBA Blogs

unpivot xmltable

Tom Kyte - Wed, 2017-02-01 11:06
Hi, I want to parse xml like: <b><i> <xml> <field1>value1</field1> <field2>value2</field2> <field3>value3</field3> </xml> </i></b> I write query, using xmltable and unpivot: <code> select * from (select p.field1, p...
Categories: DBA Blogs

Number type column less than comparison not working

Tom Kyte - Wed, 2017-02-01 11:06
I have created a view of table name, High value and subpartition name columns from the dba_tab_subpartitions table. <code> create view sample as with xml as ( select dbms_xmlgen.getxmltype('select table_name, subpartition_name, high_value from...
Categories: DBA Blogs

“Consistent gets” is higher when query is executed for the first time

Tom Kyte - Wed, 2017-02-01 11:06
I have the following query which when executed for the first time returns "consistent gets" 628066. When I re-execute it, it returns "consistent gets" as 270923. The table itself has 272446 blocks (taken from ALL_TABLES). It goes for a table sc...
Categories: DBA Blogs

How to consume external API with JSON response and write to Oracle DB hosted on AWS RDS

Tom Kyte - Wed, 2017-02-01 11:06
Hi, I am looking for options and best practices for the following use case: - consume an external API every 15 seconds, which returns JSON data and insert the response into an oracle db table - the JSON response has around 80 fields which need...
Categories: DBA Blogs

SQL Query Help Required

Tom Kyte - Wed, 2017-02-01 11:06
I'm new to Oracle and have got a requirement from my client as follows. Eventhough I have got the PL/SQL solution for this requirement, I'm interested in understanding whether a SQL based solution is possible rather than a PL/SQL approach. If yes, p...
Categories: DBA Blogs

Insert Into table_name Select for table with CLOB column is very slow.

Tom Kyte - Wed, 2017-02-01 11:06
Hi Sir, I have a activity for making non partition table to partition table, the table has clob column i was using below approach to fulfill the activity. rename existing table to _old create new partition blank table insert into new blank pa...
Categories: DBA Blogs

Concat all columns

Tom Kyte - Tue, 2017-01-31 16:46
Hello Tom. I want to concat all columns of a row into one string. <code>select * from table</code> should bring out one colum per row, including all fieldvalues as one string. The use auf || didn't work, because I want it for different table...
Categories: DBA Blogs

When are these wait event occur and what are the causes enq: IM - contention for blr and enq: TA - contention

Tom Kyte - Tue, 2017-01-31 16:46
When are these wait event occur and what are the causes and solutions for them. 1) enq: IM - contention for blr 2) enq: TA - contention 3) undo segment tx slot
Categories: DBA Blogs

data base sync method

Tom Kyte - Tue, 2017-01-31 16:46
Hi, I have two data base in production one is DEV db (source DB) and another QA DB(Target db). If any changes apply to Source DB then it has to reflect in target DB but not vice versa. The TARGET DB is in read write mode. Please let me know the ea...
Categories: DBA Blogs

Oracle Database Licensing in the Cloud

Tom Kyte - Tue, 2017-01-31 16:46
Hi Just read the latest release of the Oracle Database licensing in cloud (http://www.oracle.com/us/corporate/pricing/cloud-licensing-070579.pdf), has little concern! Does this mean, we will face doubling our licensing cost if we host our Oracle d...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs