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

Locally partitioned index rebuild issues

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

Insert data in a new table

Tom Kyte - Sun, 2018-10-07 23:26
I have a question table having 1 record. create table q_text_t (q_id number,q_text varchar2(100)); insert into q_text_t values(1,'What is the capital of India?'); I have another answer table having 4 answers to the corresponding question. creat...
Categories: DBA Blogs

Minimum of a Date column

Tom Kyte - Sun, 2018-10-07 05:06
Hello Tom, I am working on a current project wherein the requirement to calculate a certain column in a certain table is as under. The base table is this: <code> create table main_data (from_value varchar2(10), to_value varchar2(10), act...
Categories: DBA Blogs

Compound Trigger and Global Variables

Tom Kyte - Sun, 2018-10-07 05:06
Hi! To avoid mutating exception, i'm using a compound trigger, filling a array and then i intend to loop through this array e do my thing. The problem is that the global variable loses it contents when I enter "after statement" if (and only if)...
Categories: DBA Blogs

Inserting with WITH FUNCTION Select is giving error

Tom Kyte - Sat, 2018-10-06 10:46
Hi Ask Tom Team, Below select query is working fine for me, but am not able to insert the result using insert statement., Please provide me some suggestions <code>WITH FUNCTION T11 (P_A1 VARCHAR2) RETURN NUMBER IS BEGIN IF P_A1 = ...
Categories: DBA Blogs

Is the Oracle regular expression supporting this character extraction?

Tom Kyte - Sat, 2018-10-06 10:46
Hi Oracle SQL experts, I am using Oracle regular expression to deal with some characters. My db is 12c. I have a string every line like this input like this 1234adhefd#123 345bheufs15# ... output will be from the first alpha lette...
Categories: DBA Blogs

SGA Management

Tom Kyte - Sat, 2018-10-06 10:46
Hi Tom, I would like to know that, how Oracle internally manages when end user try to extract the data which is more than SGA, for Example if our SGA is 7 GB and user query is about 20 GB data, how it will internally manages, as far as I know, ser...
Categories: DBA Blogs

Moving datafile

Tom Kyte - Sat, 2018-10-06 10:46
Hi there in our production database,we have a tablespace called TESTDB and this tablespace has 2 DATAFILEs. location for these 2 datafiles are D:\ORADATA\TESTDB\TESTDB01.DBF D:\ORADATA\TESTDB\TESTDB02.DBF Recently i have added a new datafi...
Categories: DBA Blogs

How to move contents data from one datafile to another?

Tom Kyte - Sat, 2018-10-06 10:46
Hi Tom, Thanks for your asktom website. A tablespace consists of several data files. My purpose is to cleanly move contents in one data file to another on and then I can drop that empty file from the tablespace. So I can reduce the data file nu...
Categories: DBA Blogs

Lock wait timeout

Tom Kyte - Fri, 2018-10-05 16:26
How to set lock wait timeout in Oracle. We are executing insert/update/delete from java applications. Sometimes due to long running transactions or slowness lock acquired by one transaction on particular row gets hit by another transaction and blocki...
Categories: DBA Blogs

More archives during impdp, what is the exact reason and what will happen internally

Tom Kyte - Fri, 2018-10-05 16:26
Why more archives will be generated during import (impdp) activity, i would like to know what will happen internally due to which more archives being generated. Is it only for import activity or it will generate more redo for export as well ? Many...
Categories: DBA Blogs

Query on dba_hist_active_sess_history query taking too long

Tom Kyte - Fri, 2018-10-05 16:26
Hi, I'm using the below query to fetch details from dba_hist_active_sess_history which matches a specific wait event occurring at a specific hour of the day within the last 90 days: select USER_ID, PROGRAM, MACHINE from dba_hist_active_sess_his...
Categories: DBA Blogs

Excessive archive log generation during data load

Tom Kyte - Fri, 2018-10-05 16:26
Hi Tom, I am encountering a situation related to data loading and excessive archive log generation. I am using Oracle 8.1.6 under Solaris 7. I insert twice a week about 1 million rows in to a table whose columns are of number datatype. The loa...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs