pro_gen_add_files_to_temp_ts.sql

Oracle RMAN scripts, windows下Oracle RMAN实现的自动备份脚本.

create table temp_for_pro_gen_ts_sql_temp 
(lineno number, 
ts_name varchar2(30),
text varchar2(800)
);

CREATE OR REPLACE PROCEDURE pro_gen_add_files_to_temp_ts
(       p_v_display_prompt_info VARCHAR2 default 'T')
AS
        CURSOR cur_temp_ts IS SELECT    *
                                        FROM    sys.dba_tablespaces
                                        WHERE tablespace_name != 'SYSTEM'
                                                and status != 'INVALID' AND contents = 'TEMPORARY'
                                        ORDER BY contents, tablespace_name;

        CURSOR cur_df (c_ts VARCHAR2) IS SELECT * 
                                                                        FROM    dba_data_files
                                                                        WHERE   tablespace_name = c_ts
                                                                                and     tablespace_name != 'SYSTEM'
                                                                        ORDER BY file_name;
        CURSOR cur_temp_df (c_ts VARCHAR2) IS SELECT    * 
                                                                        FROM    dba_temp_files
                                                                        WHERE   tablespace_name = c_ts
                                                                        ORDER BY file_name;
        CURSOR cur_users IS select 'ALTER USER '||lower(username)|| 
                                                                ' TEMPORARY TABLESPACE '||lower(TEMPORARY_TABLESPACE)||';' sql_text
                                                from dba_users;
        rec_cur_df                      cur_df%rowtype;
        rec_cur_temp_ts         cur_temp_ts%rowtype;
        rec_cur_temp_df         cur_temp_df%rowtype;
        rec_cur_users           cur_users%rowtype;
        b_first_rec                     BOOLEAN;
        v_string                        VARCHAR2(800);
        n_lineno                        number := 0;
        v_display_prompt_info   VARCHAR2(1);
        n_max_size                      number(38,0);
