FAQ OracleConsultez toutes les FAQ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select 
    FILE_ID, 
    FILE_NAME, 
    STATUS, 
    TABLESPACE_NAME 
from 
    DBA_DATA_FILES 
order by FILE_ID; 
  
   FILE_ID FILE_NAME                          STATUS    TABLESPACE_NAME 
---------- ---------------------------------- --------- --------------- 
         1 /u01/LSC63/data/system01LSC63.dbf  AVAILABLE SYSTEM 
         2 /u01/LSC63/data/undo01LSC63.dbf    AVAILABLE UNDOTBS1 
         3 /u01/LSC63/data/sysaux01LSC63.dbf  AVAILABLE SYSAUX 
         4 /u01/LSC63/data/sysaux02LSC63.dbf  AVAILABLE SYSAUX 
         5 /u01/LSC63/data/sysaux03LSC63.dbf  AVAILABLE SYSAUX 
         6 /u01/LSC63/data/users01LSC63.dbf   AVAILABLE USERS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
select 
    a.TABLESPACE_NAME, 
    a.CONTENTS, 
    a.EXTENT_MANAGEMENT, 
    a.ALLOCATION_TYPE, 
    a.SEGMENT_SPACE_MANAGEMENT, 
    a.BIGFILE, 
    a.STATUS, 
    nvl(sum(b.count_files),0) FILES, 
    nvl(sum(b.bytes),0) "SIZE", 
    nvl(sum(b.maxbytes),0) MAX_SIZE, 
    nvl(sum(b.bytes),0)-nvl(sum(c.free_bytes),0) "USED" 
from DBA_TABLESPACES a, 
    ( 
    select TABLESPACE_NAME, 
        sum(BYTES) bytes, 
        count(*) count_files, 
        sum(greatest(MAXBYTES,BYTES)) maxbytes 
    from DBA_DATA_FILES 
    group by TABLESPACE_NAME 
    union all 
    select TABLESPACE_NAME, 
        sum(BYTES), 
        count(*), 
        sum(greatest(MAXBYTES,BYTES)) maxbytes 
    from DBA_TEMP_FILES 
    group by TABLESPACE_NAME 
    ) b, 
    ( 
    select TABLESPACE_NAME, 
        sum(BYTES) free_bytes 
    from DBA_FREE_SPACE 
    group by TABLESPACE_NAME 
    union all 
    select TABLESPACE_NAME, 
        sum(BYTES_FREE) free_bytes 
    from V$TEMP_SPACE_HEADER 
    group by TABLESPACE_NAME 
    ) c 
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+) 
    and a.TABLESPACE_NAME = c.TABLESPACE_NAME (+) 
group by 
    a.TABLESPACE_NAME, 
    a.CONTENTS, 
    a.EXTENT_MANAGEMENT, 
    a.ALLOCATION_TYPE, 
    a.SEGMENT_SPACE_MANAGEMENT, 
    a.BIGFILE, 
    a.STATUS 
order by a.TABLESPACE_NAME; 
  
TABLESPA CONTENTS  EXTENT_MAN ALLOCATIO SEGMEN BIG STATUS FILES      SIZE   MAX_SIZE      USED 
-------- --------- ---------- --------- ------ --- ------ ----- --------- ---------- --------- 
SYSAUX   PERMANENT LOCAL      SYSTEM    AUTO   NO  ONLINE     3 397410304 6442450944 395771904 
SYSTEM   PERMANENT LOCAL      SYSTEM    MANUAL NO  ONLINE     1 471859200 2147483648 470548480 
TEMP     TEMPORARY LOCAL      UNIFORM   MANUAL NO  ONLINE     1  20971520 2147483648  17039360 
UNDOTBS1 UNDO      LOCAL      SYSTEM    MANUAL NO  ONLINE     1 182452224 2147483648  13041664 
USERS    PERMANENT LOCAL      SYSTEM    MANUAL NO  ONLINE     1  26214400 2147483648    524288

1
2
3
4
5
6
7
8
9
10
11
12
13
select NAME, 
    THREAD#, 
    SEQUENCE#, 
    BLOCKS*BLOCK_SIZE "SIZE", 
    RESETLOGS_ID, 
    COMPLETION_TIME, 
    STATUS 
from V$ARCHIVED_LOG 
order by SEQUENCE#; 
  
NAME                                      THR  SEQ    SIZE RESETLOGS COMPLETION S 
----------------------------------------- --- ---- ------- --------- ---------- - 
/u01/LSC63/arch/arch_588083500_1_2472.dbf     2472 2081280 588083500 2006-06-30 A

1
2
3
4
5
6
7
8
9
10
11
12
select 
    FILE_ID, 
    FILE_NAME, 
    STATUS, 
    TABLESPACE_NAME 
from 
    DBA_TEMP_FILES 
order by FILE_ID; 
  
   FILE_ID FILE_NAME                        STATUS    TABLESPACE_NAME 
---------- -------------------------------- --------- --------------- 
         1 /u01/LSC63/data/temp01LSC63.dbf  AVAILABLE TEMP

1
2
3
4
5
6
7
8
9
select NAME, 
    STATUS 
