Oracle 11g: Copy tables to another database, using Data Pump and bash

Times are tough nowadays. You can’t rely on an only instance of your database and redundancy is a world of a century, probably, but the truth is: you have to have a copy of all your data somewhere, and you need to keep it up to date. Of course there are different solutions like Oracle Streams, Data Guard etcetera, but sometimes you need much more simple though powerful and reliable way to get a copy of your data. For example, you may need an analytic database for reports since you don’t wont let muggles access to your database and you you are not happy with all the high load they treat your precious instance. Then this story is yours.

Well, you happen to have tables inside one Oracle Database which you want to duplicate to another. So this another will be your workplace, and the source database will be the distant one.

Also, your source database has tables in different schemas while your target one has only one, different from these ones, and you want to store these tables within only one this scheme. Moreover, source DB objects placed in different tablespaces but you want to use your ones locally.

The script of the process  will be:

  • Connect to distant DB
  • Export needed table using Data Pump
  • Check if locally exists table with the same name and temporary table for import
  • If local table does not exist, create it structure using definitions from exported data, and copy its structure to temporary table using Data Pump
  • Truncate temporary table
  • Import data from export file to temporary table using Data Pump
  • Merge temporary table with persistent one using field ‘id’ or, if ‘id’ is not exists, given field.
  • And, of course, log everything

The final result will be a script which takes schema and table name as a parameter and does all the other steps on his one. We’ll run it outside cron daemon, and it will create and keep up to date our tables.

So, let’s start! Читать далее Oracle 11g: Copy tables to another database, using Data Pump and bash

How to create AWR report manually

Taken here

Oracle database 10/11g

If you do not have Oracle Enterprise Manager tool installed then you can create the AWR reports manually using the following commands:

1. Creating Snapshot
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

The list of the snapshot Ids along with database Ids is availabe in the view DBA_HIST_SNAPSHOT.

2. Dropping a Range of Snapshots.
Refer to the DBA_HIST_SNAPSHOT view column SNAP_ID to view available snapshots. To delete contain SNAP_ID from from 102 to 122,

BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 102, high_snap_id => 122, dbid => 8187786345);
END;
/ Читать далее How to create AWR report manually

The list of cities between two chosen cities

Bumped into a question:

I have trouble with database design and don’t know how to overcome this. The problem is with list of cities between two chosen city. User chooses start and finish city, and OPTIONALLY, user can write which cities between he will pass, so it can be 0,1,…N. How can I store this in my database ? I already have table with list of States and another table with list of cities.

I thought a bit, and if there is no better option, I will have row in which I will store IDs of cities in format «ID1,ID2,ID3,…» and in software I will separate IDs, but I think this is not normal.

Let’s solve it using oracle:
We’ll use parent-child oriented recursive table,


so if you wish to visit city 4, you have several ways: you may travel

you may travel

or

or

OK, let’s start Читать далее The list of cities between two chosen cities

Shrink, Move, Rebuild, Drop — reclaiming wasted space

So we’d run into lack of space in our server. Some of our tablespaces were too large to their partitions — one was intended to store tables and another one to store indexes. First one was 75Gb (and its storage was 78Gb), and had 26 3Gb datafiles, and the second one was 112Gb (with its storage 114Gb, 38 datafiles).

Our main goals were:

  • Reclaim space on the server — get rid of some datafiles
  • Reclaim space inside the datafiles to let data grow
  • Rebuild indexes to increase performance of the DB

As we couldn’t stop the database to perform all the needed operations (DB works 24×7) at that moment, we started to work like ninjas did (later we pressed to get a free day, however). Читать далее Shrink, Move, Rebuild, Drop — reclaiming wasted space

Oracle Database 11g Release 2 (11.2) Installation On Fedora 17 (F17)

Tnank you, guys!

This article describes the installation of Oracle Database 11g Release 2 (11.2) 64-bit on Fedora 17 (F17) 64-bit (works for 32-bit system too). The article is based on a server installation with a minimum of 2G swap and secure Linux disabled. An example of this type of Linux installation can be seen here. The installation should also include the following package groups: