DBA Blogs

Real Time Scenarios & duplicate rows

Tom Kyte - Fri, 2016-07-29 11:26
I have two questions 1.) Deleting Duplicate rows DELETE FROM EMP1 WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM EMP1 GROUP BY EMPNO); the above command deletes the duplicate rows from a given table such as emp1 in this case, group by eliminat...
Categories: DBA Blogs

Links for 2016-07-28 [del.icio.us]

Categories: DBA Blogs

Cascading calculated LAG values

Tom Kyte - Thu, 2016-07-28 17:06
Oracle 11.2.0.4 Example SQL to explain my question: https://livesql.oracle.com/apex/livesql/file/content_DMZ4GO3W9QL4BONQQB8I7NP5X.html The SQL I am working on is monthly data based on two categories (CATEGORY_A and CATEGORY_B). While we get valu...
Categories: DBA Blogs

DDL timeout parameter limit

Tom Kyte - Thu, 2016-07-28 17:06
Hi Tom I have read in the below link that DDL will not require any downtime starting from Oracle 11g (understood that same applies to Oracle 12c): http://www.oracle.com/technetwork/database/features/online-ops-087977.html I am using Oracle 12c...
Categories: DBA Blogs

Return first character of each word

Tom Kyte - Thu, 2016-07-28 17:06
Sir can you help me regarding this query in SQL by using character functions only.... I/P : ABC DEF GHI Output: ADG
Categories: DBA Blogs

ORA-00980 translation is no longer valid

Tom Kyte - Thu, 2016-07-28 17:06
Dear Sir, I am exporting a database from one system and importing it in another system. after importing its shows the error ORA-00980 tranlation is no longer valid everywhere i can see the solution but my question is 1)its working well with wit...
Categories: DBA Blogs

Invalid rowid in deadlock trace file...I must be missing something

Tom Kyte - Thu, 2016-07-28 17:06
Hi! We recently had an event where a trigger was changed by only a few lines resulting in hundreds of deadlocks a minute. We changed it back and all is well. I'm trying to figure out what happened though. The change in the trigger was if inserting s...
Categories: DBA Blogs

defferable

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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)

Tom Kyte - 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

Tom Kyte - 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)

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs