Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 1 month 1 day ago

Rebuild optimize failing for Text index in RDBMS 12c (12.1.0.2.0) CDB-PDB config

Thu, 2016-07-14 05:46
Hi Guys, DB: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production OS: OEL 6.5 I am testing use case for Oracle Text index on 12c CDB-PDB config (single PDB), and found that REBUILD optimize is failing with internal exce...
Categories: DBA Blogs

Spatial queries

Thu, 2016-07-14 05:46
Hi Tom I am calculating distance between two 2 points (using their gps co-ordinates) from the below query (Query 1). One column (name geo_location of sdo_geometry datatype where spatial index is created) in my table stores the geometric location o...
Categories: DBA Blogs

Session Memory Release

Thu, 2016-07-14 05:46
Hi Connor, Wanted to understand the session memory release process. 1] A database 2] Connections are established to the database, through connection pool 3] Connection pool is configured in Application Server [ Weblogic ] 4] The connection p...
Categories: DBA Blogs

Is it possible to overwrite OS User when connecting to oracle database from a .NET Application

Thu, 2016-07-14 05:46
I have a .NET Desktop Application which runs on our user's machine individually, as part of one of our functionality we need to pull data from Upstream that has data stored in an Oracle Database. Currently we get data from this oracle db by logging ...
Categories: DBA Blogs

Cardinality of table collections in SQL

Thu, 2016-07-14 05:46
I'm doing two queries, one will use an index I am trying to use. The first query it does not use the index and does full table scan. number_tbl will be a parameter passed into a stored procedure. We are using a type to pass in a table of numbers. ...
Categories: DBA Blogs

Migration of about 20000 Tablespaces from Solaris Sparc 10 with Oracle 9.2.0.4 to OL 7.2 64bit Oracle 12c

Thu, 2016-07-14 05:46
Hello Tom we plan a migration of our production system. we think we have two possibilities 1) one is from the 9i system exporting all TS's with exp and go directly to the 12c and do with imp all tablespaces in the new database but this optio...
Categories: DBA Blogs

sql loader delimiters

Thu, 2016-07-14 05:46
columns separated by ',' in data file but in control file by mistake if i mentioned field terminated '/' in control file.then what happen while loading data.
Categories: DBA Blogs

How can we skip existing values while inserting with sequence to id column(unique)

Wed, 2016-07-13 11:46
Hi Tom, Can you please let me know best possible way for below scenario to skip existing value while loading through sequence Table Structure: Tem_seq_check(id number(3) unique,name varchar2(5)); Tem_name(name varchar2(5)). I will insert...
Categories: DBA Blogs

Message queue

Wed, 2016-07-13 11:46
Hi, I have a situation where I was asked to use message queue technique, can you please suggest me whether it is possible/feasible or not. There are two data bases D1 & D2, upon changing the status of a particular field in D1 some data need to ...
Categories: DBA Blogs

get ORA-01031: insufficient privileges when execute procedure

Wed, 2016-07-13 11:46
Hello there: I met the "ORA-01031: insufficient privileges" error, when rebuild index with online option in a procedure. I know the role cannot be used in procedure, so grant some privileges to system(its DBA' user:system privilege: -- 2...
Categories: DBA Blogs

Analytics question

Wed, 2016-07-13 11:46
<code> I have a table from a 3rd party application that is used to track an order through the various manufacturing operations. A subset of the information looks like this: ORDER OPN STATION CLOSE_DATE ----- --- ------- ----------...
Categories: DBA Blogs

not able to re-create materialized view on prebuilt table

Wed, 2016-07-13 11:46
Steps I am trying to execute : <code> CREATE TABLE sample.MV(application_mode varchar2(25)); CREATE MATERIALIZED VIEW sample.MV ON PREBUILT TABLE REFRESH FORCE ON DEMAND AS SELECT application_mode FROM sample.tbl_name WHERE cnt > 0 ...
Categories: DBA Blogs

oracle lsitener config

Wed, 2016-07-13 11:46
I am trying to understand how the oracle listener gets its config info. I see this when I run lsnrctl status: Listener Parameter File /home/oracle/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /home/oracle/u0...
Categories: DBA Blogs

Archiving log buffer directly to archive logs

Wed, 2016-07-13 11:46
Is it possible to archive contents of log buffer directly to archive logs,instead of writing into redo logs. Will this decrease the load in system I/O. Please explain the reason if it is not possible.
Categories: DBA Blogs

List all caches in Oracle

Tue, 2016-07-12 17:26
Hi Tom, I would like to list all caches in Oracle. Are there any usefull SQL queries which can help me to see some usesull performance data from Oracle's caches? BR, Peter
Categories: DBA Blogs

CASE statement

Tue, 2016-07-12 17:26
Hello Gurus, Please correct me if am framing wrong CASE statement using multiple columns (CASE WHEN (ENA_PRE1 IS NULL AND ENA_RT1 IS NULL) THEN RT_UNE IS NULL ELSE RT_UNE END) Thank you in Advance.
Categories: DBA Blogs

How to allow 100% CPU?

Tue, 2016-07-12 17:26
Hi I am running Oracle Database 11g Release 11.2.0.1.0 (Standard Edition) - 64bit Production on Windows 2008 R2, on a physical machine with a 4 core CPU. Whatever I do, the maximum CPU utilization of oracle.exe process is 25%. This is annoying b...
Categories: DBA Blogs

Column view definition in data dictionary

Tue, 2016-07-12 17:26
Hello there, Is it possible to get view column definition from data dictionary? I mean something similar as for tables - user_tab_columns. Thanks, Dusan
Categories: DBA Blogs

SELECT column from TABLE3, UPDATE column in TABLE2, INSERT all column in TABLE1

Tue, 2016-07-12 17:26
Hi I need to solve some difficult logic process. create table aa ( id int, name_child varchar2(25) ); create table bb ( id int, name_master varchar2(25) ); insert into bb values('-1', 'DUMMY'); bb is the master table, aa is the child...
Categories: DBA Blogs

difference b/w row database and column database, how the data will compressed in database

Tue, 2016-07-12 17:26
Hi Tom, I have to questions i.e; 1.what is difference b/w row database and column database, how can we create indexes on column database? 2.how the data will be compressed in database(on what basis data will be compressed in database)?
Categories: DBA Blogs

Pages