Tom Kyte

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

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

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

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

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

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

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

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

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

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

Difference Between Unique Index and Primary Key Index

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)

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

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

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

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

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

impdp with multiple REMAP_SCHEMA statements tries to load data twice in the same schema

Thu, 2018-02-01 19:06
Dear TOM, We are using impdp Release 11.2.0.3.0 - Production to exchange data between two databases, both in version 11.2.0.3.0 - 64bit Production. We use the following import.par parfile with several REMAP_SCHEMA statements: <code>DUMPFILE=...
Categories: DBA Blogs

No estimate time remainings drop column unused, with checkpoint.

Thu, 2018-02-01 19:06
Hi Tom! I had a problem removing a column set unused(version RDBMS 11.2.0.4) Because he was using CHECKPOINT, could not keep track of a running development, as well as estimate in v$SESSION_LONGOPS. For time remaings estimate, I was using an ave...
Categories: DBA Blogs

Few SQL & PL/SQL question(s)

Thu, 2018-02-01 19:06
Hi, There are three small questions in this, I wanted to ask in december but you guys were on vacation that's why I could not ask. Q1. What is the exact size restrictions of trigger I have read 32k here k is what is it KB or (32000 bytes or 3276...
Categories: DBA Blogs

SQL request with analytics to fill previous values

Thu, 2018-02-01 19:06
Hello, I have a table with this structure table values: <code>(code varchar2(10), date_value date, value number);</code> Example data : <code>'Code1','15/03/2017',25000 'Code1','06/06/2017',26000 'Code1','18/07/2017',29000 'Code1...
Categories: DBA Blogs

where to use metadata table as a plsql developer

Thu, 2018-02-01 00:26
Hii friends, how, when, where to use metadata views like ALL_SOURCE, USER_SOURCE, ALL_DEPENDENCIES, USER_DEPENDENCIES, ALL_OBJECTS, USER_OBJECTS. THANKS.
Categories: DBA Blogs

Identify session using Oracle XA

Thu, 2018-02-01 00:26
Is there a way to identify if the session executing the PL/SQL application is part of the XA Transaction. We have an application running on WebLogic 12.2.1 using a JDBC Pool. It connects to the database through the Connection Pool. Some of the re...
Categories: DBA Blogs

Pages