« Oracle数据恢复:SYSTEM回滚段损坏案例一则 | Blog首页 | Invalid Login Using Trusted Authentication 错误原因 »

《循序渐进Oracle》第二版一书相关代码

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%';

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）；

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
/

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;
/

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;
/

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
/

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;         
/

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 UNLIMITED

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
/

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
/

By eygle on 2011-08-09 16:47 | Comments (2) | Books | 2856 |

Book

2 Comments

stone.xiao | October 27, 2011 12:40 PM

Eygle老师: 我在深入解析书中看到有关v$Process视图的一段话"如果在OS上发现某个进程表现异常(如占用很高的CPU资源),那么通过OS上的PID和V$Process视图中的SPID关联,就可以找到OS上的进程在数据库内部的化身..." 这段话在LINUX下可能很快的找到相关的进程.但是在WINDOWS下,任务管理器中的Oracle进程的PID号怎么在v$Process中查找不到,这个在DB中该如何查找?特别是一台WINDOWS机器上启动了几个实例,那么如何区别这几个oracle.exe分别属于哪个sid呢?望解答,万分感谢.

eygle | October 28, 2011 1:01 AM

参考：
http://www.eygle.com/archives/2004/10/nttools_mange_windows_oracle.html

Windows上总有一些工具可以帮你解决这些问题。