from V$CONTROLFILE 
order by NAME; 
  
NAME                                              STATUS 
------------------------------------------------- ------- 
/u01/LSC63/ctrl/ctrl01aLSC63.dbf 
/u01/LSC63/ctrl/ctrl01bLSC63.dbf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select l.GROUP#, 
    l.THREAD#, 
    l.SEQUENCE#, 
    l.BYTES, 
    l.MEMBERS, 
    l.STATUS, 
    f.TYPE, 
    f.MEMBER 
from V$LOGFILE f, V$LOG l 
where l.GROUP# = f.GROUP# 
order by GROUP#,MEMBER; 
  
GROUP# THR  SEQ   BYTES MEMBERS STATUS     TYPE   MEMBER 
------ --- ---- ------- ------- ---------- ------ -------------------------------- 
     1   1 2473 4194304       2 INACTIVE   ONLINE /u01/LSC63/redo/redo01aLSC63.dbf 
     1   1 2473 4194304       2 INACTIVE   ONLINE /u01/LSC63/redo/redo01bLSC63.dbf 
     2   1 2474 4194304       2 ACTIVE     ONLINE /u01/LSC63/redo/redo02aLSC63.dbf 
     2   1 2474 4194304       2 ACTIVE     ONLINE /u01/LSC63/redo/redo02bLSC63.dbf 
     3   1 2475 4194304       2 CURRENT    ONLINE /u01/LSC63/redo/redo03aLSC63.dbf 
     3   1 2475 4194304       2 CURRENT    ONLINE /u01/LSC63/redo/redo03bLSC63.dbf

1
2
3
4
5
6
7
8
9
10
11
SELECT NAME, 
       DBID, 
       LOG_MODE,  
       FLASHBACK_ON, 
       OPEN_MODE, 
       CREATED 
  FROM v$database; 
  
NAME               DBID LOG_MODE     FLASHBACK OPEN_MODE  CREATED 
--------- ------------- ------------ --------- ---------- ---------- 
LSC63        1119326572 ARCHIVELOG   NO        READ WRITE 2006-04-18

1
2
3
4
5
6
7
8
9
10
11
SELECT PROPERTY_NAME, 
       PROPERTY_VALUE, 
       DESCRIPTION 
  FROM DATABASE_PROPERTIES 
 ORDER BY PROPERTY_NAME; 
  
PROPERTY_NAME                  PROPERTY_VALUE  DESCRIPTION 
------------------------------ --------------- ------------------------------------- 
DEFAULT_PERMANENT_TABLESPACE   USERS           Name of default permanent tablespace 
DEFAULT_TBS_TYPE               SMALLFILE       Default tablespace type 
DEFAULT_TEMP_TABLESPACE        TEMP            Name of default temporary tablespace

1
2
3
4
5
6
7
SELECT INSTANCE_NAME, 
       VERSION 
  FROM V$INSTANCE; 
  
INSTANCE_NAME    VERSION 
---------------- ----------------- 
LSC63            10.2.0.2.0

1
2
3
4
5
6
SQL> SELECT GLOBAL_NAME 
  2    FROM GLOBAL_NAME; 
  
GLOBAL_NAME 
------------------------------ 
LSC63.DOMAIN.CH

1
2
3
4
5
6
7
8
9
SELECT NAME, 
       DETECTED_USAGES 
  FROM DBA_FEATURE_USAGE_STATISTICS 
 ORDER BY NAME; 
  
NAME                  DETECTED 
--------------------- -------- 
Partitioning (system)       11 
Partitioning (user)          0

SQL> drop database;

SQL> alter tablespace Tbs1 rename to Tbs2 ;

1
2
3
4
5
6
7
SQL> create tablespace x datafile '/tmp/x1.dbf' size 4m, '/tmp/x2.dbf' size 4m; 
  
Tablespace created. 
  
SQL> alter tablespace x drop datafile '/tmp/x2.dbf'; 
  
Tablespace altered.

1
2
3
4
5
6
SQL> SELECT A.tablespace_Name, A.Alloue, B.Occupe, C.Libre 
  2    FROM (select tablespace_name, sum(bytes)/1024/1024 AS ALLOUE from dba_data_files group by tablespace_name) a, 
  3         (select tablespace_name, Sum(bytes)/1024/1024 AS OCCUPE from dba_segments group by tablespace_name) b, 
  4         (select tablespace_name, Sum(bytes)/1024/1024 AS LIBRE from dba_free_space group by tablespace_name) c 
  5   WHERE B.tablespace_Name = A.tablespace_Name 
  6     AND C.Tablespace_Name = B.Tablespace_Name;

1
2
3
4
5
6
7
8
9
10
11
SQL> select occupant_name, space_usage_kbytes from v$sysaux_occupants ; 
  
OCCUPANT_NAME                                                    SPACE_USAGE_KBYTES 
---------------------------------------------------------------- ------------------ 
LOGMNR                                                                         6080 
LOGSTDBY                                                                        896 
STREAMS                                                                         512 
XDB                                                                           49600 
AO                                                                            21248 
XSOQHIST                                                                      21248 
...

select username, account_status, default_tablespace from dba_users;