BEGIN
        v_display_prompt_info := p_v_display_prompt_info;

        delete temp_for_pro_gen_ts_sql_temp; 
        commit;

        OPEN cur_temp_ts;
        LOOP
                FETCH cur_temp_ts INTO rec_cur_temp_ts;
                EXIT WHEN cur_temp_ts%NOTFOUND;

                IF v_display_prompt_info = 'T' THEN
                        IF rec_cur_temp_ts.extent_management = 'DICTIONARY' THEN
                                v_string := '-- ADD DATAFILES TO  TABLESPACE TEMPORARY '||lower(rec_cur_temp_ts.tablespace_name);
                        ELSE
                                v_string := '-- ADD TEMPFILES TO TEMPORARY TABLESPACE '||lower(rec_cur_temp_ts.tablespace_name);
                        END IF;
                        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
                        values (n_lineno, rec_cur_temp_ts.tablespace_name, v_string);
                        n_lineno := n_lineno + 1;
                END IF;

                v_string := 'ALTER TABLESPACE '||lower(rec_cur_temp_ts.tablespace_name);

                b_first_rec := TRUE;
                insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
                values (n_lineno, rec_cur_temp_ts.tablespace_name, v_string);
                n_lineno := n_lineno + 1;

                IF rec_cur_temp_ts.extent_management = 'DICTIONARY' THEN

                        OPEN cur_df(rec_cur_temp_ts.tablespace_name);
                        LOOP
                                FETCH cur_df INTO rec_cur_df;
                                EXIT WHEN cur_df%NOTFOUND;
                                IF (b_first_rec) then
                                        b_first_rec := FALSE;

                                        v_string := 'ADD DATAFILE ';
                                        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
                                        values (n_lineno, rec_cur_temp_ts.tablespace_name, v_string);
                                        n_lineno := n_lineno + 1;
                                ELSE
                                        UPDATE temp_for_pro_gen_ts_sql_temp
                                        SET text = text||','
                                        WHERE lineno = n_lineno - 1;

                                        INSERT INTO temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text) 
                                        VALUES(n_lineno, rec_cur_temp_ts.tablespace_name, chr(9)||chr(9));
                                        n_lineno := n_lineno + 1;
                                END IF;

                                IF rec_cur_df.autoextensible = 'YES' THEN
                                        n_max_size := ceil(rec_cur_df.maxbytes/1024/1024);
                                        IF n_max_size > 10240 THEN
                                                n_max_size := 10240;
                                        END IF;
                                        v_string:=''''||rec_cur_df.file_name||''''||
                                           ' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M '||
                                           ' MAXSIZE '||to_char(n_max_size)||'M ';
                                ELSE
                                        v_string:=''''||rec_cur_df.file_name||''''||
                                           ' SIZE '||to_char(ceil(rec_cur_df.bytes/1024/1024)) || 'M REUSE';
                                END IF;

                                update temp_for_pro_gen_ts_sql_temp
                                set text = nvl(text,'')||v_string
                                where lineno = n_lineno - 1;

                        END LOOP;
                        CLOSE cur_df;

                        -- v_string := 'TEMPORARY ;';
                        v_string := 'TEMPORARY ;';
                        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
                        values (n_lineno, rec_cur_temp_ts.tablespace_name, v_string);
                        n_lineno := n_lineno + 1;

                        v_string:='                                                  ';
                        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
                        values (n_lineno, rec_cur_temp_ts.tablespace_name, v_string);
                        n_lineno := n_lineno + 1;
                ELSE

                        OPEN cur_temp_df(rec_cur_temp_ts.tablespace_name);
                        LOOP
                                FETCH cur_temp_df INTO rec_cur_temp_df;
                                EXIT WHEN cur_temp_df%NOTFOUND;
                                if (b_first_rec) then
                                        b_first_rec := FALSE;

                                        v_string := 'ADD TEMPFILE ';
                                        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
                                        values (n_lineno, rec_cur_temp_ts.tablespace_name, v_string);
                                        n_lineno := n_lineno + 1;
                                else
                                        update temp_for_pro_gen_ts_sql_temp
                                        set text = text||','
                                        where lineno = n_lineno - 1;

                                        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text) values
                                        (n_lineno, rec_cur_temp_ts.tablespace_name, chr(9)||chr(9));
                                        n_lineno := n_lineno + 1;
                                end if;
                                IF rec_cur_temp_df.autoextensible = 'YES' THEN
                                        n_max_size := ceil(rec_cur_temp_df.maxbytes/1024/1024);
                                        IF n_max_size > 10240 THEN
                                                n_max_size := 10240;
                                        END IF;
                                        v_string:=''''||rec_cur_temp_df.file_name||''''||
                                           ' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M '||
                                           ' MAXSIZE '||to_char(n_max_size)||'M ';
                                ELSE
                                        v_string:=''''||rec_cur_temp_df.file_name||''''||
                                           ' SIZE '||to_char(ceil(rec_cur_temp_df.bytes/1024/1024)) || 'M REUSE';
                                END IF;

                                update temp_for_pro_gen_ts_sql_temp
                                set text = nvl(text,'')||v_string
                                where lineno = n_lineno - 1;

                        END LOOP;
                        CLOSE cur_temp_df;

                        update temp_for_pro_gen_ts_sql_temp
                        set text = text||';'
                        where lineno = n_lineno - 1;

                        v_string:='                                                  ';
                        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
                        values (n_lineno, rec_cur_temp_ts.tablespace_name, v_string);
                        n_lineno := n_lineno + 1;
                END IF;
        END LOOP;
        CLOSE cur_temp_ts;

        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
        values (n_lineno, 'ALTER USER', '                    ');
        n_lineno := n_lineno + 1;

        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
        values (n_lineno, 'ALTER USER', '-- ~~~~~~~~~~~~~~~~ ALTER USER TEMPORARY TABLESAPCE SQL ~~~~~~~~~~~~~~~');
        n_lineno := n_lineno + 1;

        insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
        values (n_lineno, 'ALTER USER', '                    ');
        n_lineno := n_lineno + 1;

        OPEN cur_users;
        LOOP
                FETCH cur_users INTO rec_cur_users;
                EXIT WHEN cur_users%NOTFOUND;

                insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
                values (n_lineno, 'ALTER USER', rec_cur_users.sql_text);
                n_lineno := n_lineno + 1;

                insert into temp_for_pro_gen_ts_sql_temp (lineno, ts_name, text)
                values (n_lineno, 'ALTER USER', '                    ');
                n_lineno := n_lineno + 1;

        END LOOP;
        CLOSE cur_users;

        commit;
END;
/