Sql*loader的傻瓜方法

一:建有索引，但查询过程显然没有用到相关的索引

逐步的,也将自己解决问题的记录整理出来.

1: ora-02085错误信息的解决方式:

(1):如果dblink与当前db不是同一台服务器,则将dblink数据库的init,ora中的global_name改为false

(2):如果dblink与当前db是同一台服务器,则将dblink数据库的init,ora和当前db中的global_name改为false

(3):create database link dblinkname CONNECT TO USERNAME IDENTIFIED BY PASSWORD USING 'tnsname.ora中的服务名';

logmnr笔记

EXECUTE dbms_logmnr_d.build('logmnr.ora','f:oracleadminnphtlutlfile');->
EXECUTE dbms_logmnr.add_logfile('f:oracleoradataREDO01.log',dbms_logmnr.new);-->sys.v$logmnr_logs
EXECUTE dbms_logmnr.add_logfile('f:oracleoradataREDO02.log',dbms_logmnr.addfile);
EXECUTE dbms_logmnr.add_logfile('f:oracleoradataREDO03.log',dbms_logmnr.addfile);
ALTER session SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SELECT low_time,high_time,db_name,low_scn,next_scn FROM v_$logmnr_logs;
EXECUTE dbms_logmnr.start_logmnr(11968307,4294967295,to_date('2004-04-09 15:41:51','yyyy-mm-dd hh24:mi:ss'),to_date('2004-04-09 15:44:42','yyyy-mm-dd hh24:mi:ss'),'f:oracleadminnphtlutlfilelmnrdir.ora');->v_$logmnr_contents
SET heading off;
SET linesize 8000;
SELECT timestamp,ubafil,ubablk,data_blk#,seg_name,seg_type_name,table_space,session#,session_info,
username,sql_redo,sql_undo,csf,ph1_name,ph1_redo,ph1_undo  FROM v_$logmnr_contents;

一:操作系统自动export的文件名含有日期(基于windows)

1: show block space

create or replace procedure show_space
    ( p_segname in varchar2,
      p_owner   in varchar2 default user,
      p_type    in varchar2 default 'TABLE' )
    as
        l_free_blks                 number;
   
        l_total_blocks              number;
        l_total_bytes               number;
        l_unused_blocks             number;
        l_unused_bytes              number;
        l_LastUsedExtFileId         number;
        l_LastUsedExtBlockId        number;
        l_LAST_USED_BLOCK           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.free_blocks
        ( segment_owner     => p_owner,
          segment_name      => p_segname,
          segment_type      => p_type,
          freelist_group_id => 0,
          free_blks         => l_free_blks );
  
       dbms_space.unused_space
       ( segment_owner     => p_owner,
         segment_name      => p_segname,
          segment_type      => p_type,
          total_blocks      => l_total_blocks,
          total_bytes       => l_total_bytes,
          unused_blocks     => l_unused_blocks,
          unused_bytes      => l_unused_bytes,
          LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
          LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
          LAST_USED_BLOCK => l_LAST_USED_BLOCK );
   
        p( 'Free Blocks', l_free_blks );
        p( 'Total Blocks', l_total_blocks );
        p( 'Total Bytes', l_total_bytes );
        p( 'Unused Blocks', l_unused_blocks );
        p( 'Unused Bytes', l_unused_bytes );
        p( 'Last Used Ext FileId', l_LastUsedExtFileId );
        p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
        p( 'Last Used Block', l_LAST_USED_BLOCK );
    end;

 查看全文

以下是个人收集的oracle网站

有好长一段时间没碰数据库,一直以为自己的状态应该依旧,可是,当真正面临实战的时候,才发现头脑里只有概念了.现在将当初使用一些工具的备忘录整理一下.也以此告诫自己要踏实,勤学,切勿浮躁,将心态归零

一:使用explain plan

 查看全文