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
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
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,
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 102, high_snap_id => 122, dbid => 8187786345);
/ Читать далее How to create AWR report manually
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
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: