Sys-Admin Information’s

Share Knowledge, Distribute Knowledge, Capture & Modify Knowledge, Create Knowledge

Archive for the ‘Export-Import’ Category

Export Script (Oracle)

without comments

Yesterday, I was export the database for backup mode, Coz the database not in archived log mode, so export was the solved. I used this in HP-UX
Note:
1. Change ‘TABLE’ with your table and ‘SEPT’ with your partition table name.
2. Change ‘SCHEMA’ with your shema where table place.
3. Change ‘compress’ with your ‘compress’ tool, like (g)un(zip), etc.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

DECLARE
   vname LONG := NULL;
   vtable_name VARCHAR2 (50) := 'TABLE';
BEGIN
   FOR i IN (SELECT table_name, partition_name
               FROM dba_tab_partitions
              WHERE table_owner NOT IN ('SYS', 'SYSTEM')
                AND table_name = vtable_name
                AND partition_name NOT LIKE '%06%'
		AND partition_name LIKE '%SEPT%')
   LOOP
      vname :=
            'exp "''/ as sysdba''" buffer=50000000 file='
         || vtable_name
         || '-'
         || i.partition_name
         || ' tables=SCHEMA.'
         || vtable_name
         || ':'
         || i.partition_name
         || ' statistics=none log='
         || vtable_name
         || '-'
         || i.partition_name
         || '.log
compress -v '
         || vtable_name
         || '-'
         || i.partition_name
         || '.dmp';
      DBMS_OUTPUT.PUT_LINE (vname);
   END LOOP;
END;

Written by Hendrawan

September 12th, 2007 at 4:06 pm

Posted in Export-Import, Oracle, Scripting