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

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

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

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

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

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

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

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

Categories: DBA Blogs

Regarding Listener

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

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

Difference between procedure and stored procedure.

Tom Kyte - Mon, 2016-07-25 16:06
Hi Tom, I want to know what is the difference between procedure and stored procedure? Thanks, Deekshith.
Categories: DBA Blogs

Interval partitioning

Tom Kyte - Mon, 2016-07-25 16:06
Hi Tom, I am trying to create a partitioned table so that a date-wise partition is created on inserting a new row for release_date column. But please note that release_date column is having number data type (as per design) and people want to create...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs