1

I have inherited a legacy OpenVMS system with an Oracle RDB database. Recently due to the backup size becoming stupidly large we have archived some of the older data out of the production database. Now we need to shrink the actual database files but are not sure how to go about this.

I have a vague memory of watching someone else unload/export and reload/import data in order to achieve this in the past (sadly he is no longer around to ask).

Ideally what I need is a step by step of how to do this.

MT0
  • 143,790
  • 11
  • 59
  • 117
IKJ
  • 11
  • 2

1 Answers1

1

Basically you will want to do

mc sql$ export database filename ''base' into ''fil_export'; exit

and then, if it has been successful, drop the database

mc sql$ drop database filename ''base';

Now you import it

mc sql$ import database filename ''base' from ''fil_export';

This is explained in detail in the Oracle RDB docs, for example the

Oracle Rdb™ SQL Reference Manual Volume 4

avalable here for example

download.oracle.com/otn_hosted_doc/rdb/pdf/sql_ref_v72_part4.pdf

user2915097
  • 30,758
  • 6
  • 57
  • 59
  • Hi, I'. getting the following when I try to do that. – IKJ Oct 24 '16 at 08:37
  • Hi, I get the foollowing when I try that : SQL> export database filename ''bos_db' into ''bos_unload'; export database filename ''bos_db' into ''bos_unload'; ^ %SQL-W-LOOK_FOR_STT, Syntax error, looking for: %SQL-W-LOOK_FOR_CON, USER, USING, INTO, %SQL-F-LOOK_FOR_FIN, found BOS_DB instead bos_db and bos_unload are logicals pointing to the database file and unload file location respectively. – IKJ Oct 24 '16 at 08:38
  • Well, the various quotes mean in DCL `replace with the value of`, so just remove all quotes, and try again, something like `export database filename bos_db into bos_unload;` this shoudl create a bos_unload.rbf, as .rbf is the default for this type of file – user2915097 Oct 24 '16 at 09:01
  • Thanks, that did the export/import, though it didn't result in the file size actually being reduced, I think I'm missing something along the way here. – IKJ Oct 26 '16 at 10:30
  • you can reduce the size of the snapshot files,`define sql$datatabase bos_db̀` then `mc sql$` and `show storage area` now when no user are on the database, you can shrink the various snapshot files – user2915097 Oct 26 '16 at 14:11
  • Hi, I can see the storage areas, I just can't work out how to shrink them. – IKJ Oct 27 '16 at 07:10
  • something along the lines of `mc sql$` and then `alter storage area my_snapshot_area snapshot allocation 50000 pages;` no `commit;` is required, check the size of the snapshot files before and after, you need no user on the database at that moment – user2915097 Oct 27 '16 at 07:54