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

Client IP

Tom Kyte - Wed, 2017-03-22 08:46
How to get client IP from browser from oracle application express.
Categories: DBA Blogs

DBMS_STATS.AUTO_SAMPLE_SIZE seems to always generate 100%

Tom Kyte - Wed, 2017-03-22 08:46
Hello, I was curious about something that I am running in to. The following happens: select dbms_stats.get_param('ESTIMATE_PERCENT') from dual; AUTO_SAMPLE_SIZE select * from dba_tab_statistics where sample_size != num_rows; no rows se...
Categories: DBA Blogs

update as column id with existing records

Tom Kyte - Wed, 2017-03-22 08:46
Hi, I am trying to update as column id in table with 8350 record. There is ID column with scatter id number CREATE TABLE DB_Trac ( T_ID NUMBER(9,0) Not Null, FV_Place NUMBER ) Insert into DB_Trac (T_ID,FV_Place ) values (...
Categories: DBA Blogs

Change value of CONSTANT declaration

Tom Kyte - Mon, 2017-03-20 20:06
Hi, in my code I defined a constant through a custom function that fetches some data from the DB and creates an instance of a custom type. if the return value of the function changes over time, I'm wondering which event trigger a refresh on that ...
Categories: DBA Blogs

Validate all required fields before committing form

Tom Kyte - Mon, 2017-03-20 20:06
Dears, I have a master-detail form in which I want to validate all required fields before committing. When I press Save (Key-Commit), I have a procedure that loops on records in the detail block. If a required field is null in the detail blo...
Categories: DBA Blogs

Table e Index Partitioning

Tom Kyte - Mon, 2017-03-20 20:06
Hi all at ASK, we have a 180 gb table with 340 gb index and we want partioning index and table. Is possible ? There is a year column and i suppose that column is ok for partioning table and index. Which strategy is best ? Thanks in advanc...
Categories: DBA Blogs

REST API from PLSQL

Tom Kyte - Mon, 2017-03-20 20:06
I have one more requirement where I need to change one particular user's password belongs to an application which is hosted outside of our network. External application team provided information about REST API that need to used to search user and ...
Categories: DBA Blogs

Difference between Correlated and Non-Correlated Subqueries

Tom Kyte - Mon, 2017-03-20 20:06
Hi, In Many Website and Question answer communities like Quora etc i read about difference between Non and Co-related Sub queries, the basic difference is Co-relate execute outer query first then sub query Example <code>select * from departmen...
Categories: DBA Blogs

Log Buffer #507: A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2017-03-20 07:58

This Log Buffer edition covers Oracle, SQL Server and MySQL blog posts with a touch of Cloud.

Oracle:

Provisioning EBS on Oracle Cloud: Latest Enhancements

ORA-54002 when trying to create Virtual Column using REGEXP_REPLACE on Oracle 12cR2

Business rules, common sense and query performance

Problem with V$RECOVERY_AREA_USAGE view and FRA space not being reclaimed

Updated SQLcl: 2 New Commands, Bug Fixes, and 12cR2 Oh My!

SQL Server:

Windowing Functions: Tell me when that changes

SQL Server Bulk Insert Row Terminator Issues

Introducing DLM Techniques for a Multi-Database Multi-Server System

Ding – The World’s Largest Mobile Top-up Network Streamlines QA with SQL Server Containers

Enable Trace Flags in SQL Server

MySQL:

Migrating MySQL database from Amazon RDS to DigitalOcean

Monitoring Databases: A Product Comparison

New collations in MySQL 8.0.0

How to store date, time, and timestamps in UTC time zone with JDBC and Hibernate

MySQL Connector/NET 7.0.7 m6 development has been released

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs