DBA Blogs

same explain plan but once in a while query is taking unusual time

Tom Kyte - Thu, 2017-03-23 21:26
Hi, In my Application, one query with fixed filter columns and dynamic values, performs in msecs most of the time but once in a while the query is taking up to 1 min. By using SQL ID I was able to get explain plan, which is same for long runni...
Categories: DBA Blogs

Error on table PEGANBAM_EXTERNAL.self_service_id, ELISA_ID. Field in data file exceeds maximum length

Tom Kyte - Thu, 2017-03-23 21:26
Error is : Record 4: Rejected - Error on table SELF_SERVICE_ID_B, column "SSN". ORA-01400: cannot insert NULL into ("PEGANBAM_EXTERNAL"."SELF_SERVICE_ID_B"."SSN") Here is my control file below: OPTIONS ( DIRECT = FALSE , PARALLEL ...
Categories: DBA Blogs

Large Number of buffer Gets

Tom Kyte - Thu, 2017-03-23 21:26
Hi Team, Not able to understand few thing while running SQL. I am running SQL,in which there is full scan table with around 7 million records. and if I check gv$sql,it has high number if IO. almost around 100 millions. My question is: 1. ...
Categories: DBA Blogs

Exchange range-hash partition to another range-hash partition table.

Tom Kyte - Thu, 2017-03-23 21:26
Hi, I want to do partition exchange from Table A to Table B. Following is one test case, create table RANGE_HASH_TEST ( TRN_DT DATE, SEQ_NO NUMBER, REF_NO VARCHAR2(26) ) PARTITION BY RANGE (TR...
Categories: DBA Blogs

Need an SQL script to insert multiple bind variables all at a time into the "Select statement at where clause".

Tom Kyte - Thu, 2017-03-23 21:26
Hi Tom, Good Morning, This is Venkatesh and I am from India,I need an SQL script to insert multiple bind variables all at a time into the "Select statement at where clause". For eg: Select * from Tab where col in ('val1','val2','val3'.......
Categories: DBA Blogs

Times Ten

Tom Kyte - Thu, 2017-03-23 21:26
Hi, Please answer my below questions 1.What is Time ten SQL? 2.whats the difference between Time Ten SQL and Pl/SQL?
Categories: DBA Blogs

sqlnet parameter

Tom Kyte - Thu, 2017-03-23 21:26
what is different between SQLNET.EXPIRE_TIME and SQLNET.INBOUND_CONNECT_TIMEOUT in easy meaning? if How can I simulate above both parameter?
Categories: DBA Blogs

Partner Webcast – Cloud@Customer: Partner opportunities with Oracle Cloud Machines

Customers have a clear understanding of the value Cloud can bring to their business. They see the potential for speeding new applications to market.  They want the agility and flexibility that...

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

Index on Column having special characters

Tom Kyte - Thu, 2017-03-23 03:06
HI Team, Consider below table structure - tb_contact_details ------------------ sequenceno mobileno request_date ---------- -------- ------------ 100001 +9198989 11/12/2001 100002 +9198989 11/12/2002 100003 +9191258 11/12/200...
Categories: DBA Blogs

Archival strategy from huge data tables

Tom Kyte - Thu, 2017-03-23 03:06
Hi Tom, We have a requirement to archive data from huge data into some backup tables based upon a date range or number of rows. As per the stats that i extracted these tables contains data around 275 MM records. Can you please let me know the b...
Categories: DBA Blogs

Loading two files with one CTL having different column

Tom Kyte - Thu, 2017-03-23 03:06
Hello Tom, Good Morning. I am in need to load two csv files with same CTL through SQLLoader. File f1 has 3 columns and ctl loads them in corresponding table columns. But file f2 has 2 columns only(last column of f1 is not present). ...
Categories: DBA Blogs

How to reduce the size of a tablespace?

Tom Kyte - Thu, 2017-03-23 03:06
today I could purge a "table space" that apparently occupied 70 gb, but actually used 3 gb, therefore now that "table space" measures 70 gb with only 3 gb occupied, then I want to minimize the "table space" at least 5 gb, but not let me make the oper...
Categories: DBA Blogs

Using Delphix to support Oracle upgrade

Bobby Durrett's DBA Blog - Wed, 2017-03-22 17:47

I’m working on upgrading a very buggy unpatched 11.1.0.7 Oracle database to a fully patched 11.2.0.4 version. I’m using Delphix to support the upgrade and it has been a big help so far. This is on the HP-UX 11.31 Itanium platform.

The great thing about using Delphix to support an upgrade is that my very first pass through the upgrade scripts was with a full-sized clone of production. In the past I probably started with a tiny subset or even an out of the box demo database for my first upgrade pass and even when I got to QA it wasn’t a full test of a production upgrade. This time, my first test was with all the data and that was very cool.

The main example of how this helped is that we had a lot of data in the SYS.WRI$_OPTSTAT_HISTGRM_HISTORY and SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY tables in production and this made the first upgrade of its clone take a long time. After two or three attempts at other ways to speed things up, I ended up applying patch 12683802 on an 11.1.0.7 Oracle home and this allowed me to truncate these two tables.

Delphix helped me here because I had an unused 11.1.0.7 Oracle home on a different host from the one I was doing the upgrade on. I didn’t want to apply a patch on the upgrade host because there were three other databases using the home and I didn’t want to bring them down or patch them. Delphix let me move the VDB that I was upgrading over to the host that had the unused home. Then I applied the patch there and ran the truncate using the dbms_stats.purge_stats(dbms_stats.purge_all) procedure call that the patch enabled.

Then I moved the VDB back to the host where I intended to do the upgrade, which already had the fully patched 11.2.0.4 binaries installed, and ran the upgrade there. Pretty cool. I think I did the most recent upgrade in about 3.5 hours with the empty OPTSTAT tables.

By the way, doing an upgrade within Delphix is easy. You just bring the VDB up on the old oracle home, do the upgrade as you normally would include all the shutdown and startup commands, and then within the Delphix GUI you let Delphix know the VDB is now on a new Oracle home by shutting it down, choosing the new home, and bringing it back up. Piece of cake.

Bobby

Categories: DBA Blogs

Partitioning in 11g/12c

Tom Kyte - Wed, 2017-03-22 08:46
Hi Chirs/Connor, We have 10 tables (Size around 510 GigaBytes). We need to convert these non-partitioned table to Partition based on certain columns we identifed. Currently we are running on Oracle 11g and there is no direct option available t...
Categories: DBA Blogs

Using a view with table/cast/multiset within a cte utilizing a union statement returns null

Tom Kyte - Wed, 2017-03-22 08:46
Trying to utilize TABLE/CAST/MULTISET in Ora11 to duplicate some functionality from converted SQL using outer apply. Since Oracle doesn't have that in version 11 I defined an object and table of said object, cross join it and basically get the same t...
Categories: DBA Blogs

Explain plan cardinality and cost

Tom Kyte - Wed, 2017-03-22 08:46
Hi, I checked EXPLAIN PLAN of a Select Query(In SQL Devloper Tool by pressing F10 Short cut Key), there i noticed one column Cardinality with COST. As Cardinality Increases i noticed increase in COST, i read Optimizer chooses less cost plan to ...
Categories: DBA Blogs

Full table scan with sorting.

Tom Kyte - Wed, 2017-03-22 08:46
Dear Team, I have one analytic question, please help me to understand this. Suppose I have SGA_TARGET=5 GB DB_CACHE_SIZE= 2 GB PGA_SIZE= 2 GB TABLE SIZE= 15 GB (e.g. table_name employee) If I fire command like "select * from employee or...
Categories: DBA Blogs

rac undo tablespace

Tom Kyte - Wed, 2017-03-22 08:46
Hi , Why we need separate undo and temp table space in rac and please justify
Categories: DBA Blogs

Merging Partitions Of Two Different Tables

Tom Kyte - Wed, 2017-03-22 08:46
Hi, Suppose i have a table A in which there is a varchar2(200) and a DATE column. I have created a range partition on the date column.I have another table B in same table space and in same database with similar structure. Is there any way by which...
Categories: DBA Blogs

test question ignore

Tom Kyte - Wed, 2017-03-22 08:46
test question ignore
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs