《循序渐进Oracle》第二版一书相关代码
作者：
eygle |
English Version
以下是《循序渐进Oracle》一书第二版的相关脚本，如果大家需要哪个脚本，可以在此提出，我一并补充，谢谢。
P28:GetParDescrb.sql
SET linesize 120
COL name for a30
COL value for a20
COL describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';P163：GetCharcterSet.sql
col nls_charset_id for 9999
col nls_charset_name for a30
col hex_id for a20
select nls_charset_id(value) nls_charset_id,
value nls_charset_name,to_char(nls_charset_id(value), 'xxxx') hex_id
from v$nls_valid_values where parameter = 'CHARACTERSET'
order by nls_charset_id(value）；
P227：GetTrcname.sql
SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' TRACE_FILE_NAME
FROM (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a,
(SELECT SUBSTR (VALUE, -6, 1) symbol FROM v$parameter
WHERE NAME = 'user_dump_dest') b,
(SELECT instance_name FROM v$instance) c,
(SELECT spid FROM v$session s, v$process p, v$mystat m
WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
/P236:GetBFno.sql
CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
RETURN VARCHAR2
IS
l_str VARCHAR2 (255) DEFAULT NULL;
BEGIN
l_str :=
'datafile# is:'
|| DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'))
|| chr(10)||'datablock is:'
|| DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),'xxxxxxxx'));
RETURN l_str;
END;
/P256：show_space_assm.sql
CREATE OR REPLACE PROCEDURE show_space_assm (
p_segname IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT USER,
p_type IN VARCHAR2 DEFAULT 'TABLE'
)
AS
l_fs1_bytes NUMBER;
l_fs2_bytes NUMBER;
l_fs3_bytes NUMBER;
l_fs4_bytes NUMBER;
l_fs1_blocks NUMBER;
l_fs2_blocks NUMBER;
l_fs3_blocks NUMBER;
l_fs4_blocks NUMBER;
l_full_bytes NUMBER;
l_full_blocks NUMBER;
l_unformatted_bytes NUMBER;
l_unformatted_blocks NUMBER;
PROCEDURE p (p_label IN VARCHAR2, p_num IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.put_line (RPAD (p_label, 40, '.') || p_num);
END;
BEGIN
DBMS_SPACE.space_usage (segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
);
p ('free space 0-25% Blocks:', l_fs1_blocks);
p ('free space 25-50% Blocks:', l_fs2_blocks);
p ('free space 50-75% Blocks:', l_fs3_blocks);
p ('free space 75-100% Blocks:', l_fs4_blocks);
p ('Full Blocks:', l_full_blocks);
p ('Unformatted blocks:', l_unformatted_blocks);
END;
/
P265.GetFileHWM.sql
col name for a40
col resizecmd for a80
select a.file#,
a.name,
a.bytes / 1024 / 1024 CurrentMB,
ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
'alter database datafile ''' || a.name || ''' resize ' ||
ceil(HWM * a.block_size) / 1024 / 1024 || 'M;' ResizeCmd
from v$datafile a,
(SELECT file_id, MAX(block_id + blocks - 1) HWM
FROM DBA_EXTENTS
GROUP BY file_id) b
where a.file# = b.file_id(+)
And (a.bytes - HWM * a.block_size) >0
order by 5
/
P290.Get_rowid.sql
create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is :'||to_char(row_number);
return ls_my_rowid ;
end;
/
P535.External表
CREATE TABLE "USERS"
(
USERNAME VARCHAR2(30),
USER_ID NUMBER,
PASSWORD VARCHAR2(30)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SQLDR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'SQLDR':'users.bad'
DISCARDFILE 'SQLDR':'users.dis'
LOGFILE 'SQLDR':'users.log'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
USERNAME CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
USER_ID CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
PASSWORD CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location('data.txt')
)
REJECT LIMIT UNLIMITEDP541.Listener日志
create or replace directory LISTENER_LOG
as '/opt/oracle/product/9.2.0/network/log'
/
CREATE TABLE listener_log (text VARCHAR2(4000))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY listener_log
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
NODISCARDFILE
)
LOCATION ('listener.log')
)
REJECT LIMIT UNLIMITED
/
P544 - P546.Listener日志分析
create or replace directory LISTENER_LOG
as '/opt/oracle/product/9.2.0/network/log'
/
CREATE TABLE listener_log (text VARCHAR2(4000))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY listener_log
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
NODISCARDFILE
)
LOCATION ('listener-hsboss.log')
)
REJECT LIMIT UNLIMITED
/
SELECT DISTINCT SUBSTR (text,
INSTR (text, 'PROGRAM') + 8,
INSTR (SUBSTR (text, INSTR (text, 'PROGRAM') + 8),
')'
)
- 1
) PROGRAM
FROM (SELECT *
FROM listener_log
WHERE text LIKE '%PROGRAM%' AND ROWNUM < 1000000)
/
CREATE TABLE listener_log
(
ldate DATE,
connect_data VARCHAR2(400),
address_protocol VARCHAR2(400),
action VARCHAR2(20),
service_name VARCHAR2(20),
return_code NUMBER(10)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY listener_log
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
NODISCARDFILE
FIELDS TERMINATED BY "*" LRTRIM
MISSING FIELD VALUES ARE NULL
(
ldate CHAR(30) DATE_FORMAT DATE MASK "DD-MON-YYYY HH24:MI:SS",
connect_data,
address_protocol,
action,
service_name,
return_code
)
)
LOCATION ('listener-hsboss.log')
)
REJECT LIMIT UNLIMITED
/
set timing on
SELECT HOST, COUNT (*)
FROM (SELECT SUBSTR (connect_data,
INSTR (connect_data, 'HOST') + 5,
INSTR (SUBSTR (connect_data,
INSTR (connect_data, 'HOST') + 5
),
')'
)
- 1
) HOST
FROM (SELECT *
FROM listener_log
WHERE connect_data LIKE '%PROGRAM%' AND ROWNUM < 1000000))
GROUP BY HOST
ORDER BY 2
/
