Tom Kyte

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

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

12c attribute clustering vs old index cluster

Wed, 2018-10-10 06:26
Hi, The attribute clustering works only for the direct path inserts? Will it support the normal inserts? If not whats the way to have the data in table stored in an ordered way especially for normal inserts (not direct path). Can you explain ...
Categories: DBA Blogs

SPACE GROWTH procedure for data volume calcualation for datawarehouse

Wed, 2018-10-10 06:26
Hi, I am currently working on the data sizing for each table to be loaded in a datawarehouse. Ref: https://docs.oracle.com/cd/E11882_01/doc.112/e26211/app_size.htm#DMAOG301 I have seen a procedure from https://docs.oracle.com/database/121/AR...
Categories: DBA Blogs

OBJECT_GROWTH_TREND function

Wed, 2018-10-10 06:26
Hi, I am trying to use the script below to analyze the data consumed for a table since I am planning the storage capacity for a datawarehouse. Using this value(for every table), I will then be able to plan data for 18 months. I am writing a scrip...
Categories: DBA Blogs

Generate formatted excel and pdf report in apex

Wed, 2018-10-10 06:26
Please tell us the options for generating the formatted excel and pdf reports in apex. It is helpful if opensource option.
Categories: DBA Blogs

difference with blocks from DBA_SEGMENTS and Blocks From TSQ$

Wed, 2018-10-10 06:26
I have a schema and I look for the size by using the following <code> select segment_type,sum(blocks) from dba_segments where owner='W_GBA_000' group by segment_type; </code> SEGMENT_TYPE SUM(BLOCKS) ------------------ ----------- LOBIN...
Categories: DBA Blogs

How does insert and delete use space regarding to indexes?

Wed, 2018-10-10 06:26
Hello Tom I have such as test: SQL> create table t as select * from dba_objects; Table created. SQL> desc t Name OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP ...
Categories: DBA Blogs

Gather Index Stats ve Rebuild Index

Wed, 2018-10-10 06:26
Hi Team, We encountered production issue related 100% CPU usage. New script introduced that was rebuilding indexes on table having size 450GB with parallel degree 8. I read one arrival on asktom where they explained rebuild recreates the Ind...
Categories: DBA Blogs

Generate Link dymanicly

Sun, 2018-10-07 23:26
i have to call oracle report rdf,rep from apex, i have some fields like DATE_FROM and DATE_TO in apex form. i want to generate url dynamically based on data from date_from and date_to fields by pressing button. i have used window.open('') in dynami...
Categories: DBA Blogs

wait event - PGA memory operation

Sun, 2018-10-07 23:26
Trans 1) One temporary table which hold approx 45000 rows fill using cursor. Trans 2) Now that temporary table update with query which also call using cursor So when Trans 1 is call at that time following wait event fine "PGA memory operation...
Categories: DBA Blogs

Tablespace sizing for datawarehouse

Sun, 2018-10-07 23:26
Hello Team, my question is a bit similar to https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:228413960506 but I will need further clarification. I am loading data on monthly partitioned tables from flat files using sqlloader ev...
Categories: DBA Blogs

Locally partitioned index rebuild issues

Sun, 2018-10-07 23:26
Hi, I have a huge partitioned table with 1 Billion rows, for some reason we dropped the index and were re creating the index when due to a Support issue we found out that we had duplicate rows. So we created the index in disabled mode and then ...
Categories: DBA Blogs

Pages