FAQ OracleConsultez toutes les FAQ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> SELECT   *  
  2  FROM     USER_OBJECT_SIZE  
  3  WHERE    TYPE IN ('PACKAGE BODY','PACKAGE','FUNCTION','PROCEDURE')  
  4  ORDER BY TYPE,NAME ;  
  
NAME                           TYPE          SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE  
------------------------------ ------------- ----------- ----------- ---------- ----------  
LIGNESALAIRE                   FUNCTION              280         261 563          0  
PWD_DECODE                     FUNCTION              459         234 702          0  
RETOURNE_PARAM                 FUNCTION              885         239 990          0  
SRV_LECTURE_PARAMETRE          FUNCTION              458         342 697          0  
TEST_XX                        FUNCTION              869         269 1119         0  
PKG_CORRECTION                 PACKAGE               414        1493 362          0  
TEST_RECORD                    PACKAGE               233         761 875          0  
PKG_CORRECTION                 PACKAGE BODY        11408           0 12467        0  
TEST_RECORD                    PACKAGE BODY          403           0 1376         0  
DEBUG                          PROCEDURE            1153         475 1343         0  
DISPLAY_IMAGE                  PROCEDURE             894         509 1277         0  
ECRITURE_ERREUR                PROCEDURE             755        1517 673          0

ps -ef | egrep pmon_ | grep -v grep

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
COL "Total octets alloués" FORMAT A20  
COL "octets utilisés" FORMAT A20  
COL "octets libres" FORMAT A20  
SELECT  
       a.POOL "Pool"  
    , b.Octets || ' (' || ROUND(b.Octets/1024/1024) || ' Mo)' "Total octets alloués"  
    , (b.Octets-a.BYTES) || ' (' || ROUND((b.Octets-a.BYTES)/1024/1024) || ' Mo)' "octets utilisés"  
    , a.BYTES || ' (' || ROUND(a.BYTES/1024/1024) || 'Mo)' "octets libres"  
FROM   V$SGASTAT a,  
       (  SELECT POOL, SUM(BYTES) Octets, SUM(BYTES/1024/1024) Mo  
          FROM V$SGASTAT  
          WHERE POOL IS NOT NULL  
          GROUP BY POOL  
          ORDER BY POOL) b  
WHERE  NAME = 'free memory'  
AND    a.POOL = b.POOL  
ORDER BY a.POOL ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SQL> COL "Total octets alloués" FORMAT A20  
SQL> COL "octets utilisés" FORMAT A20  
SQL> COL "octets libres" FORMAT A20  
SQL> SELECT  
  2      a.POOL "Pool"  
  3    , b.Octets || ' (' || ROUND(b.Octets/1024/1024) || ' Mo)' "Total octets alloués"  
  4    , (b.Octets-a.BYTES) || ' (' || ROUND((b.Octets-a.BYTES)/1024/1024) || ' Mo)' "octets utilisés"  
  5    , a.BYTES || ' (' || ROUND(a.BYTES/1024/1024) || 'Mo)' "octets libres"  
  6  FROM   V$SGASTAT a,  
  7         (  SELECT POOL, SUM(BYTES) Octets, SUM(BYTES/1024/1024) Mo  
  8            FROM V$SGASTAT  
  9            WHERE POOL IS NOT NULL  
 10            GROUP BY POOL  
 11            ORDER BY POOL) b  
 12  WHERE  NAME = 'free memory'  
 13  AND    a.POOL = b.POOL  
 14  ORDER BY a.POOL  
 15  /  
  
Pool        Total octets alloués octets utilisés      octets libres  
----------- -------------------- -------------------- --------------------  
java pool   29360128 (28 Mo)     0 (0 Mo)             29360128 (28Mo)  
large pool  8404644 (8 Mo)       304668 (0 Mo)        8099976 (8Mo)  
shared pool 41943040 (40 Mo)     22455544 (21 Mo)     19487496 (19Mo)

