DBA Blogs

Query on system catalog is very slow

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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?

Tom Kyte - 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

Tom Kyte - 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?

Tom Kyte - 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

Tom Kyte - 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?

Tom Kyte - 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?

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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$

Tom Kyte - 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?

Tom Kyte - 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

Tom Kyte - 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

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part II (Aladdin Sane)

Richard Foote - Tue, 2018-10-09 01:16
In Part I of this series, I highlighted how a Non-Partitioned Global Index on a Partitioned Table is able to effectively perform “Partition Pruning” by reading only the associated index entries to access just the table blocks of interest from relevant table partitions when the table partitioned keys are specified in an SQL Predicate. Understanding […]
Categories: DBA Blogs

Generate Link dymanicly

Tom Kyte - 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

Tom Kyte - 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

Tom Kyte - 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs