Tom Kyte

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

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

Using BULK COLLECT

Tue, 2016-07-12 17:26
Hi Tom, I am running into an issue while using BULK COLLECT INTO in a stored procedure. I use a cursor to get data (42 million rows, 70 columns) from a remote database (a table in Teradata), then insert those records into a table in Oracle. After ...
Categories: DBA Blogs

Decide Query performance

Mon, 2016-07-11 23:06
How we define which query is run slower? Means how we can say that query takes more time and require performance tuning?
Categories: DBA Blogs

find an error at online document

Mon, 2016-07-11 23:06
Hi I found a error at online document when I study the oracle. https://docs.oracle.com/database/121/CWADD/crsref.htm#CWADD91788 I do not where to submit this .So I write here, May it can help . crsctl config has Use the <b><i><code>crsctl ...
Categories: DBA Blogs

Grant Access on DB_Link to other User

Mon, 2016-07-11 23:06
I need to give access to other user on current DB_LINK, can you provide command ? Current Link: sittest Grant to User: Z9XBIQ1_DW Regards Chaudhry
Categories: DBA Blogs

Forcing a query to timeout

Mon, 2016-07-11 23:06
I have several PL/SQL stored procedure reports that monitor various aspects of our database environment (ASM, tablespace, backups, alerts, ...). These all have the same framework, loop thru the entries in a table, using a db_link, query the remote sy...
Categories: DBA Blogs

Network-based IMPDP with local SCN?

Mon, 2016-07-11 23:06
Hi, I found a procedure for a network based impdp. The procedure sets the actual (flashback) scn of the target system (see below). That?s a mistake, isn?t it? What will impdp do with this invalid scn? Simply ignore it? Cheers, Markus ...
Categories: DBA Blogs

Question on multiple DML in FORALL

Mon, 2016-07-11 04:46
Hi, I have below question on FORALL 1) <code> forall 1 .. l_var.count delete tab1 where id=l_var(i); forall 1 .. l_var.count delete tab2 where id=l_var(i); forall 1 .. l_var.count delete tab3 where id=l_var(i); </code> 2)...
Categories: DBA Blogs

Error when import large data from 11.2.0.3 into 11.2.0.4 Oracle Exadata

Mon, 2016-07-11 04:46
Hi, We hit this and no clue how it suddenly happen. Issue cronology as below (using Toad version 10.2, 10.5 and 11). 1) Export out from Exadata DB version 11.2.0.3 table A with 4million records - ended successfully. 2) Import into Exadata DB ...
Categories: DBA Blogs

Adding subpartitions

Mon, 2016-07-11 04:46
Hello Sir I have a table partitioned on RANGE (daily) (interval partitioning) - there are about 300 partitions . Now I want to add a sub-partition to each of these based on a value in a different column. Should I come up with a script with 300 A...
Categories: DBA Blogs

Distinct Values from Multidimensional Collection

Mon, 2016-07-11 04:46
Hi Tom, Is there a way to retrieve Unique records from a multidimensional collection? DISTINCT and MULTISET operators seem to work on collections only when they have 1 field. I have the below <b>TYPE TYP_TABLE_REC IS RECORD ( SCHEMA_NA...
Categories: DBA Blogs

Best Way to Design a Table

Mon, 2016-07-11 04:46
I've to design Professional Tax table for Payroll(INDIA) , For every state it has its own prof tax according to their gross salary Consider One state Say MAHARASHTRA IF PR-GROSS < 2500.00 TAX= 30.00 IF PR-GROSS < 3500.00 TAX= 60.00 IF PR...
Categories: DBA Blogs

Pages