DBA Blogs

Accessing dynamically generated pivot columns through a cursor

Tom Kyte - Tue, 2018-02-06 09:06
Hi Tom, I have a PL/SQL report wherein the columns change dynamically (refer sample SQL: https://livesql.oracle.com/apex/livesql/s/f8gbm32e2acju7jzvuzjjh3a1) based on the parameter. Suppose if the report is run for the Q1 2018, then it will have...
Categories: DBA Blogs

how to create VIEW in a schema, have to retrieve LOB data over db_link

Tom Kyte - Tue, 2018-02-06 09:06
ORA-22992: cannot use LOB locators selected from remote tables 22992. 00000 - "cannot use LOB locators selected from remote tables" *Cause: A remote LOB column cannot be referenced. *Action: Remove references to LOBs in remote tables. ...
Categories: DBA Blogs

Partner Webcast – Database Security Assessment Tool and GDPR: Uncover new opportunities

As part of Oracle’s defense in depth capabilities, the Oracle Database Security Assessment Tool (DBSAT) helps identify areas where your database configuration, operation, or implementation...

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

Constraint to allow either NULL or distinct values

Tom Kyte - Mon, 2018-02-05 15:06
Hi guys, I wonder if it is possible to create a constraint on a table which does either allow NULL in a specified column for a group of items *or* a unique value. I set up an example. Imagine you can either set a jedi-unit to manage themselves...
Categories: DBA Blogs

Avoiding multiple json traversals

Tom Kyte - Mon, 2018-02-05 15:06
Good morning folks I'm trying to consume JSON documents that basically follow this structure: <code> { "items": [ { "id": "111A", "someAttributes": [ { "name": "anAttribute", "value": "A Value" }, { "nam...
Categories: DBA Blogs

Error: "Specified partition does not exist" when creating multi-group XML Index on partitioned table

Tom Kyte - Mon, 2018-02-05 15:06
Hi TOM We are trying to create a multi-group XML Index on a partitioned table. Table has no sub-partitions as they are not yet supported by XML Indexes, but we need the XML Index to have more than one group. This is the table: <code>CREAT...
Categories: DBA Blogs

Insert date from Oracle to PostgreSQL

Tom Kyte - Mon, 2018-02-05 15:06
I try to insert date form oracle to postgresql my database date format is dd/mm/yyyy postgresql date format yyyy/mm/dd <code>insert into "public"."test"@PG_LINK select 'test' name, TO_DATE (TO_CHAR(SU.DATE_OF_BIRTH, 'yyyy/mm/dd'),'yyyy/mm/dd...
Categories: DBA Blogs

Do something when the transaction ends

Tom Kyte - Mon, 2018-02-05 15:06
I work with Delphi and Oracle, and what I'm trying to do is change the value of a field by informing a user by mail. This is being done wrongly with a trigger. I thought that in the compound trigger in the after statement the transaction was over (co...
Categories: DBA Blogs

extract an xml node

Tom Kyte - Mon, 2018-02-05 15:06
I have some xml that has a namespace defined in a node within a namespace node ( see Ah:AppHdr ), I would like to extract the value of the element Ah:MsgRef .Can I extract it, if so how? When I try I get an XPATH error. declare l_req xmlType := x...
Categories: DBA Blogs

Can not make read only tablespace which has offline datafile

Tom Kyte - Mon, 2018-02-05 15:06
Hello Dears, A couple of months ago, we had problem: we lost most recent added disk on the server. Which contained latest 4 datafiles. As we had no database/archivelog backup, we had to mark those datafiles offline drop and open the instance. W...
Categories: DBA Blogs

How to run our Integration tests in Parallel against Oracle with a clean state for every test

Tom Kyte - Sun, 2018-02-04 20:46
He have a bunch of junit tests that interact with the DB. As we all know when you write tests that interact with a db you always have the problem of random DB state that can make these types of DB tests brittle. Our DB is very big and has multi schem...
Categories: DBA Blogs

Apex item label justification

Tom Kyte - Sun, 2018-02-04 20:46
hey, I have an Apex Form Page with several items like that:(blanks instead of -) Number field 1: [ ] -----------field 2: [ ] ------any field 3: [ ] so the item label, is right side. My question is: Is it poss...
Categories: DBA Blogs

Rows and column size - how large can a table be

Tom Kyte - Sat, 2018-02-03 07:46
Hi Tom, Can an oracle database accommodate 127 million rows with maximum of 17 columns where each character is 1 in both rows and columns
Categories: DBA Blogs

Restore archivelogs from RMAN backup

Learn DB Concepts with me... - Fri, 2018-02-02 14:06




Restore archive logs from RMAN backup


rman> restore archivelog from logseq=37501 until logseq=37798 thread=1;

or

rmna> restore archivelog between sequence 37501 and 37798 ;
Categories: DBA Blogs

Difference Between Unique Index and Primary Key Index

Tom Kyte - Fri, 2018-02-02 13:26
IS fetching row(s) using Primary key Index (in where clause) is better then Fetching row(s) using Unique Index (in where clause)? Is there any internal difference between those Unique Index and Primary Key Index?
Categories: DBA Blogs

PL/SQL procedure issue (bulk collect and insert on a dynamic table)

Tom Kyte - Fri, 2018-02-02 13:26
Hi All, Requirement : We need to pass table name as a parameter into the Procedure and also further I need to concatenate that value with a string to make a correct table name which will be available in database. For ex :As per my code , if a...
Categories: DBA Blogs

Lag Analytics Function

Tom Kyte - Fri, 2018-02-02 13:26
Hi Tom, Can you please help me with the below: I have a table like below: <code>Yr Qtr Mth Sales 2010 1 1 1000 2010 1 2 2000 2010 1 3 2500 2010 2 4 3000 2010 2 5 3500 2010 2 ...
Categories: DBA Blogs

How to use ASH report

Tom Kyte - Fri, 2018-02-02 13:26
Hi Tom, Few days back I attended an interview, and one question which I failed to answer properly,was How to use ASH report. I know few things about ASH report like Top user event,Top CPU time and which SQL has spent how much % on which event. But...
Categories: DBA Blogs

Oracle SQL developer's native package???

Tom Kyte - Thu, 2018-02-01 19:06
hi Tom: when I check my ODBC connection under Administration tool, I don't not find any Oracle driver. the strange thing is I can still use Oracle SQL developer to connect to Oracle. I assume we always need a Oracle driver to connect to oracle dat...
Categories: DBA Blogs

date format

Tom Kyte - Thu, 2018-02-01 19:06
Hi Tom, In my pl/sql program I get date in format of string .. say like '2010/06/30 00:00:00' Now I need to convert this into a date and pass it as a parameter to another program.. what is the best way to that.. do i need to read nls_dat...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs