Tom Kyte

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

Where is alter session coming from? Every time I log in, I see "Session altered."

Fri, 2018-07-06 02:26
Every time I log in to our DB via sqlplus, I see this: $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 5 11:48:29 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c E...
Categories: DBA Blogs

Does Oracle 9i Data Guard have real time sync?

Fri, 2018-07-06 02:26
Hi We have a Oracle 9i database ,When I set up the data guard I want to use real time sync I got a error .So I want to confirm whether Oracle 9i have this future
Categories: DBA Blogs

update cascade

Fri, 2018-07-06 02:26
How do I cascade update on the key fields of linked tables thereby ensuring referencial integrity.
Categories: DBA Blogs

I want to understand this decode function which applied in this query

Fri, 2018-07-06 02:26
<code> SELECT a.syrmn, a.orgcode, SUBSTR (a.accode, 1, 3) || '0000000' caccode, SUBSTR (a.accode, 1, 6) || '0000' saccode, accode, DECODE (SIGN (SUM (DECODE (a.sntb, 'D', a.samnt, -a.samnt))), 1, SUM (...
Categories: DBA Blogs

ORA-01652 unable to extend temp

Thu, 2018-07-05 08:06
Hi Team, I went through some weird situation where I am not able to figure out the reason behind error ORA-01652 unable to extend temp. While executing a PL/SQL block at one point I am facing this issue. ORA-01652 unable to extend temp. Whi...
Categories: DBA Blogs

CentOS as the Operating System for Oracle 12.2 database server

Thu, 2018-07-05 08:06
Hello Experts, We are planning to install Oracle <b>12.2</b> database server on a Linux <b>CentOS 7.4</b>. <b>My first question</b> May I know whether this setting is supported by Oracle? I've already noted that this OS is not listed under t...
Categories: DBA Blogs

Exadata and direct path read

Thu, 2018-07-05 08:06
Hi Tom, In order for smart scan to be achieved , mainly two thing should happen : 1 - The optimizer should choose to full scan the table. 2 - Scanning the data should be done using direct path read. The first requirement is quit simple , bu...
Categories: DBA Blogs

regexp question

Wed, 2018-07-04 13:46
Hi, I am trying to highlight a search term in a result set feeding a web page. So far I can highlight the entire result, but not just the term match. The problem is in white space mismatches between the column data and the search term that is ente...
Categories: DBA Blogs

IMPLICIT CURSOR ATTRIBUTE SQL%NOTFOUND NOT WORKING

Wed, 2018-07-04 13:46
Hi Tom, I am using implicit cursor attributes SQL%NOTFOUND for the below PL/SQL block. But it raises NO_DATA_FOUND exception. It works fine for update and delete statement as they are not going to raise the NO_DATA_FOUND exception. ...
Categories: DBA Blogs

Email Domain Extraction using sql query

Tue, 2018-07-03 01:06
if the part of email before domain matches for eg in : xyz@gmail.com xyzef@gmail.com if xyz and xyzef does not match ,then do not consider this records and if the scenario is like , if non domain part are equal like xyz@gmail.com xyz@g...
Categories: DBA Blogs

Make Index Invisible for a session

Tue, 2018-07-03 01:06
Hi With the advent of In-Memory capabilities in 12C is there a way to make the optimizer ignore indexes for particular table for a session. The reason being if we want to use Oracle 12C as HTAP (Hybrid Transactional Analytical Processing) we wou...
Categories: DBA Blogs

ORA-31011: XML parsing failed issue

Tue, 2018-07-03 01:06
While extracting data from xml parsing issue is coming. Issue is coming while extracting data for field_name 401K_LOAN_1 and 401K_LOAN_2. Please advise. <code>PROCEDURE SP1( SXML IN CLOB, p_status OUT VARCHAR2, p_message OUT VARCHAR...
Categories: DBA Blogs

XMLForest for more than one tables

Tue, 2018-07-03 01:06
hi all i'm trying to make an enquiry from 2 tables with XML function, but i have some problems: this is sql i'm using for this sample select from the 2 different tables i'm using <code>select deptno,dname from dept; select empno,ename,de...
Categories: DBA Blogs

Transpose Rows Into Column

Mon, 2018-07-02 06:46
Hi, My question is regarding transpose of rows into columns: <code>BANNER_CODE/DIV_CODE/LEG_MATNR/SAP_MATNR/MAKTX/LEG_MATKL/SAP_MATKL/LEG_WHERL/SAP_WHERL/ CS/1/10137/58351/BAKE KING CHOCOLATE RICE 160G/384/10203004/34/SG/1 GH/1/36762/212615/M...
Categories: DBA Blogs

acfs not supported in oracle linux 7 though oracle documents says its supported.

Sun, 2018-07-01 12:26
Hi tom, In a rac environment with grid infrastructure i am trying to configure acfs. But i am getting following error. ACFS-9459: ADVM/ACFS is not supported on this OS version: 'unknown' ACFS-9201: Not Supported Blogs says i need to appl...
Categories: DBA Blogs

Creating linguistic indexes for CANADIAN FRENCH

Sun, 2018-07-01 12:26
When creating a linguistic index, I am not able to specify CANADIAN FRENCH. Oracle reports that the NLS parameter string is invalid. I suspect that it's because there is a space in it, but the answer eludes me. Here is a short example of a script ...
Categories: DBA Blogs

ora_rowscn - is it always incremental,

Sun, 2018-07-01 12:26
Hello, I want to sqoop data out of my Oracle 11.2 database on a daily basis. However, I want to do only incremental extracts. Apparently, scn_to_timestamp doesn't always work due to ORA-08181: specified number is not a valid system change number...
Categories: DBA Blogs

How to get the operating system user OSUSER from Oracle

Sat, 2018-06-30 18:06
I believe there is a way to get the LAN user ID of a user from within an Oracle query. I thought the variable was called OSUSER or OS_USER. I've tried select os_user from dual, but that doesn't work. Yet I think I'm close. Can you lead me in the ...
Categories: DBA Blogs

Some Interview Questions

Fri, 2018-06-29 05:26
Hi Tom, Recently I attened a inteview , I have given the questions which I'm not able to answer. 1.When using rman ,oracle will use its own processes than the OS one.the questions is what are the advantages oracle is getting by using it ow...
Categories: DBA Blogs

what's the exact difference bwtween force logging and supplemental logging?

Thu, 2018-06-28 11:06
I am confusing about the difference bwtween force logging and supplemental logging, could you explain that for me? and are both force logging and supplemental loggins necessary for goldengate? thanks!
Categories: DBA Blogs

Pages