POUWIEL|COM

JeroenPouwiel

Auto-generate your tablespace create statements, so you don’t have to…

November 24, 2016

Comments Off on Auto-generate your tablespace create statements, so you don’t have to…

DECLARE
  var_mod number;
  CURSOR c1 IS
    SELECT tablespace_name, ROUND(SUM(bytes)/(1024*1024),0) AS mb
      FROM dba_data_files
     WHERE tablespace_name IN (SELECT DISTINCT tablespace_name
                                 FROM dba_segments
                                WHERE owner IN ('<schema_name>','<schema_name>','<schema_name>','<schema_name>'))
    GROUP BY tablespace_name
    ORDER BY 1;
BEGIN
   FOR tblspc IN c1
   LOOP
     var_mod := FLOOR(tblspc.mb/9216);
     IF tblspc.mb <= 9216 THEN
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE '||tblspc.mb||'M AUTOEXTEND ON MAXSIZE 9G;');
     ELSIF REMAINDER(tblspc.mb, 9216) = 0 THEN
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       FOR i IN 1..var_mod LOOP
         var_mod := var_mod - 1;
         DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE '||tblspc.tablespace_name||' ADD DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       END LOOP;
     ELSE
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       FOR i IN 1..var_mod LOOP
         var_mod := var_mod - 1;
         DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE '||tblspc.tablespace_name||' ADD DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       END LOOP;
     END IF;
     var_mod :=0;
   END LOOP;
END;
/

This piece of code will gather all used tablespaces by given schema's and output create statements for those tablespaces broken down into separate files of max. 9G.
Use at your own leisure and discretion... As always: No support and no responsibility

Export using DBMS_DATAPUMP and via DB_Link

March 6, 2013

Comments Off on Export using DBMS_DATAPUMP and via DB_Link

Fill/alter in the required parameters and Ctrl-C/Ctrl-V:

DECLARE
  h1   number;
BEGIN
  h1 := dbms_datapump.open(operation => 'IMPORT', job_mode => 'SCHEMA', job_name => '<JOB_NAME__MAKE_THIS_UPPERCASE!!!>', version => 'COMPATIBLE', remote_link=>'<DB_LINK_NAME>');
  dbms_datapump.set_parallel(handle => h1, degree => 1);
  dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
  dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''<SCHEMA_NAME>'')');
  dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'NOT IN(''<EXCLUDED_TABLE01>'',''<EXCLUDED_TABLE02>'',''<EXCLUDED_TABLE03>'')', object_type => 'TABLE');
  dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_TIME', value => 'TO_TIMESTAMP (to_char(sysdate,''dd.mm.yyyy HH24:MI:SS''),''dd.mm.yyyy HH24:MI:SS'')');
  dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
  dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
  dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
END;
/

Categories