Oracle Database Discussions

due to a problem with tablespace datapumps hanging I'm resorting to doing a full export and exclude certain schemas. Our instances contain customer data in thier own tablespaces that we do not back up so I cant do a full "proper" backup. All the datawanting to be backed up are in tablespaces begining with CODA everything else gets excluded.

I have a shell script that calls expdp and it works, but I want a PL/SQL block that I can execute form Enterprise manager.

But I'm getting an error....

16:22:15 SQL> @new_backup
16:22:18 SQL> DECLARE
16:22:18 2 h1 NUMBER;
16:22:18 3 more_than_1 BOOLEAN;
16:22:18 4 exclude_list VARCHAR2(255);
16:22:18 5 exclude_user VARCHAR2(30);
16:22:18 6 CURSOR c_exclude IS select username from dba_users where default_tablespace not like 'CODA_%';
16:22:18 7 BEGIN
16:22:18 8 OPEN c_exclude;
16:22:18 9 LOOP
16:22:18 10 FETCH c_exclude INTO exclude_user;
16:22:18 11 EXIT WHEN c_exclude%NOTFOUND;
16:22:18 12 IF more_than_1 = TRUE THEN
16:22:18 13 exclude_list := exclude_list || ',' || chr(39) || exclude_user || chr(39);
16:22:18 14 ELSE
16:22:18 15 exclude_list := chr(39) || exclude_user || chr(39);
16:22:18 16 more_than_1 := true;
16:22:18 17 END IF;
16:22:18 18 END LOOP;
16:22:18 19 CLOSE c_exclude;
16:22:18 20 exclude_list := 'IN (' || exclude_list || ')';
16:22:18 21 DBMS_OUTPUT.PUT_LINE(exclude_list);
16:22:18 22
16:22:18 23 h1 := dbms_datapump.open('EXPORT','FULL',null,'EXPORT_CODA');
16:22:18 24 dbms_datapump.set_parallel(h1,1);
16:22:18 25 dbms_datapump.add_file(h1,'coda.expdp','EXPDP_ORA101',null,DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
16:22:18 26 dbms_datapump.add_file(h1,'coda.log', 'EXPDP_ORA101',null,DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
16:22:18 27 dbms_datapump.metadata_filter(h1,'EXCLUDE_PATH_EXPR',exclude_list,'SCHEMA');
16:22:18 28 dbms_datapump.start_job(h1);
16:22:18 29 dbms_datapump.detach(h1);
16:22:18 30
16:22:18 31 END;
16:22:18 32 /
IN ('SYS','SYSTEM','OUTLN','DIP','DBSNMP','WMSYS','CODA_OEM','DRKW','PERFSTAT','ALLIANCE_PROC','ALLIANCE','ALLIANCE_101','ALLIANCE_P
ROC_101','ECGD','HOYE0252','WELT0257_WF','WELT0257_PROC','WELT0257_FIN')
DECLARE
*
ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2486
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3417
ORA-06512: at line 27

the in (<usernames>) at line 20 is because lists are not supported at 10.1.0.5 only in 10.2 and we're running 10.1.05 on this instance

This is the par file that works for expdp

DIRECTORY=EXPDP_ORA101
DUMPFILE=backup_oracle1.expdp,backup_oracle2
FULL=Y
LOGFILE=backup_oracle.log
PARALLEL=2
EXCLUDE=SCHEMA:"IN (
'SYS',
'SYSTEM',
'OUTLN',
'DIP',
'DBSNMP',
'WMSYS',
'CODA_OEM',
'DRKW',
'PERFSTAT',
'ALLIANCE_PROC',
'ALLIANCE',
'ALLIANCE_101',
'ALLIANCE_PROC_101',
'ECGD',
'HOYE0252',
'WELT0257_WF',
'WELT0257_PROC',
'WELT0257_FIN')"