Oracle data acquisition tablespace creation script

Time:2020-2-18

The scenario of this script is to create a corresponding table space on the target database when using datapump for data migration. In this case, for systems with a large number of table spaces, such as PeopleSoft, it will be more difficult to create a table space by hand.

The following script runs on the source database, gets the creation script of the tablespace, and then only needs to modify the path to use it.

Tablespace creation script

------------------------
--Get tablespace generation script--
------------------------

DECLARE
  CURSOR c_dbf IS
    SELECT tablespace_name,
           file_name,
           bytes,
           maxbytes,
           increment_by,
           AUTOEXTENSIBLE
      FROM dba_data_files
     where tablespace_name not in
           ('SYSTEM', 'SYSAUX', 'USERS','UNDOTBS1')
     ORDER BY tablespace_name;
  v_tbname_temp DBA_DATA_FILES.TABLESPACE_NAME%type;
  v_tbname      DBA_DATA_FILES.TABLESPACE_NAME%type;
  v_dbfname     DBA_DATA_FILES.FILE_NAME%type;
  v_bytes       dba_data_files.bytes%type;
  v_maxbytes    dba_data_files.maxbytes%type;
  v_inc         dba_data_files.increment_by%type;
  v_autoext     dba_data_files.AUTOEXTENSIBLE%type;
BEGIN
  OPEN c_dbf;
  FETCH c_dbf
    INTO v_tbname, v_dbfname, v_bytes, v_maxbytes, v_inc, v_autoext;
  --  DBMS_OUTPUT.PUT_LINE('create tablespace '|| v_tbname || ' datafile '||chr(39)||v_dbfname ||chr(39)|| ' size '||v_bytes ||'  autoextend on '  ||' next '||v_inc||' maxsize ' ||v_maxbytes||';' );
  v_tbname_temp := 'a';
  WHILE c_dbf%FOUND LOOP
    IF v_autoext = 'YES' THEN
      IF v_tbname_temp <> v_tbname THEN
        DBMS_OUTPUT.PUT_LINE('create tablespace ' || v_tbname ||
                             ' datafile ' || chr(39) || v_dbfname ||
                             chr(39) || ' size ' || v_bytes ||
                             '  autoextend on ' || ' next ' || v_inc ||
                             ' maxsize ' || v_maxbytes || ';');
      ELSE
        DBMS_OUTPUT.PUT_LINE('alter tablespace ' || v_tbname ||
                             ' add datafile ' || chr(39) || v_dbfname ||
                             chr(39) || ' size ' || v_bytes ||
                             '  autoextend on next ' || v_inc ||
                             ' maxsize ' || v_maxbytes || ';');
      END IF;
    ELSE
      IF v_tbname_temp <> v_tbname THEN
        DBMS_OUTPUT.PUT_LINE('create tablespace ' || v_tbname ||
                             ' datafile ' || chr(39) || v_dbfname ||
                             chr(39) || ' size ' || v_bytes || ';');
      ELSE
        DBMS_OUTPUT.PUT_LINE('alter tablespace ' || v_tbname ||
                             ' add datafile ' || chr(39) || v_dbfname ||
                             chr(39) || ' size ' || v_bytes ||
                             '  autoextend on next ' || v_inc ||
                             ' maxsize ' || v_maxbytes || ';');
      END IF;
    END IF;
  
    v_tbname_temp := v_tbname;
    FETCH c_dbf
      INTO v_tbname, v_dbfname, v_bytes, v_maxbytes, v_inc, v_autoext;
  END LOOP;
  CLOSE c_dbf;
END;

After running, you can see the tablespace creation script in DBMS Ou output.

Temporary tablespace creation script

------------------------
--Get temporary tablespace generation script--
------------------------

DECLARE
  CURSOR c_dbf IS
    SELECT tablespace_name,
           file_name,
           bytes,
           maxbytes,
           increment_by,
           autoextensible
      FROM dba_temp_files
     ORDER BY tablespace_name;
  v_tbname_temp dba_temp_files.TABLESPACE_NAME%type;
  v_tbname      dba_temp_files.TABLESPACE_NAME%type;
  v_dbfname     dba_temp_files.file_name%type;
  v_bytes       dba_temp_files.bytes%type;
  v_maxbytes    dba_temp_files.maxbytes%type;
  v_inc         dba_temp_files.increment_by%type;
  v_autoext     dba_temp_files.AUTOEXTENSIBLE%type;
BEGIN
  OPEN c_dbf;
  FETCH c_dbf
    INTO v_tbname, v_dbfname, v_bytes, v_maxbytes, v_inc, v_autoext;
  --  DBMS_OUTPUT.PUT_LINE('create temporary tablespace '|| v_tbname || ' tempfile '||chr(39)||v_dbfname ||chr(39)|| ' size '||v_bytes ||'  autoextend on '  ||' next '||v_inc||' maxsize ' ||v_maxbytes||';' );
  v_tbname_temp := 'a';
  WHILE c_dbf%FOUND LOOP
    IF v_autoext = 'YES' THEN
      IF v_tbname_temp <> v_tbname THEN
        DBMS_OUTPUT.PUT_LINE('create temporary tablespace ' || v_tbname ||
                             ' tempfile ' || chr(39) || v_dbfname ||
                             chr(39) || ' size ' || v_bytes ||
                             '  autoextend on ' || ' next ' || v_inc ||
                             ' maxsize ' || v_maxbytes || ';');
      ELSE
        DBMS_OUTPUT.PUT_LINE('alter tablespace ' || v_tbname ||
                             ' add tempfile ' || chr(39) || v_dbfname ||
                             chr(39) || ' size ' || v_bytes ||
                             '  autoextend on next ' || v_inc ||
                             ' maxsize ' || v_maxbytes || ';');
      END IF;
    ELSE
      IF v_tbname_temp <> v_tbname THEN
        DBMS_OUTPUT.PUT_LINE('create temporary tablespace ' || v_tbname ||
                             ' tempfile ' || chr(39) || v_dbfname ||
                             chr(39) || ' size ' || v_bytes || ';');
      ELSE
        DBMS_OUTPUT.PUT_LINE('alter tablespace ' || v_tbname ||
                             ' add tempfile ' || chr(39) || v_dbfname ||
                             chr(39) || ' size ' || v_bytes || ';');
      END IF;
    
    END IF;
    v_tbname_temp := v_tbname;
    FETCH c_dbf
      INTO v_tbname, v_dbfname, v_bytes, v_maxbytes, v_inc, v_autoext;
  END LOOP;
  CLOSE c_dbf;
END;

After running, you can see the temporary tablespace creation script in DBMS ﹣ output.

Recommended Today

Laravel service container must know

The article was forwarded from the professional laravel developer community. Original link: https://learnku.com/laravel/t To learn how to build an application with laravel is not only to learn how to use different classes and components in the framework, but also to remember allartisanCommand or all helper functions (we have Google). Learning to code with laravel is […]