Oracle's V$ Views(轉)

ADPDB>select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

select name, checkpoint_change#, to_char(checkpoint_time, 'DD.MM.YYYY HH24:MI:SS') from v$datafile_header

ORA81> select count(*) from v where name like 'V$%';

  COUNT(*)
----------
       185

select 
  oracle_username
  os_user_name,
  locked_mode,
  object_name,
  object_type
from 
  v$locked_object a,dba_objects b
where 
  a.object_id = b.object_id

select process, pid, status, client_process, group# "Stdby Redo Log Gr", block# from v$managed_standby;

select value from v$parameter where name = 'db_block_size'

create table f(g number);

create or replace procedure long_proc as
    rindex       pls_integer := dbms_application_info.set_session_longops_nohint;
    slno         pls_integer; 
                                          -- Name of task
    op_name      varchar2(64) := 'long_proc';

    target       pls_integer := 0;        -- ie. The object being worked on
    context      pls_integer;             -- Any info
    sofar        number;                  -- how far proceeded
    totalwork    number := 1000000;       -- finished when sofar=totalwork

                                          -- desc of target
    target_desc  varchar2(32) := 'A long running procedure';

    units        varchar2(32) := 'inserts';                -- unit of sofar and totalwork
  begin

  dbms_application_info.set_module('long_proc',null);

  dbms_application_info.set_session_longops (
    rindex,
    slno);

  for sofar in 0..totalwork loop

    insert into f values (sofar);

    if mod(sofar,1000) = 0 then
      dbms_application_info.set_session_longops (
        rindex,
        slno,
        op_name,
        target,
        context,
        sofar,
        totalwork,
        target_desc,
        units);

    end if;

  end loop;
end long_proc;

select time_remaining,sofar,elapsed_seconds 
from v$session_longops l, v$session s 
where l.sid=s.sid and l.serial# = s.serial# and s.module='long_proc'

select * from v$sgastat where name = 'free memory'

select sql_text from v$sqlarea where users_executing > 0;

variable addr varchar2(20)
variable hash number
variable child number

exec :addr := '&sqladdr'; :hash :=  &hashvalue; :child := &childno;

select lpad(' ', 2*(level-1))||operation||' '||
decode(id, 0, 'Cost = '||position) "OPERATION",
options, object_name
from v$sql_plan
start with (address = :addr
              and hash_value = :hash
              and child_number = :child
              and id=0 )
  connect by prior id = parent_id
          and prior address = address
          and prior hash_value = hash_value
          and prior child_number = child_number
  order by id, position ;

select sql_text,address,hash_value,child_number from v$sql where users_executing > 0; 

set serveroutput on size 1000000

declare
  v_stmt     varchar2(16000);
  v_sql_text v$sqltext_with_newlines.sql_text%type;
  v_sid      v$session.sid%type;
begin
  for r in (
    select 
        sql_text,s.sid
    from 
      v$sqltext_with_newlines t, 
      v$session s 
    where 
      s.sql_address=t.address
    order by s.sid, piece) loop

    v_sid := nvl(v_sid,r.sid);

    if v_sid <> r.sid then
      dbms_output.put_line(v_sid);
      put_line(v_stmt,100);
      v_sid  := r.sid; 
      v_stmt := r.sql_text;
    else
      v_stmt := v_stmt || r.sql_text;
    end if;
    
  end loop;
  dbms_output.put_line(v_sid);
  dbms_output.put_line(v_stmt,100);

end;
/

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production