1
2
3
4
5
6
7
8
9
set linesize 250 
col file_name format a40 
  
select b.file_name, a.file#, a.cnt 
  from (select file#, count(1) cnt  
          from v$bh 
         group by file#) a, 
       dba_data_files b 
 where a.file# = b.file_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT COMP_NAME, 
       STATUS, 
       VERSION 
  FROM DBA_REGISTRY 
 ORDER BY COMP_NAME; 
  
COMP_NAME                          STATUS VERSION 
---------------------------------- ------ ----------- 
JServer JAVA Virtual Machine       VALID  10.2.0.2.0 
Oracle Database Catalog Views      VALID  10.2.0.2.0 
Oracle Database Java Packages      VALID  10.2.0.2.0 
Oracle Database Packages and Types VALID  10.2.0.2.0 
Oracle Expression Filter           VALID  10.2.0.2.0 
Oracle Text                        VALID  10.2.0.2.0 
Oracle Workspace Manager           VALID  10.2.0.3.0 
Oracle XDK                         VALID  10.2.0.2.0 
Oracle XML Database                VALID  10.2.0.2.0 
Oracle interMedia                  VALID  10.2.0.2.0

1
2
3
4
5
6
7
8
9
10
11
SELECT ACTION_TIME, 
       ACTION, 
       VERSION, 
       ID 
  FROM DBA_REGISTRY_HISTORY 
 ORDER BY to_timestamp(ACTION_TIME,'DD.MM.YYYY HH24:MI:SSXFF'); 
  
ACTION_TIME      ACTION   VERSION              ID 
---------------- -------- ------------ ---------- 
14.02.2006 10:28 CPU                      4751932 
07.03.2006 11:30 UPGRADE  10.2.0.2.0

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT occupant_name, schema_name, move_procedure 
  2    FROM v$sysaux_occupants ; 
  
OCCUPANT_NAME    SCHEMA_NAME     MOVE_PROCEDURE 
---------------- --------------- --------------------------------------------------------------- 
LOGMNR           SYSTEM          SYS.DBMS_LOGMNR_D.SET_TABLESPACE 
LOGSTDBY         SYSTEM          SYS.DBMS_LOGSTDBY.SET_TABLESPACE 
STREAMS          SYS                                              
XDB              XDB             XDB.DBMS_XDB.MOVEXDB_TABLESPACE 
AO               SYS             DBMS_AW.MOVE_AWMETA 
XSOQHIST         SYS             DBMS_XSOQ.OlapiMoveProc 
...

1
2
3
4
5
SQL> SELECT dbms_utility.port_string FROM dual; 
  
PORT_STRING 
----------------------------------------------- 
IBMPC/WIN_NT-8.1.0

1
2
3
4
5
SELECT spid                   
  FROM v$process                   
 WHERE NOT EXISTS ( SELECT 1  
                      FROM v$session                                      
                     WHERE paddr = addr);

1
2
3
SVRMGRL> SELECT spid, osuser, s.program                   
          FROM v$process p, v$session s                   
         WHERE p.addr=s.paddr;

c:>orakill sid thread

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/disk1' SCOPE=BOTH SID='*' ;

1
2
3
4
5
6
7
8
9
10
SQL> SELECT pid FROM v$process 
  2   WHERE addr =  
  3  ( 
  4      SELECT paddr FROM v$bgprocess 
  5       WHERE name = 'SMON' 
  6  ); 
  
       PID 
---------- 
         8

1
2
SQL> ORADEBUG WAKEUP 8 
Instruction traitée.

1
2
3
4
5
6
7
SQL> SELECT   se.osuser, se.username, se.sid, 
  2           su.extents, su.blocks * to_number(rtrim(p.value)) AS Space, 
  3           tablespace 
  4  FROM     v$sort_usage su, v$parameter p, v$session se 
  5  WHERE    p.name = 'db_block_size' 
  6  AND      su.session_addr = se.saddr 
  7  ORDER BY se.username, se.sid;