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

Oracle Cloud Jump Start With Oracle Cloud Infrastructure

Oracle Cloud Infrastructure is the cloud for your most demanding workloads. It combines the elasticity and utility of public cloud with the granular control, security, and predictability of...

We share our skills to maximize your revenue!
Categories: DBA Blogs

The identity column jumps its value if using merge into statement

Tom Kyte - Thu, 2018-10-04 22:06
Hi, I have one table defined as below, one of the column is defined as identity type <code> create table TEST ( col1 VARCHAR2(10), col2 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 999999999999999...
Categories: DBA Blogs

dbms_lob.compare and length

Tom Kyte - Thu, 2018-10-04 22:06
Hello, I'm trying within a trigger to compare two clobs to see if there is any change. I am trying to prevent any unnecessary writes. Prior to writing to audit trail I compare two values. <code> v_clob_compare := dbms_lob.compare( :old.clob_tex...
Categories: DBA Blogs

Using function in conjunction with WITH query clause

Tom Kyte - Thu, 2018-10-04 22:06
Bit of a newbie, and hoping I can get pointed in the right direction. I've simplified things to demonstrate the issue I'm experiencing (and I'm really struggling to get a clear answer on other posts). When running the following: <code>with f...
Categories: DBA Blogs

SQL Query to Convert Ten Rows with One Column to Five Rows With One Column

Tom Kyte - Thu, 2018-10-04 22:06
i have a table with column name as value. There are 10 rows in the table. The desired output of this to be displayed as two columns first 5 rows as one column A and rows 6 to 10 as column B , next to each other as 5 rows of data like this <code>A B...
Categories: DBA Blogs

Calculate a variable date value and use it in a where clause to return all rows after that date

Tom Kyte - Thu, 2018-10-04 22:06
Long time SQL user of many flavors but brand new to PL/SQL and struggling to learn the "Oracle way". I've seen MANY examples of using variables in queries online and in documentation, but I've been unsuccessful finding a sample of what I want to do ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs