Many times a PL/SQL programmer will create a
database level table to hold intermediate results. This creation
of an intermediate table results in IO, recursive SQL and the
associated time delays and performance costs. By using as PL/SQL
table constructed in memory the cost of using a database table and
populating it is eliminated, many times at a significant
performance improvement.
PL/SQL tables should not be used for large data
loads or large processing requirements, they are most suitable for
loading look up values that are scanned repetitively or for small
intermediate result table. One limitation is that any needed file
generation will have to be moved from SQL into UTL_FILE since
standard SQL (non-PL/SQL) cannot read the PL/SQL tables. For an
example look at the database recreation script generator in Figure
23.
REM FUNCTION:
SCRIPT FOR CREATING 9.1 DB FROM EXISTING DB
REM This script must be run by a user with the DBA role.
REM This script is intended to run with Oracle9i.
REM Running this script will in turn create a script to
REM rebuild the database. This created
REM
script, crt_db91.sql, is run by SQLPLUS
REM Only preliminary testing of this script was
performed.
REM Be sure to test it completely before relying on it.
REM M. Ault 8/23/01 TUSC Consulting
REM
rem SET VERIFY OFF FEEDBACK OFF ECHO OFF PAGES 0
SET TERMOUT ON
PROMPT Creating db build script...
rem SET TERMOUT OFF;
set verify on feedback on echo on
CREATE TABLE db_temp
(lineno NUMBER, text VARCHAR2(255))
/
TRUNCATE TABLE db_temp;
CREATE OR REPLACE PROCEDURE recreate_db AS
CURSOR get_block IS
SELECT value
FROM v$parameter
WHERE name='db_block_size';
--
CURSOR sys_ts_cursor IS
SELECT initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
min_extlen,
extent_management,
allocation_type
FROM dba_tablespaces
WHERE tablespace_name = 'SYSTEM';
--
CURSOR undo_ts_cursor IS
SELECT initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
min_extlen,
extent_management,
allocation_type,
tablespace_name
FROM dba_tablespaces,v$parameter
WHERE name='undo_tablespace'
AND tablespace_name = value;
--
CURSOR temp_ts_cursor IS
SELECT initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
min_extlen,
extent_management,
allocation_type,
tablespace_name
FROM dba_tablespaces, database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE'
AND property_value = tablespace_name;
--
CURSOR sys_df_cursor IS
SELECT file_name,
bytes,
autoextensible,
maxbytes,
increment_by
FROM dba_data_files
WHERE tablespace_name = 'SYSTEM'
ORDER BY file_id;
--
CURSOR undo_df_cursor IS
SELECT file_name,
bytes,
autoextensible,
maxbytes,
increment_by
FROM dba_data_files, v$parameter
WHERE name='undo_tablespace'
AND tablespace_name = value
ORDER BY file_id;
--
CURSOR temp_df_cursor IS
SELECT a.file_name,
a.bytes,
a.autoextensible,
a.maxbytes,
a.increment_by
FROM dba_temp_files a, database_properties b
WHERE b.property_name='DEFAULT_TEMP_TABLESPACE'
AND a.tablespace_name=b.property_value
ORDER BY file_id;
--
CURSOR get_timezone IS
SELECT property_value
FROM database_properties
WHERE property_name='DBTIMEZONE';
--
CURSOR grp_cursor IS
SELECT group#
FROM v$log;
--
CURSOR mem_cursor (grp_num number) IS
SELECT a.member,
b.bytes
FROM v$logfile a,
v$log b
WHERE a.group#=grp_num
AND a.group#=b.group#
ORDER BY
member;
--
CURSOR get_char_sets IS
SELECT property_name,property_value
FROM database_properties
WHERE property_name IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
--
CURSOR get_max_values IS
SELECT type, records_total
FROM v$controlfile_record_section
WHERE type in ('DATABASE','REDO LOG','DATAFILE','LOG
HISTORY');
--
-- Variable Declarations
--
block_size NUMBER;
lv_initial_extent dba_tablespaces.initial_extent%TYPE;
lv_next_extent dba_tablespaces.next_extent%TYPE;
lv_min_extents dba_tablespaces.min_extents%TYPE;
lv_max_extents dba_tablespaces.max_extents%TYPE;
lv_pct_increase dba_tablespaces.pct_increase%TYPE;
lv_file_name dba_data_files.file_name%TYPE;
lv_bytes dba_data_files.bytes%TYPE;
lv_max_extend dba_data_files.maxbytes%TYPE;
lv_ext_incr dba_data_files.increment_by%TYPE;
lv_autoext dba_data_files.autoextensible%TYPE;
lv_first_rec BOOLEAN;
lv_min_extlen dba_tablespaces.min_extlen%TYPE;
lv_extent_man dba_tablespaces.extent_management%TYPE;
lv_allocation dba_tablespaces.allocation_type%TYPE;
lv_ts_name dba_tablespaces.tablespace_name%TYPE;
lv_timezone database_properties.property_value%TYPE;
lv_char_set database_properties.property_value%TYPE;
lv_type database_properties.property_name%TYPE;
lv_max_var VARCHAR2(15);
lv_max_val NUMBER;
sub_strg VARCHAR2(20);
grp_member v$logfile.member%TYPE;
bytes v$log.bytes%TYPE;
db_name VARCHAR2(8);
db_string VARCHAR2(255);
db_lineno NUMBER := 0;
thrd NUMBER;
grp NUMBER;
sz NUMBER;
begin_count NUMBER;
max_group NUMBER;
--
-- Local use procedures
--
PROCEDURE write_out(p_line INTEGER,
p_string VARCHAR2) IS
BEGIN
INSERT INTO db_temp (lineno,text)
VALUES (p_line,p_string);
END;
--
-- Start of actual code
--
BEGIN
BEGIN
SELECT MAX(group#) INTO max_group FROM sys.v$log;
db_lineno:=db_lineno+1;
SELECT 'CREATE DATABASE '||name INTO db_string
FROM sys.v$database;
write_out(db_lineno,db_string);
db_lineno:=db_lineno+1;
SELECT 'CONTROLFILE REUSE' INTO db_string
FROM dual;
write_out(db_lineno,db_string);
COMMIT;
END;
BEGIN
OPEN get_max_values;
LOOP
FETCH get_max_values INTO lv_max_var,lv_max_val;
EXIT WHEN get_max_values%NOTFOUND;
IF lv_max_var='DATABASE' THEN
lv_max_var:='MAXINSTANCES';
ELSIF lv_max_var = 'REDO LOG' THEN
lv_max_var:='MAXLOGFILES';
ELSIF lv_max_var = 'LOG HISTORY' THEN
lv_max_var:='MAXLOGHISTORY';
ELSIF lv_max_var = 'DATAFILE' THEN
lv_max_var:='MAXDATAFILES';
END IF;
db_string:=lv_max_var||' '||to_char(lv_max_val);
write_out(db_lineno,db_string);
db_lineno:=db_lineno+1;
END LOOP;
CLOSE get_max_values;
COMMIT;
END;
BEGIN
OPEN sys_ts_cursor;
FETCH sys_ts_cursor INTO Lv_initial_extent,
Lv_next_extent,
Lv_min_extents,
Lv_max_extents,
Lv_pct_increase,
Lv_min_extlen,
Lv_extent_man,
Lv_allocation;
lv_first_rec := TRUE;
CLOSE sys_ts_cursor;
OPEN sys_df_cursor;
LOOP
FETCH sys_df_cursor INTO lv_file_name,
lv_bytes,
lv_autoext,
lv_max_extend,
lv_ext_incr;
EXIT WHEN sys_df_cursor%NOTFOUND;
IF (lv_first_rec) THEN
lv_first_rec := FALSE;
db_string := 'DATAFILE ';
ELSE
db_string := db_string ||chr(10)|| ',';
END IF;
db_string:=db_string||chr(39)||lv_file_name||chr(39)||
' SIZE '||to_char(lv_bytes) || ' REUSE';
IF lv_autoext='YES' THEN
IF lv_max_extend=0 THEN
sub_strg:=' MAXSIZE UNLIMITED';
ELSE
sub_strg:=' MAXSIZE '||TO_CHAR(lv_max_extend);
END IF;
IF lv_ext_incr != 0 THEN
db_string:=db_string||chr(10)||' AUTOEXTEND ON NEXT '||
to_char(lv_ext_incr*block_size)||sub_strg;
END IF;
END IF;
IF lv_min_extlen != 0 AND lv_extent_man!='LOCAL' THEN
db_string:=db_string||chr(10)||
'MINIMUM EXTENT '||TO_CHAR(lv_min_extlen);
END IF;
END LOOP;
CLOSE sys_df_cursor;
db_lineno := db_lineno + 1;
write_out(db_lineno, db_string);
db_lineno := db_lineno + 1;
IF lv_extent_man!='LOCAL' THEN
db_string := (' DEFAULT STORAGE (INITIAL ' ||
TO_CHAR(lv_initial_extent) ||
' NEXT ' || lv_next_extent);
ELSE
db_string:=('EXTENT MANAGEMENT '||lv_extent_man);
IF lv_allocation='UNIFORM' THEN
db_string:=db_string||' '||lv_extent_man||' SIZE '
||lv_initial_extent;
ELSE
db_string:=db_string||' AUTOALLOCATE';
END IF;
END IF;
write_out(db_lineno, db_string);
db_lineno := db_lineno + 1;
IF lv_extent_man!='LOCAL' THEN
db_string := (' MINEXTENTS ' ||
lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents);
write_out(db_lineno, db_string);
db_lineno := db_lineno + 1;
db_string := (' PCTINCREASE ' ||
lv_pct_increase || ')');
write_out(db_lineno, db_string);
END IF;
COMMIT;
END;
BEGIN
SELECT MAX(group#) INTO max_group FROM sys.v$log;
db_lineno:=db_lineno+1;
SELECT 'LOGFILE ' INTO db_string
FROM dual;
write_out(db_lineno,db_string);
COMMIT;
IF grp_cursor%ISOPEN
THEN
CLOSE grp_cursor;
OPEN grp_cursor;
ELSE
OPEN grp_cursor;
END IF;
LOOP
FETCH grp_cursor INTO grp;
EXIT WHEN grp_cursor%NOTFOUND;
db_lineno:=db_lineno+1;
db_string:= ' GROUP '||grp||' (';
write_out(db_lineno,db_string);
IF mem_cursor%ISOPEN THEN
CLOSE mem_cursor;
OPEN mem_cursor(grp);
ELSE
OPEN mem_cursor(grp);
END IF;
db_lineno:=db_lineno+1;
begin_count:=db_lineno;
LOOP
FETCH mem_cursor INTO grp_member, bytes;
EXIT when mem_cursor%NOTFOUND;
IF begin_count=db_lineno THEN
db_string:=chr(39)||grp_member||chr(39);
write_out(db_lineno,db_string);
db_lineno:=db_lineno+1;
ELSE
db_string:=' ,'||chr(39)||grp_member||chr(39);
write_out(db_lineno,db_string);
db_lineno:=db_lineno+1;
END IF;
END LOOP;
db_lineno:=db_lineno+1;
IF grp=max_group THEN
db_string:=' ) SIZE '||bytes;
write_out(db_lineno,db_string);
ELSE
db_string:=' ) SIZE '||bytes||',';
write_out(db_lineno,db_string);
END IF;
END LOOP;
END;
BEGIN
OPEN get_block;
FETCH get_block INTO block_size;
CLOSE get_block;
END;
BEGIN
OPEN undo_ts_cursor;
FETCH undo_ts_cursor INTO Lv_initial_extent,
Lv_next_extent,
Lv_min_extents,
Lv_max_extents,
Lv_pct_increase,
Lv_min_extlen,
Lv_extent_man,
Lv_allocation,
LV_ts_name;
lv_first_rec := TRUE;
CLOSE undo_ts_cursor;
OPEN undo_df_cursor;
LOOP
FETCH undo_df_cursor INTO lv_file_name,
lv_bytes,
lv_autoext,
lv_max_extend,
lv_ext_incr;
EXIT WHEN undo_df_cursor%NOTFOUND;
IF (lv_first_rec) THEN
lv_first_rec := FALSE;
db_string := 'UNDO TABLESPACE '||lv_ts_name||'
DATAFILE ';
ELSE
db_string := db_string ||chr(10)|| ',';
END IF;
db_string:=db_string||chr(39)||lv_file_name||chr(39)||
' SIZE '||to_char(lv_bytes) || ' REUSE';
IF lv_autoext='YES' THEN
IF lv_max_extend=0 THEN
sub_strg:=' MAXSIZE UNLIMITED';
ELSE
sub_strg:=' MAXSIZE '||TO_CHAR(lv_max_extend);
END IF;
IF lv_ext_incr != 0 THEN
db_string:=db_string||chr(10)||' AUTOEXTEND ON NEXT '||
to_char(lv_ext_incr*block_size)||sub_strg;
END IF;
END IF;
IF lv_min_extlen != 0 AND lv_extent_man!='LOCAL' THEN
db_string:=db_string||chr(10)||
'MINIMUM EXTENT '||TO_CHAR(lv_min_extlen);
END IF;
END LOOP;
CLOSE undo_df_cursor;
db_lineno := db_lineno + 1;
write_out(db_lineno, db_string);
db_lineno := db_lineno + 1;
IF lv_extent_man!='LOCAL' THEN
db_string := (' DEFAULT STORAGE (INITIAL ' ||
TO_CHAR(lv_initial_extent) ||
' NEXT ' || lv_next_extent);
ELSE
db_string:=('EXTENT MANAGEMENT '||lv_extent_man);
IF lv_allocation='UNIFORM' THEN
db_string:=db_string||' '||lv_extent_man||' SIZE '
||lv_initial_extent;
ELSE
db_string:=db_string||' AUTOALLOCATE';
END IF;
END IF;
write_out(db_lineno, db_string);
db_lineno := db_lineno + 1;
IF lv_extent_man!='LOCAL' THEN
db_string := (' MINEXTENTS ' ||
lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents);
write_out(db_lineno, db_string);
db_lineno := db_lineno + 1;
db_string := (' PCTINCREASE ' ||
lv_pct_increase || ')');
write_out(db_lineno, db_string);
END IF;
COMMIT;
END;
BEGIN
OPEN temp_ts_cursor;
FETCH temp_ts_cursor INTO Lv_initial_extent,
Lv_next_extent,
Lv_min_extents,
Lv_max_extents,
Lv_pct_increase,
Lv_min_extlen,
Lv_extent_man,
Lv_allocation,
LV_ts_name;
lv_first_rec := TRUE;
CLOSE temp_ts_cursor;
OPEN temp_df_cursor;
LOOP
FETCH temp_df_cursor INTO lv_file_name,
lv_bytes,
lv_autoext,
lv_max_extend,
lv_ext_incr;
EXIT WHEN temp_df_cursor%NOTFOUND;
IF (lv_first_rec) THEN
lv_first_rec := FALSE;
db_string := 'DEFAULT TEMPORARY TABLESPACE '||lv_ts_name||'
TEMPFILE ';
ELSE
db_string := db_string ||chr(10)|| ',';
END IF;
db_string:=db_string||chr(39)||lv_file_name||chr(39)||
' SIZE '||to_char(lv_bytes) || ' REUSE';
IF lv_autoext='YES' THEN
IF lv_max_extend=0 THEN
sub_strg:=' MAXSIZE UNLIMITED';
ELSE
sub_strg:=' MAXSIZE '||TO_CHAR(lv_max_extend);
END IF;
IF lv_ext_incr != 0 THEN
db_string:=db_string||chr(10)||' AUTOEXTEND ON NEXT '||
to_char(lv_ext_incr*block_size)||sub_strg;
END IF;
END IF;
IF lv_min_extlen != 0 AND lv_extent_man!='LOCAL' THEN
db_string:=db_string||chr(10)||
'MINIMUM EXTENT '||TO_CHAR(lv_min_extlen);
END IF;
END LOOP;
CLOSE temp_df_cursor;
db_lineno := db_lineno + 1;
write_out(db_lineno, db_string);
db_lineno := db_lineno + 1;
IF lv_extent_man!='LOCAL' THEN
db_string := (' DEFAULT STORAGE (INITIAL ' ||
TO_CHAR(lv_initial_extent) ||
' NEXT ' || lv_next_extent);
ELSE
db_string:=('EXTENT MANAGEMENT '||lv_extent_man);
IF lv_allocation='UNIFORM' THEN
db_string:=db_string||' '||lv_extent_man||' SIZE '
||lv_initial_extent;
ELSE
db_string:=db_string||' AUTOALLOCATE';
END IF;
END IF;
write_out(db_lineno, db_string);
db_lineno := db_lineno + 1;
IF lv_extent_man!='LOCAL' THEN
db_string := (' MINEXTENTS ' ||
lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents);
write_out(db_lineno, db_string);
db_lineno := db_lineno + 1;
db_string := (' PCTINCREASE ' ||
lv_pct_increase || ')');
write_out(db_lineno, db_string);
END IF;
COMMIT;
END;
BEGIN
OPEN get_char_sets;
LOOP
FETCH get_char_sets INTO lv_type,lv_char_set;
EXIT WHEN get_char_sets%NOTFOUND;
IF lv_type='NLS_CHARACTERSET' THEN
db_string:='CHARACTER SET '||lv_char_set;
ELSE
db_string:='NATIONAL CHARACTER SET '||lv_char_set;
END IF;
db_lineno:=db_lineno+1;
write_out(db_lineno,db_string);
END LOOP;
CLOSE get_char_sets;
COMMIT;
END;
BEGIN
OPEN get_timezone;
FETCH get_timezone into lv_timezone;
db_string:='SET TIME_ZONE ='||chr(39)||lv_timezone||chr(39);
db_lineno:=db_lineno+1;
write_out(db_lineno,db_string);
COMMIT;
END;
BEGIN
SELECT DECODE(value,'TRUE','ARCHIVELOG','FALSE','NOARCHIVELOG')
INTO db_string FROM v$parameter WHERE name='log_archive_start';
db_lineno:=db_lineno+1;
write_out(db_lineno,db_string);
SELECT ';' INTO db_string from dual;
db_lineno:=db_lineno+1;
write_out(db_lineno,db_string);
CLOSE mem_cursor;
CLOSE grp_cursor;
COMMIT;
END;
END;
/|show error
execute recreate_db;
rem The next
section could be converted to use
rem UTLFILE so the entire anonymous PL/SQL section
rem and this report section would become a stored
rem procedure, but to keep it generic I will leave as
rem is.
COLUMN dbname NEW_VALUE db NOPRINT
SELECT name dbname FROM v$database;
SET HEADING OFF PAGES 0 VERIFY OFF RECSEP OFF LINES 132
COLUMN text FORMAT a132 WORD_WRAP
SPOOL rep_out/&db/crt_db91.sql
SELECT text
FROM db_temp
ORDER BY lineno;
SPOOL OFF
SET FEEDBACK ON VERIFY ON TERMOUT ON
DROP TABLE db_temp;
PROMPT Press enter to continue
SET VERIFY ON FEEDBACK ON PAGES 22 TERMOUT ON
CLEAR COLUMNS
Figure 23: Example DB Script Generator
While there are many inefficiencies in the PL/SQL
code, notice that it first creates a temporary table, truncates it
and then once it is finished with in, drops it. This creation,
population, use and drop is all expensive and robs performance.
The procedure in Figure 24 is more efficient.
REM FUNCTION:
SCRIPT FOR CREATING 9.1 DB FROM EXISTING DB
REM This script must be run by a user with the DBA role.
REM This script is intended to run with Oracle9i.
REM Running this script will in turn create a script to
REM rebuild the database. This created
REM
script, crt_db91.sql, is run by SQLPLUS
REM Only preliminary testing of this script was
performed.
REM Be sure to test it completely before relying on it.
REM M. Ault 8/23/01 TUSC Consulting
REM
rem SET VERIFY OFF FEEDBACK OFF ECHO OFF PAGES 0
SET TERMOUT ON
PROMPT Creating db build script...
DROP SEQUENCE
lineno_seq;
CREATE SEQUENCE lineno_seq MINVALUE 1 INCREMENT BY 1 NOCACHE;
set verify on feedback on echo on
CREATE OR REPLACE PROCEDURE recreate_db2 AS
--
lineno_count INTEGER;
file_handle UTL_FILE.FILE_TYPE;
--
TYPE db_temp IS TABLE OF VARCHAR2(255)
INDEX BY BINARY_INTEGER;
db_table DB_TEMP;
--
CURSOR get_block IS
SELECT value
FROM v$parameter
WHERE name='db_block_size';
--
CURSOR sys_ts_cursor IS
SELECT initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
min_extlen,
extent_management,
allocation_type
FROM dba_tablespace
WHERE tablespace_name = 'SYSTEM';
--
CURSOR undo_ts_cursor IS
SELECT initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
min_extlen,
extent_management,
allocation_type,
tablespace_name
FROM dba_tablespaces,v$parameter
WHERE name='undo_tablespace'
AND tablespace_name = value;
--
CURSOR temp_ts_cursor IS
SELECT initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
min_extlen,
extent_management,
allocation_type,
tablespace_name
FROM dba_tablespaces, database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE'
AND property_value = tablespace_name;
--
CURSOR sys_df_cursor IS
SELECT file_name,
bytes,
autoextensible,
maxbytes,
increment_by
FROM dba_data_files
WHERE tablespace_name = 'SYSTEM'
ORDER BY file_id;
--
CURSOR undo_df_cursor IS
SELECT file_name,
bytes,
autoextensible,
maxbytes,
increment_by
FROM dba_data_files, v$parameter
WHERE name='undo_tablespace'
AND tablespace_name = value
ORDER BY file_id;
--
CURSOR temp_df_cursor IS
SELECT a.file_name,
a.bytes,
a.autoextensible,
a.maxbytes,
a.increment_by
FROM dba_temp_files a, database_properties b
WHERE b.property_name='DEFAULT_TEMP_TABLESPACE'
AND a.tablespace_name=b.property_value
ORDER BY file_id;
--
CURSOR get_timezone IS
SELECT property_value
FROM database_properties
WHERE property_name='DBTIMEZONE';
--
CURSOR grp_cursor IS
SELECT group#
FROM v$log;
--
CURSOR mem_cursor (grp_num number) IS
SELECT a.member,
b.bytes
FROM v$logfile a,
v$log b
WHERE a.group#=grp_num
AND a.group#=b.group#
ORDER BY
member;
--
CURSOR get_char_sets IS
SELECT property_name,property_value
FROM database_properties
WHERE property_name IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
--
CURSOR get_max_values IS
SELECT type, records_total
FROM v$controlfile_record_section
WHERE type in ('DATABASE','REDO LOG','DATAFILE','LOG
HISTORY');
--
-- Variable Declarations
--
block_size NUMBER;
lv_initial_extent dba_tablespaces.initial_extent%TYPE;
lv_next_extent dba_tablespaces.next_extent%TYPE;
lv_min_extents dba_tablespaces.min_extents%TYPE;
lv_max_extents dba_tablespaces.max_extents%TYPE;
lv_pct_increase dba_tablespaces.pct_increase%TYPE;
lv_file_name dba_data_files.file_name%TYPE;
lv_bytes dba_data_files.bytes%TYPE;
lv_max_extend dba_data_files.maxbytes%TYPE;
lv_ext_incr dba_data_files.increment_by%TYPE;
lv_autoext dba_data_files.autoextensible%TYPE;
lv_first_rec BOOLEAN;
lv_min_extlen dba_tablespaces.min_extlen%TYPE;
lv_extent_man dba_tablespaces.extent_management%TYPE;
lv_allocation dba_tablespaces.allocation_type%TYPE;
lv_ts_name
dba_tablespaces.tablespace_name%TYPE;
lv_timezone
database_properties.property_value%TYPE;
lv_char_set
database_properties.property_value%TYPE;
lv_type
database_properties.property_name%TYPE;
lv_max_var
VARCHAR2(15);
lv_max_val NUMBER;
sub_strg VARCHAR2(20);
grp_member v$logfile.member%TYPE;
bytes v$log.bytes%TYPE;
db_name VARCHAR2(8);
db_string VARCHAR2(255);
thrd NUMBER;
grp NUMBER;
sz NUMBER;
begin_count NUMBER;
max_group NUMBER;
--
-- Local use procedures
--
PROCEDURE write_out(
p_string VARCHAR2) IS
BEGIN
SELECT lineno_seq.nextval into lineno_count from dual;
SELECT p_string INTO db_table (lineno_count)
FROM dual;
END;
--
-- Start of actual code
--
BEGIN
BEGIN
SELECT MAX(group#) INTO max_group FROM sys.v$log;
SELECT 'CREATE DATABASE '||name INTO db_string
FROM sys.v$database;
write_out(db_string);
SELECT 'CONTROLFILE REUSE' INTO db_string
FROM dual;
write_out(db_string);
END;
BEGIN
OPEN get_max_values;
LOOP
FETCH get_max_values INTO lv_max_var,lv_max_val;
EXIT WHEN get_max_values%NOTFOUND;
IF lv_max_var='DATABASE' THEN
lv_max_var:='MAXINSTANCES';
ELSIF lv_max_var = 'REDO LOG' THEN
lv_max_var:='MAXLOGFILES';
ELSIF lv_max_var = 'LOG HISTORY' THEN
lv_max_var:='MAXLOGHISTORY';
ELSIF lv_max_var = 'DATAFILE' THEN
lv_max_var:='MAXDATAFILES';
END IF;
db_string:=lv_max_var||' '||to_char(lv_max_val);
write_out(db_string);
END LOOP;
CLOSE get_max_values;
END;
BEGIN
OPEN sys_ts_cursor;
FETCH sys_ts_cursor INTO Lv_initial_extent,
Lv_next_extent,
Lv_min_extents,
Lv_max_extents,
Lv_pct_increase,
Lv_min_extlen,
Lv_extent_man,
Lv_allocation;
lv_first_rec := TRUE;
CLOSE sys_ts_cursor;
OPEN sys_df_cursor;
LOOP
FETCH sys_df_cursor INTO lv_file_name,
lv_bytes,
lv_autoext,
lv_max_extend,
lv_ext_incr;
EXIT WHEN sys_df_cursor%NOTFOUND;
IF (lv_first_rec) THEN
lv_first_rec := FALSE;
db_string := 'DATAFILE ';
ELSE
db_string := db_string ||chr(10)|| ',';
END IF;
db_string:=db_string||chr(39)||lv_file_name||chr(39)||
' SIZE '||to_char(lv_bytes) || ' REUSE';
IF lv_autoext='YES' THEN
IF lv_max_extend=0 THEN
sub_strg:=' MAXSIZE UNLIMITED';
ELSE
sub_strg:=' MAXSIZE '||TO_CHAR(lv_max_extend);
END IF;
IF lv_ext_incr != 0 THEN
db_string:=db_string||chr(10)||' AUTOEXTEND ON NEXT '||
to_char(lv_ext_incr*block_size)||sub_strg;
END IF;
END IF;
IF lv_min_extlen != 0 AND lv_extent_man!='LOCAL' THEN
db_string:=db_string||chr(10)||
'MINIMUM EXTENT '||TO_CHAR(lv_min_extlen);
END IF;
END LOOP;
CLOSE sys_df_cursor;
write_out(db_string);
IF lv_extent_man!='LOCAL' THEN
db_string := (' DEFAULT STORAGE (INITIAL ' ||
TO_CHAR(lv_initial_extent) ||
' NEXT ' || lv_next_extent);
ELSE
db_string:=('EXTENT MANAGEMENT '||lv_extent_man);
IF lv_allocation='UNIFORM' THEN
db_string:=db_string||' '||lv_extent_man||' SIZE '
||lv_initial_extent;
ELSE
db_string:=db_string||' AUTOALLOCATE';
END IF;
END IF;
write_out(db_string);
IF lv_extent_man!='LOCAL' THEN
db_string := (' MINEXTENTS ' ||
lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents);
write_out(db_string);
db_string := (' PCTINCREASE ' ||
lv_pct_increase || ')');
write_out(db_string);
END IF;
END;
BEGIN
SELECT MAX(group#) INTO max_group FROM sys.v$log;
SELECT 'LOGFILE ' INTO db_string
FROM dual;
write_out(db_string);
IF
grp_cursor%ISOPEN
THEN
CLOSE grp_cursor;
OPEN grp_cursor;
ELSE
OPEN grp_cursor;
END IF;
LOOP
FETCH grp_cursor INTO grp;
EXIT WHEN grp_cursor%NOTFOUND;
db_string:= ' GROUP '||grp||' (';
write_out(db_string);
IF mem_cursor%ISOPEN THEN
CLOSE mem_cursor;
OPEN mem_cursor(grp);
ELSE
OPEN mem_cursor(grp);
END IF;
begin_count:=0;
LOOP
FETCH mem_cursor INTO grp_member, bytes;
EXIT when mem_cursor%NOTFOUND;
IF begin_count=0 THEN
db_string:=chr(39)||grp_member||chr(39);
write_out(db_string);
ELSE
db_string:=' ,'||chr(39)||grp_member||chr(39);
write_out(db_string);
END IF;
END LOOP;
IF grp=max_group THEN
db_string:=' ) SIZE '||bytes;
write_out(db_string);
ELSE
db_string:=' ) SIZE '||bytes||',';
write_out(db_string);
END IF;
END LOOP;
END;
BEGIN
OPEN get_block;
FETCH get_block INTO block_size;
CLOSE get_block;
END;
BEGIN
OPEN undo_ts_cursor;
FETCH undo_ts_cursor INTO Lv_initial_extent,
Lv_next_extent,
Lv_min_extents,
Lv_max_extents,
Lv_pct_increase,
Lv_min_extlen,
Lv_extent_man,
Lv_allocation,
LV_ts_name;
lv_first_rec := TRUE;
CLOSE undo_ts_cursor;
OPEN undo_df_cursor;
LOOP
FETCH undo_df_cursor INTO lv_file_name,
lv_bytes,
lv_autoext,
lv_max_extend,
lv_ext_incr;
EXIT WHEN undo_df_cursor%NOTFOUND;
IF (lv_first_rec) THEN
lv_first_rec := FALSE;
db_string := 'UNDO TABLESPACE '||lv_ts_name||'
DATAFILE ';
ELSE
db_string := db_string ||chr(10)|| ',';
END IF;
db_string:=db_string||chr(39)||lv_file_name||chr(39)||
' SIZE '||to_char(lv_bytes) || ' REUSE';
IF lv_autoext='YES' THEN
IF lv_max_extend=0 THEN
sub_strg:=' MAXSIZE UNLIMITED';
ELSE
sub_strg:=' MAXSIZE '||TO_CHAR(lv_max_extend);
END IF;
IF lv_ext_incr != 0 THEN
db_string:=db_string||chr(10)||' AUTOEXTEND ON NEXT '||
to_char(lv_ext_incr*block_size)||sub_strg;
END IF;
END IF;
IF lv_min_extlen != 0 AND lv_extent_man!='LOCAL' THEN
db_string:=db_string||chr(10)||
'MINIMUM EXTENT '||TO_CHAR(lv_min_extlen);
END IF;
END LOOP;
CLOSE undo_df_cursor;
write_out(db_string);
IF lv_extent_man!='LOCAL' THEN
db_string := (' DEFAULT STORAGE (INITIAL ' ||
TO_CHAR(lv_initial_extent) ||
' NEXT ' || lv_next_extent);
ELSE
db_string:=('EXTENT MANAGEMENT '||lv_extent_man);
IF lv_allocation='UNIFORM' THEN
db_string:=db_string||' '||lv_extent_man||' SIZE '
||lv_initial_extent;
ELSE
db_string:=db_string||' AUTOALLOCATE';
END IF;
END IF;
write_out(db_string);
IF lv_extent_man!='LOCAL' THEN
db_string := (' MINEXTENTS ' ||
lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents);
write_out(db_string);
db_string := (' PCTINCREASE ' ||
lv_pct_increase || ')');
write_out(db_string);
END IF;
END;
BEGIN
OPEN temp_ts_cursor;
FETCH temp_ts_cursor INTO Lv_initial_extent,
Lv_next_extent,
Lv_min_extents,
Lv_max_extents,
Lv_pct_increase,
Lv_min_extlen,
Lv_extent_man,
Lv_allocation,
LV_ts_name;
lv_first_rec := TRUE;
CLOSE temp_ts_cursor;
OPEN temp_df_cursor;
LOOP
FETCH temp_df_cursor INTO lv_file_name,
lv_bytes,
lv_autoext,
lv_max_extend,
lv_ext_incr;
EXIT WHEN temp_df_cursor%NOTFOUND;
IF (lv_first_rec) THEN
lv_first_rec := FALSE;
db_string := 'DEFAULT TEMPORARY TABLESPACE '||lv_ts_name||'
TEMPFILE ';
ELSE
db_string := db_string ||chr(10)|| ',';
END IF;
db_string:=db_string||chr(39)||lv_file_name||chr(39)||
' SIZE '||to_char(lv_bytes) || ' REUSE';
IF lv_autoext='YES' THEN
IF lv_max_extend=0 THEN
sub_strg:=' MAXSIZE UNLIMITED';
ELSE
sub_strg:=' MAXSIZE '||TO_CHAR(lv_max_extend);
END IF;
IF lv_ext_incr != 0 THEN
db_string:=db_string||chr(10)||' AUTOEXTEND ON NEXT '||
to_char(lv_ext_incr*block_size)||sub_strg;
END IF;
END IF;
IF lv_min_extlen != 0 AND lv_extent_man!='LOCAL' THEN
db_string:=db_string||chr(10)||
'MINIMUM EXTENT '||TO_CHAR(lv_min_extlen);
END IF;
END LOOP;
CLOSE temp_df_cursor;
write_out(db_string);
IF lv_extent_man!='LOCAL' THEN
db_string := (' DEFAULT STORAGE (INITIAL ' ||
TO_CHAR(lv_initial_extent) ||
' NEXT ' || lv_next_extent);
ELSE
db_string:=('EXTENT MANAGEMENT '||lv_extent_man);
IF lv_allocation='UNIFORM' THEN
db_string:=db_string||' '||lv_extent_man||' SIZE '
||lv_initial_extent;
ELSE
db_string:=db_string||' AUTOALLOCATE';
END IF;
END IF;
write_out(db_string);
IF lv_extent_man!='LOCAL' THEN
db_string := (' MINEXTENTS ' ||
lv_min_extents ||
' MAXEXTENTS ' || lv_max_extents);
write_out(db_string);
db_string := (' PCTINCREASE ' ||
lv_pct_increase || ')');
write_out(db_string);
END IF;
END;
BEGIN
OPEN get_char_sets;
LOOP
FETCH get_char_sets INTO lv_type,lv_char_set;
EXIT WHEN get_char_sets%NOTFOUND;
IF lv_type='NLS_CHARACTERSET' THEN
db_string:='CHARACTER SET '||lv_char_set;
ELSE
db_string:='NATIONAL CHARACTER SET '||lv_char_set;
END IF;
write_out(db_string);
END LOOP;
CLOSE get_char_sets;
END;
BEGIN
OPEN get_timezone;
FETCH get_timezone into lv_timezone;
db_string:='SET TIME_ZONE ='||chr(39)||lv_timezone||chr(39);
write_out(db_string);
END;
BEGIN
SELECT DECODE(value,'TRUE','ARCHIVELOG','FALSE','NOARCHIVELOG')
INTO db_string FROM v$parameter WHERE name='log_archive_start';
write_out(db_string);
SELECT ';' INTO db_string from dual;
write_out(db_string);
CLOSE mem_cursor;
CLOSE grp_cursor;
END;
BEGIN
file_handle:=UTL_FILE.FOPEN('d:\oracle\sql_scripts\','rebuild_db91.sql','W',256);
FOR i in 1..lineno_count LOOP
UTL_FILE.PUT_LINE(file_handle,db_table(i));
END LOOP;
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN others THEN
UTL_FILE.FCLOSE(file_handle);
END;
END;
/
show error
execute recreate_db2;
Figure 24: Using PL/SQL Tables to speed
processing.
In this limited example the average execution time
dropped from 8.16 seconds to 8.06 seconds. With larger IO amounts
(this was only 34 lines output to the Oracle table.) Note that the
code has been simplified by removing the line count code and the
commits. If the SELECT actions in the WRITE_OUT internal procedure
that go against the DUAL table where removed performance could be
increased even more.
and include the URL for the page.
