Tom Kyte

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

defferable

Thu, 2016-07-28 17:06
Hi Sir i am new for oracle. ____Q1) 2. Disabled validate - 3. Enabled novalidate 4. Enabled validate Please explain briefly with primary key constraints only with example (either 3Q or 4Q take any one ) Q2)while doing outer join(left/reght) ...
Categories: DBA Blogs

Code enchancement

Thu, 2016-07-28 17:06
Hell Chris, i have tables called t1 and t2. Table T1 Script as follows: <code> DROP TABLE T1 CASCADE CONSTRAINTS; CREATE TABLE T1 ( ID NUMBER ) Insert into T1(ID) Values(2001); Insert into T1(ID) Values(3001); Insert into...
Categories: DBA Blogs

CASE COLUMN WHEN NULL

Thu, 2016-07-28 17:06
hi Why doesn't NULL_CASE2 return the same result as NULL_CASE1? CREATE TABLE CASENULL (DUMMY VARCHAR(10)) INSERT INTO CASENULL VALUES (NULL); INSERT INTO CASENULL VALUES ('X'); COMMIT; SELECT NVL(DUMMY,'NULL') AS DUMMY, CASE WHE...
Categories: DBA Blogs

Exact count of alphabet

Wed, 2016-07-27 22:46
Sir, I want the query so that I have the names of person who are having exactly have 2 'a' in his or her name.. M trying query but m getting more then 2 'a'. But i want exactly two. Thanks.
Categories: DBA Blogs

data pump api - keep master

Wed, 2016-07-27 22:46
Is there a way to keep the master table on the successful completion of a data pump import/export using the API? There looks to be a way to keep the master table when using interactive data pump (keep_master=Y).
Categories: DBA Blogs

database migration from sun sparc to solaris x86_64

Wed, 2016-07-27 22:46
We are planning to migrate our databases from sun sparc machine to solaris x86_64. Both are different endian format. Could you please let me know the best approach to migrate these databases
Categories: DBA Blogs

How to Read and Load Swift Message into Database

Wed, 2016-07-27 22:46
Hi Tom, This is regarding SWIFT MESSAGE (MT900 format). On Daily basis ,We will be receiving Bank to Bank transactions details in the form of SWIFT Message (MT900 format) i.e in .txt file. Here , Ask is to Read and LOAD swift message tags ...
Categories: DBA Blogs

deletion of controlfiles

Wed, 2016-07-27 22:46
hi Team, even after deleting all the controlfiles the checkpoint doesn't gives me error, where the checkpoint writes the information where no controlfile is available? Kindly clarify.
Categories: DBA Blogs

table join evaluation (ORA-01722: invalid number)

Wed, 2016-07-27 22:46
Hello AskTom Team, Could you please explain how optimizer evaluates the join with wrong data types? <code> CREATE TABLE TEST1(COL1 NUMBER,COL2 VARCHAR2(100)); CREATE TABLE TEST2(COL1 NUMBER,COL2 VARCHAR2(100)); CREATE TABLE TEST3(COL1 NUMBER...
Categories: DBA Blogs

ORA-12154

Wed, 2016-07-27 22:46
Hi ,I installed Oracle 12c on my laptop , and when i try to connect from form builder gets this error ORA-12154 :tns : couldn't resolve the connect identifier specified I tried all ways but not solved I hope anyone can help me cause I only know ab...
Categories: DBA Blogs

Char vs Varchar (data location)

Wed, 2016-07-27 22:46
Hello Tom, I read your article https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1542606219593 (and would have replied there, but couldn't figure out how to do that) and was wondering if this MS SQL "feature" I read about in "Bu...
Categories: DBA Blogs

Inserts and Stats

Wed, 2016-07-27 04:46
I know this is a newbie question but I have to ask to get an outside perspective. Table A has 20 columns of mixed data types and there are a number of large inserts run through out the day. Now here is the question no laughing Do stats impact the per...
Categories: DBA Blogs

Hash Partitioning

Wed, 2016-07-27 04:46
Tom, I am having issue with data distribution among different partitions. example: create table t partition by HASH(X) ( PARTITIONS 32 STORE IN (VRI) AS (SELECT CAST ( ds.X AS VARCHAR2 (20))AS X, FROM test ds ); ) / ...
Categories: DBA Blogs

ORA-01502: index 'KRISHNA.PK_EMPNO' or partition of such index is in unusable state

Wed, 2016-07-27 04:46
HI TOM, 1)DESTINATION TABLE NAME IS:TEMP(EMPNO NUMBER(3) CONSTRAINT PK_EMPNO PRIMARY KEY,ENAME VARCHAR2(10),JOB VARCHAR2(10),SAL INTEGER) I'M LOADING DATA FROM FLAT FILE into TEMP TABLE USING SQL*LOADER THROUGH DIRECT METHOD FLAT FILE IS ----...
Categories: DBA Blogs

single field having multiple line value. issue in extracted file.

Wed, 2016-07-27 04:46
Hi Every time I am facing some issues, please help on this. Test cases are given below CREATE TABLE AA ( ID NUMBER NOT NULL, NAME VARCHAR2(80 BYTE) NOT NULL, BANK_ADDRESS VARCHAR2(1000 BYTE) ); INSERT INTO AA VALUES (1,?MGR?, `IC...
Categories: DBA Blogs

Oracle home and connecting to the database

Wed, 2016-07-27 04:46
Sir im a beginer to oracle database my query is,How we can connect to a database if we do not know the ORACLE_HOME. eg: if we have 5 databases running in a server and there are 5 different ORACLE_HOME ( ie, ORACLE_HOME is different for each datab...
Categories: DBA Blogs

Hierarchical XML

Wed, 2016-07-27 04:46
Hi Team, I have the below data in a table:- id parent_id 1 2 3 1 4 1 5 2 6 2 7 3 8 3 I have tried hierarchical functions and I have got the below data SELECT id, parent_id, LEVEL, CONNECT_...
Categories: DBA Blogs

SQL to calculate date week numbers

Wed, 2016-07-27 04:46
Hi, I would appreciate if someone could point me in the right direction here. How do I write this SQl statement for below. Here is the Criteria below: I'm trying to go back 52 weeks based on (today's date). So for next week this week will be tw...
Categories: DBA Blogs

Regarding Listener

Mon, 2016-07-25 16:06
Hello sir, Currently i am working in Clover infotech as a ORACLE DBA...actually i wanted to know that is there any hard limit for number of Listeners in oracle . Means how many listeners we can configure in single database and also how many con...
Categories: DBA Blogs

Recover Catalog Manager

Mon, 2016-07-25 16:06
I understand that Recover Catalog Manager does has the metadata of the registered database, if the registered database has been recovered by doing restlogs(once we reset the logs we cannot use the old backups using the control file of the database as...
Categories: DBA Blogs

Pages