Asunto
001 : Tipos de datos Medidos en Bytes
Asunto 002 : Calculo del Extent Inicial
Asunto 003 : Crear jobs en Oracle
Asunto 004 : Consulta sobre Database Link
Asunto 005 : Manual del Developer 2000
Asunto 006 : Uso del Package dbms_sql
Asunto 007 : URL del SQL ORACLE 8
Asunto 008 : Imagenes & Long Raw
Asunto 009 : Crecimiento de Tablas e Indices
Asunto 010 : Bases de datos de Desarrollo
Sample Code Repository
Name: PL/SQL: DBMS_SQL sample code
----------------------------------------------------------------------------
Description:
Basic DBMS_SQL sample which opens a dynamically created cursor
Source Code:
set termout on
set serveroutput on
declare
v_cur integer;
v_cur_val varchar2(2000);
v_bind_val integer;
v_cur_tmp integer;
begin
v_cur := dbms_sql.open_cursor;
dbms_sql.parse(v_cur,'select * from dept where deptno < :v_bind_val',dbms_sql.v7);
dbms_sql.define_column(v_cur,2,v_cur_val,2000);
dbms_sql.bind_variable(v_cur,':row',row);
v_cur_tmp := dbms_sql.execute(v_cur);
loop
if dbms_sql.fetch_rows(v_cur) = 0 then
exit;
end if;
dbms_sql.column_value(v_cur,2,v_cur_val);
dbms_output.put_line('Value: ' ||v_cur_val);
end loop;
dbms_sql.close_cursor(v_cur);
end;
SET ECHO OFF
REM ******************************************************************
REM DBA REPORTS
REM ******************************************************************
REM
REM Program : rep_space_def.SQL
REM Function: to print the name of segments that allocate
REM extent for them will be fault
REM because there is not enought space on the
REM owner tablespace.
REM
REM ******************************************************************
create table system.free_space_help as
select tablespace_name, max(bytes) bytes from sys.dba_free_space
group by tablespace_name
/
create table system.frsp_anct as select * from sys.dba_free_space where 1=2
/
alter table system.frsp_anct add total_data_files number
/
alter table system.frsp_anct add total_free_space number
/
rem set newpage 0
ttitle center 'Print the name of segments that have not enought space on the' skip -
center 'owner tablespace for extend.' -
center 'Segments without enought space on TS ' skip skip -
left 'File Name : REP_SPACE_DEF.SQL' -
right 'Page:' format 999 sql.pno skip skip
col ow format a18 heading 'Owner'
col ty format a10 heading 'Type'
col na format a30 heading 'Name'
col exts format 9,999 heading 'Extents'
col maxe format 9,999 heading 'Max'
col flag format a1 heading ' '
col nline newline
column segment_name format a30 heading 'Segment|Name'
column segment_type format a05 heading 'Segm |Type'
column tablespace_name format a14 heading 'Tablespace|Name'
column owner format a09 heading 'Owner'
column bytes format 999,999,999 heading 'Size Extent |on table byt'
column kbytes format 999,999,999 heading 'Size Extent |on table Kby'
column tsbytes format 999,999,999 heading 'Size Extent |on TS bytes '
column tskbytes format 999,999,999 heading 'Size Extent |on TS Kbytes'
column total_data_files format 999,999,999 heading 'Total Dat.Kb|Files for TS'
column total_free_space format 999,999,999 heading 'Total Fre.Kb|Space for TS'
column extents format 999 heading '# |Ext '
column KBytes format 999,999 heading 'Kilo |bytes '
define free_text_to_identify_report = 'Report Space Deficit'
set verify off
spool rep_space_def.out
start time
set verify off
set feedback 6
set heading on
set pages 28
set lines 130
set tab off
set time on
select segment_name, segment_type, a.tablespace_name, owner,
round(a.next_extent * (1 + pct_increase/100)) bytes,
b.bytes tsbytes, a.extents, (a.blocks * 8 ) KBytes
from sys.dba_segments a, system.free_space_help b
where b.tablespace_name = a.tablespace_name
and round(a.next_extent * (1 + pct_increase/100)) > b.bytes
order by 4, 3, 1
/
declare
cursor c1 is
select distinct b.tablespace_name
from sys.dba_segments a, system.free_space_help b
where b.tablespace_name = a.tablespace_name
and round(a.next_extent * (1 + pct_increase/100)) > b.bytes;
cursor c2 (ts char) is
select tablespace_name, file_id, block_id, bytes, blocks
from sys.dba_free_space
where tablespace_name = ts
order by bytes desc;
fts varchar2(30);
fts2 varchar2(30);
fi number;
bi number;
byt number;
bl number;
total_df number;
total_fs number;
begin
open c1 ;
loop
fetch c1 into fts ;
exit when c1%notfound;
select sum(bytes)
into total_df
from sys.dba_data_files
where tablespace_name = fts;
select sum(bytes)
into total_fs
from sys.dba_free_space
where tablespace_name = fts;
open c2(fts);
for i in 1..5 loop
fetch c2 into fts2, fi, bi, byt, bl;
if c2%notfound then
exit;
else
insert into system.frsp_anct values (fts2,fi,bi,byt,bl,total_df,total_fs);
end if;
end loop;
close c2;
end loop;
commit;
close c1;
end;
/
set feedback on
set pages 15
column dt format a45 heading 'Data File Name'
column file_id format 999 heading 'File|Id '
column block_id format 999999 heading 'Block |Id '
rem compute sum of bytes on tablespace_name
break on tablespace_name skip on total_data_files skip on total_free_space skip
select tablespace_name, file_id, block_id, trunc(bytes/1024) tskbytes, blocks,
trunc(total_data_files/1024) total_data_files,
trunc(total_free_space/1024) total_free_space, to_char(null) dt
from system.frsp_anct
union
select tablespace_name,
to_number(null), to_number(null), to_number(null),
to_number(null), to_number(null), to_number(null),
substr(file_name,1,45) file_name
from system.dba_data_files
where tablespace_name in (select distinct tablespace_name
from system.frsp_anct)
order by 1, 8, 4 desc
/
spool off
ttitle off
clear breaks
clear columns
clear computes
drop table system.free_space_help
/
drop table system.frsp_anct
/
set verify on
Following are the detailed steps that one would need to follow in order to create a second database as a copy of an already existing one. This example copies a database "Alpha" to "Beta". This method uses the Unix 'cp' command and Oracle's 'create controlfile' statement. * Create OFA directory structure for the "Beta" database files. * Modify /etc/oratab to specify the "Beta" database name and oracle_home * Shutdown the "Alpha" database. * Copy "Alpha" database files, redologs and control files (dbf, log & ctl) * to the "Beta" directories under OFA structure. * Copy initalpha.ora & config.ora from the "Alpha" db to initbeta.ora and config.ora for "Beta". * Modify "Beta" config.ora to point to the copied control files for "Beta" * (i.e. do a global change from alpha to beta). * However, do not change the 'dbname' in the config.ora file. * Modify "Beta" config.ora to point to the copied control files for * "Beta" (i.e. do a global change from alpha to beta). * Create a Symbolic link from $oracle_home/dbs/initbeta.ora to * $ORACLE_BASE/admin/beta/pfile/initbeta.ora * Create a Symbolic link from $oracle_home/dbs/initbeta_0.ora to * $ORACLE_BASE/admin/beta/pfile/initbeta_0.ora * Change <SID> to the "Beta" database and start it in the MOUNT State. SQLDBA> connect internal SQLDBA> startup mount SQLDBA>alter database backup controlfile to trace; SQLDBA> shutdown Will get "ORA-01109: Database not open" SQLDBA> exit * Modify the trace file that is create under the * $ORACLE_BASE/admin/beta/udump directory (ora_xxx.trc) * Modify the newly created "CREATE CONTROLFILE" script to point to the * new datafiles and redologs. I usually copy it to a file called hello.sql $ vi ora_xxx.trc (or hello.sql) The first executable line should be STARTUP NOMOUNT. Just delete the ones before it. * Modify the "Create Controlfile ..... " statement to CREATE CONTROLFILE REUSE SET DATABASE "beta" RESETLOGS NOARCHIVELOG You'll have to add the word SET, change the Database name "XXXX" (lower case) & remove NO From RESETLOGS Do a Global change of the old SID name to the new SID name. Make sure all the files listed bellow "point" to the right location of those files. For examples, if you ran out of room and had to "move" .dbf files to other locations, make sure you change that location here!. * $ chmod 755 hello.sql * Modify the 'dbname' in the cconfig.ora to reflect the new <SID> (Beta). * Run the trace file (hello.sql) as a sql scrip from within SQLDBA, connect internal SQLDBA> @hello.sql The instance will start and you get three errors: ORA-00283: recovery session canceled due the errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done ORA-01589: Must use RESETLOGS or NORESETLOGS option for the database open * The create controlfile command updates file headers and datafile * information in the control files. To open the database you will have to * ensure that System Change Number (SCN) in control files are consistent with * SCN's in redologs. If you try to 'alter database open' at this point, it * will fail with a error explaining that redologs need to be reset. To startup * the database issue the following commands: SQLDBA> alter database open resetlogs; This step may take few minutes. SQLDBA> shutdown (just to shotdown cleanly) SQLDBA> startup (just to startup cleanly) I recommend removing the hello.sql and ora_XXX.trc files so future DBA's /client will not get confused. Walla !! You now have a "Beta" database, which is identical to the "Alpha" database. You can then start the "Alpha" database also ( Please be sure to change your SID to alpha ).
Para el tema de la creación de
las bases de datos normalmente utilizo el ERWIN que es
una herramienta gráfica para el modelamiento de base de
datos. Una vez que ya tienes el modelo listo, te permite
conectarte a la base de datos y crea no solo las
estructuras sino también los constraints. El ERWIN
también te permite hacer Ingeniería Reversa es decir
conectarlo a la base de datos y te crea un modelo de todo
lo que ya tienes creado (tablas, relaciones, constraints,
etc) ya sea para hacer correcciones o para recrear todo
nuevamente. Solo faltaria el tema de la migración de los
datos, para eso si tendras que crear un script, utilizar
cursores u otra herramienta.