Tom Kyte

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

Conditional index

Wed, 2017-11-22 12:46
Tom, Thanks for taking my question. I am trying to conditionally index rows in a table. In SQL Server 2008 there is a feature called filtered indexes that allows you to create an index with a where clause. So I have a table abc: <code>create...
Categories: DBA Blogs

Transfer data from one db to another db over db link using trigger

Wed, 2017-11-22 12:46
Hi, I am working on a project in which data marts are involved. We are creating triggers to transfer data from OLTP DB to data mart (Online extraction). Following is the code of a trigger for a table involving clob column. I have seen different solut...
Categories: DBA Blogs

RMAN and archivelogs

Tue, 2017-11-21 18:26
Hi, I have read quite a bit on Oracles RMAN utility and know that for hot backups RMAN doesn't use old method of placing tablespaces in Archive log mode freezing datafile headers & writing changes to Redo/ Archive logs. Hence a company with a larg...
Categories: DBA Blogs

Difference between "consistent gets direct" and "physical reads direct"

Tue, 2017-11-21 18:26
Hi Tom/Team, Could you explain the difference between "consistent gets direct" and "physical reads direct"? Thanks & Regards
Categories: DBA Blogs

Import data from Flat File to two different Table using UTL_FILE.

Tue, 2017-11-21 00:06
Hi Please help this Question. Import data from Following Flat File to two different Table using UTL_FILE. a. EMP and b. DEPT Note --- 1. In Last Line NULL Employee Should not Entry into Table. 2. Deptno Should go to both the Table EMP a...
Categories: DBA Blogs

Repeating parent-nodes in hierarchical query

Tue, 2017-11-21 00:06
Hello AskTOM Team, with the schema as provided in the LiveSQL Link (which is simply the example EMP/DEPT schema), I ran the following query <code> select case when LEVEL = 1 then ENAME else rp...
Categories: DBA Blogs

What causes a materialized view to get invalidated

Mon, 2017-11-20 05:46
Hello, I have a materialized view whose definition looks like this: CREATE MATERIALIZED VIEW <owner>.<materialized view name> (<column list>) TABLESPACE <tablespace name> PCTUSED 0 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (...
Categories: DBA Blogs

Distributed Option in Oracle 7

Mon, 2017-11-20 05:46
Hi Tom! Is Oracle Distributed Option required for accessing remote databases? Also, if there is some restriction on database version i.e. the version shuld be same on all nodes?
Categories: DBA Blogs

BEFORE Triggers Fired Multiple Times

Sat, 2017-11-18 17:06
Dear Tom, I have a question about triggers execution: according to documentation at http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1169 "BEFORE Triggers Fired Multiple Times If an UPDATE or DELETE ...
Categories: DBA Blogs

Behavior of dba_hist_sgastat metrics 'free memory' and 'SQL' for pool 'shared pool' have changed with 12.1.0.2

Sat, 2017-11-18 17:06
We are upgrading from 11.2.0.4 to 12.1.0.2 and we are seeing a new behavior for the "free memory" in the shared pool. We are looking at AWR in dba_hist_sgastat. While in 11g it goes up and down, in 12c it is non-decreasing, over several days at least...
Categories: DBA Blogs

how SPM works with cursor_sharing=force?

Fri, 2017-11-17 22:46
<code>Hello Tom The post https://blogs.oracle.com/optimizer/how-do-adaptive-cursor-sharing-and-sql-plan-management-interact explains the interaction between cursor sharing and SPM quite clear. But I met some unexpected results if I set the cursor_...
Categories: DBA Blogs

Whitespaces bug

Fri, 2017-11-17 22:46
I have noticed a weird bug in our Oracle database as we are moving from one database (Oracle 11.2.0.3.0) to another database (Oracle 11.2.4.0). Database servers are in different data center with different operating systems and support teams. So the v...
Categories: DBA Blogs

Generate Dates Between Two Given Dates Through Cursor

Fri, 2017-11-17 04:26
I have a table as below Create Session_Detail(ID,Date,Day,Status) I need a button where i write the cursor which is reading start_date and end_date and generate the dates in session_detail as per given start_date and end_date??
Categories: DBA Blogs

Exporting multiple rows from a table to single row text using utl

Thu, 2017-11-16 10:06
In my project, I need export multiple rows from a single column to single row text using utl file Example below My table : Employees Employee_name ----------------------------- Smith John Tom Adam And my output text file should export like...
Categories: DBA Blogs

INSTR Function to find exact match of a value

Thu, 2017-11-16 10:06
Hi, Struggling for sometime now. I have a list read from a table column - (1,2,3,10,11) I need to check if my number is present in the above list So for 1 INSTR(1, List) return 2 which is fine. Now the issue is if the list contain (2,3,10,11)...
Categories: DBA Blogs

Strangler Pattern For Linked Oracle DB - how could it be done?

Wed, 2017-11-15 15:46
Hi, I'm not a big expert with Oracle databases so please forgive my fumbling description. We're working with a system where there are two databases which are connected using a database link. A -> queries and RPCs -> B There is a need to intr...
Categories: DBA Blogs

Storing a calculated value in a variable at run time without using triggers.

Wed, 2017-11-15 15:46
Is there a way to store a calculated data without the use of triggers? With some input data, calculations will be done and a variable will store the output data. When the input data is modified, the variable will have another calculated data. Is ther...
Categories: DBA Blogs

Is Using ROWTYPE is better then fetching 75% columns values to different variables

Wed, 2017-11-15 15:46
Hi Tom, I am using multiple variables to get columns values from a table(obviously I am using INTO clause and getting one row with filter criteria). I can do this by using %ROWTYPE as well. This way it will fetch all of the columns. Now, pro...
Categories: DBA Blogs

Virtual Private Database

Wed, 2017-11-15 15:46
Hello Tom, I know you can use VPD to restrict data horizontally - that is restrict rows based on where clauses. However, is there a way to automatically hide columns from users using VPD? Let's say there is a table called MBR(MBR_ID NUMBER, LNAM...
Categories: DBA Blogs

merge the cursor output of multiple stored procedures into one result

Wed, 2017-11-15 15:46
Hello Tom, Can you please tell me how can i merge the output of 6 stored procedures into one result. The output of each stored procedure is a cursor which holds n number of records(records data structure is same). I have to merge the data and ...
Categories: DBA Blogs

Pages