Tom Kyte

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

Query the table in Oracle 12c In-Memory still have physical reads

13 hours 45 min ago
1. I cached a table in Oracle12C inmemory as following. <code>SQL> SELECT A.SEGMENT_NAME, 2 SUM(A.INMEMORY_SIZE) / 1024 / 1024 / 1024, 3 SUM(BYTES) / 1024 / 0124 / 1024, 4 SUM(A.BYTES_NOT_POPULATED) 5 FROM V$...
Categories: DBA Blogs

Salary increment by using cursor

13 hours 45 min ago
<code>declare i number; j varchar2(30); cursor raise is select name, salary from emp; begin open raise; loop fetch raise into j,i; i :=i+15; dbms_output.put_line(i||' '||j); exit when raise%notfound; end loop; end; /</code> I am try...
Categories: DBA Blogs

Filter in Interactive Report with morethen 1000 values against one field

13 hours 45 min ago
Hello, Is there any way I can apply a filter with more than 1000 values i.e. 5000 in single filed while working on the interactive report. I just want to copy paste my values separated by ','
Categories: DBA Blogs

How to read a value from the user at run time?

Mon, 2018-10-15 14:46
<b></b>Hey! I am a beginner. I was watching YouTube tutorials on Oracle PL/SQL and I came across this feature that uses the placeholder to assign a value to a variable by reading it from the user at the run time. I tried my best to use the given...
Categories: DBA Blogs

Is there way to convert user defined type to JSON?

Mon, 2018-10-15 14:46
Is there way to convert user defined types/collections to JSON (the same way as it's possible to convert them to XML via XMLTYPE)?
Categories: DBA Blogs

How does a global index behave on partitioned table

Mon, 2018-10-15 14:46
Suppose i have below table which has 1 billion record and partitioned based on Budget_Flag <code> CREATE TABLE xx_tab1 (budget_flag varchar(1), period_name VARCHAR2(10), begin_dr number, begin_cr number, creation_date DATE) PARTITI...
Categories: DBA Blogs

regarding exchange table partition

Mon, 2018-10-15 14:46
hi Team, currently, we are doing partition activity , in brief about the activity is we are using exchange partition with other table. While performing exchange we are facing issue of ORA-14097 : column type or size mismatch in ALTER TABLE EXC...
Categories: DBA Blogs

Query or report all runs of a scheduled job

Mon, 2018-10-15 14:46
It's possible someone has already written such a query, but how could I write a select statement that would report on all the expected times a scheduled job is going to run? So, for example, if I have 2 DBMS_SCHEDULED jobs: job_a runs at specifi...
Categories: DBA Blogs

Standby Database for Reporting Purpose

Mon, 2018-10-15 14:46
Hi We are using 10.2.0.1.0 database and we intend to configure a standby database for reporting purpose. Could you guide us as to how can we achieve the above. Regards Abdul Wahid
Categories: DBA Blogs

Enabling TKPROF/TRACE on certain queries

Mon, 2018-10-15 14:46
Hey, Is there a way to enable TKPROF / TRACE on certain queries only. I have application with hundreds of queries. When we do load test using appdynamics(profiling tool) it shows certain simple queries like insert/update too slow (43 seconds,53 seco...
Categories: DBA Blogs

How can I get characters before and after '-OO' character in a sentence?

Mon, 2018-10-15 14:46
Hi Tom, I have a string like '000-O 1000 DENIS GRATTON TRANSPORT LTD-OO Profile Class-PHONE-25183', can I use oracle regular expression to get all characters before '-OO'? I am using Oracle 12c release 1 Kind Regards, Derek
Categories: DBA Blogs

Query on system catalog is very slow

Sat, 2018-10-13 07:46
Hi I have a query in my application: SELECT NULL, p.owner, p.table_name, pc.column_name, NULL, f.owner, f.table_name, fc.column_name, fc.position, NULL, DECODE( f.delete_rule, 'CASCADE', 0, 'SET N...
Categories: DBA Blogs

Index creation on GTT

Sat, 2018-10-13 07:46
Hello, i want session specific data for my current requirement. i am doing some calculations so i need that data only. at a time multiple users can use screen and do operations so i am using GTT. i have created GTT and created index on that GTT. ...
Categories: DBA Blogs

filter and access in execution plan

Fri, 2018-10-12 13:26
<code> Hi,Tom I have a question and want to learn from you. the following is an execution plan from OTN,but I don't understand the difference between "filter" and "access" listed in "Predicate Information" and I did not find any OTN documents...
Categories: DBA Blogs

How to fetch one column from a sys_refcursor?

Fri, 2018-10-12 13:26
How do I get only few columns from a sys_refcursor and pass it as an input to another proc? I have a proc which returns the result set in a sys_refcursor. A column from this output has to be passed as an input to another proc. For eg: proc_a...
Categories: DBA Blogs

Procedure is taking too much time to execute

Fri, 2018-10-12 13:26
Hi, We have created procedure with below logic and it's taking too much time to execute, the main logic we've used as below, procedure: create or replace procedure test_parts ( P_QUOTE_NUMBER IN table_c.QUOTE_NUMBER%TYPE, ...
Categories: DBA Blogs

How to grant entire Schema access?

Thu, 2018-10-11 19:06
Hello!! I did some research, but I still need help, please. I have eight schemas: A, B, C, E, F, G and H. I need to give schema A, full access (select, insert, update, delete) to schema B, C, D, E, F, G and H (all objects: TABLE, VIEW, PACKA...
Categories: DBA Blogs

DBMS_SCHEDULER: Emailing after inserting into database tables

Thu, 2018-10-11 19:06
Hello Tom, You provided an example for "Emailing after inserting into database tables."; I?m working with 10g. 10g documentation recommends using DBMS_SCHEDULER. The code you provided using DBMS_JOB is listed below. Would be possible to prov...
Categories: DBA Blogs

Why does the optimizer in Oracle Database does not use an index for create-table-as-select?

Thu, 2018-10-11 19:06
hi when I execute following queries see completely different result where is problem? here I execute a simple select query which takes a few seconds to complete: <code> SELECT * FROM PRODUCTION.VERY_SMALL_TABLE L INNER JOIN PR...
Categories: DBA Blogs

procedure having out parameter of clob type, how we will see the value of it?

Thu, 2018-10-11 00:46
procedure having out parameter of clob type, how we will see the value of it,please advise? Hello Tom, we are having one procedure as below: PROCEDURE P1 ( p_dat_file_name out varchar2, p_zip_file_name out varchar2, p_metad...
Categories: DBA Blogs

Pages