database’imizde bulunan tüm tablespace’lerimizin scriptini
aşağıdaki örnek .sh dosyasında spool ile .txt dosyasına alabiliriz.
———————————————–
#!/usr/bin/ksh
. $HOME/.profile

sqlplus -s /nolog < /dev/null
connect orhan/password@orcl

set verify off;
set termout off;
set feedback off;
set pagesize 0;

create table ts_temp (lineno number, ts_name varchar2(40),text long);

DECLARE
CURSOR ts_cursor IS select tablespace_name,
initial_extent,
next_extent,
min_extents,
max_extents,
pct_increase,
status
from sys.dba_tablespaces
where tablespace_name != ‘SYSTEM’
and status != ‘INVALID’
order by tablespace_name;
CURSOR df_cursor (c_ts VARCHAR2) IS select file_name,
bytes
from sys.dba_data_files
where tablespace_name = c_ts
and tablespace_name != ‘SYSTEM’
order by file_name;

lv_tablespace_name sys.dba_tablespaces.tablespace_name%TYPE;
lv_initial_extent sys.dba_tablespaces.initial_extent%TYPE;
lv_next_extent sys.dba_tablespaces.next_extent%TYPE;
lv_min_extents sys.dba_tablespaces.min_extents%TYPE;
lv_max_extents sys.dba_tablespaces.max_extents%TYPE;
lv_pct_increase sys.dba_tablespaces.pct_increase%TYPE;
lv_status sys.dba_tablespaces.status%TYPE;
lv_file_name sys.dba_data_files.file_name%TYPE;
lv_bytes sys.dba_data_files.bytes%TYPE;
lv_first_rec BOOLEAN;
lv_string long;
lv_lineno number := 0;

procedure write_out(p_line INTEGER, p_name VARCHAR2,
p_string VARCHAR2) is
begin
insert into ts_temp (lineno, ts_name, text) values
(p_line, p_name, p_string);

end;

BEGIN
OPEN ts_cursor;
LOOP
FETCH ts_cursor INTO lv_tablespace_name,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_pct_increase,
lv_status;
EXIT WHEN ts_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := (‘CREATE TABLESPACE ‘||lower(lv_tablespace_name));
lv_first_rec := TRUE;
write_out(lv_lineno, lv_tablespace_name, lv_string);
OPEN df_cursor(lv_tablespace_name);
LOOP
FETCH df_cursor INTO lv_file_name,
lv_bytes;
EXIT WHEN df_cursor%NOTFOUND;
if (lv_first_rec) then
lv_first_rec := FALSE;
lv_string := ‘DATAFILE ‘;
else
lv_string := lv_string || ‘,’;
end if;
lv_string:=lv_string||””||lv_file_name||””||
‘ SIZE ‘||to_char(lv_bytes) || ‘ REUSE’;
END LOOP;
CLOSE df_cursor;
lv_lineno := lv_lineno + 1;
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (‘ DEFAULT STORAGE (INITIAL ‘ ||
to_char(lv_initial_extent) ||
‘ NEXT ‘ || lv_next_extent);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (‘ MINEXTENTS ‘ ||
lv_min_extents ||
‘ MAXEXTENTS ‘ || lv_max_extents);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := (‘ PCTINCREASE ‘ ||
lv_pct_increase || ‘)’);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_string := (‘ ‘||lv_status);
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:=’/’;
write_out(lv_lineno, lv_tablespace_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string:=’ ‘;
write_out(lv_lineno, lv_tablespace_name, lv_string);
END LOOP;
CLOSE ts_cursor;
END;
/
SPOOL /temp/orhan/tablespace_scripts.txt
set heading off
set recsep off
col text format a80 word_wrap

select text
from ts_temp
order by ts_name, lineno;

spool off;

drop table ts_temp;
—————————————–

eğer tablespace’lerimiz üzerinde zamanla değişiklikler
yapmışsak (datafile’ların boyutunun artırılması gibi,
ALTER DATABASE DATAFILE …) o zaman en güncel haliyle
tablespace’lerimizin scriptlerini dbms_metadata.get_ddl
ile çıkarabiliriz.

————————————-
set lines 132
set pages 200
set long 4000

select dbms_metadata.get_ddl(‘TABLESPACE’, tablespace_name) DDL from dba_tablespaces
————————————-

bir örnek script daha..
——————————————-
DECLARE

CURSOR get_ts IS SELECT * FROM dba_tablespaces
WHERE tablespace_name != ‘SYSTEM’;

CURSOR get_df (p_ts VARCHAR2) IS
SELECT * from dba_data_files
WHERE tablespace_name = p_ts;

l_str VARCHAR2(10);

BEGIN

FOR ts_rec IN get_ts LOOP

dbms_output.put_line (‘CREATE TABLESPACE ‘||ts_rec.tablespace_name);

FOR df_rec IN get_df (ts_rec.tablespace_name) LOOP

IF get_df%ROWCOUNT = 1 THEN
l_str := ‘DATAFILE’;
ELSE
l_str := ‘,’;
END IF;

dbms_output.put_line (l_str||’ ‘
||chr(39)||df_rec.file_name||chr(39)
||’ SIZE ‘||df_rec.bytes||’ REUSE ‘);

if df_rec.autoextensible = ‘YES’ then

dbms_output.put_line (‘ AUTOEXTEND ON’
||’ NEXT ‘||df_rec.increment_by );

if df_rec.maxbytes = 68719443968 then
dbms_output.put_line (‘ MAXSIZE UNLIMITED’);
else
dbms_output.put_line (‘ MAXSIZE ‘||df_rec.maxbytes);
end if;

end if;

END LOOP;

dbms_output.put_line (‘EXTENT MANAGEMENT ‘ ||ts_rec.extent_management );

if ts_rec.extent_management = ‘LOCAL’ then

if ts_rec.allocation_type = ‘SYSTEM’ then

dbms_output.put_line (‘ AUTOALLOCATE ‘);

else

dbms_output.put_line (‘ UNIFORM SIZE ‘||ts_rec.initial_extent);

end if;

end if;

if ts_rec.extent_management = ‘DICTIONARY’ then

dbms_output.put_line (‘DEFAULT STORAGE (INITIAL ‘||ts_rec.initial_extent
||’ NEXT ‘||ts_rec.next_extent
||’ MINEXTENTS ‘||ts_rec.min_extents
||’ MAXEXTENTS ‘||ts_rec.max_extents
||’ PCTINCREASE ‘||ts_rec.pct_increase||’ ) ‘);

end if;

dbms_output.put_line (‘ ONLINE;’);
dbms_output.new_line;

END LOOP;

END;
/
————————————————-