27jun21 Practical TBSP
Uploaded by
imsanju190927jun21 Practical TBSP
Uploaded by
imsanju1909[oracle@rhps01 ~]$ ps -ef | grep pmon
oracle 10627 1 0 10:56 ? 00:00:00 ora_pmon_test
oracle 12899 12837 0 11:10 pts/0 00:00:00 grep --color=auto pmon
[oracle@rhps01 ~]$
[oracle@rhps01 ~]$
[oracle@rhps01 ~]$
[oracle@rhps01 ~]$
[oracle@rhps01 ~]$ export ORACLE_SID=test
[oracle@rhps01 ~]$
[oracle@rhps01 ~]$ sqlplus / as sysdba
SQL*Plus: Release [Link].0 Production on Sun Jun 27 11:11:53 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release [Link].0 - 64bit Production
SQL>
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST READ WRITE
SQL>
SQL>
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB1 MOUNTED
4 ABC MOUNTED
6 ANDY READ WRITE NO
SQL>
SQL>
SQL>
SQL>
SQL> alter pluggable database abc open;
Pluggable database altered.
SQL>
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB1 MOUNTED
4 ABC READ WRITE NO
6 ANDY READ WRITE NO
SQL>
SQL>
SQL> show con_id
CON_ID
------------------------------
1
SQL>
SQL>
SQL> alter session set container=abc;
Session altered.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 ABC READ WRITE NO
SQL>
SQL>
SQL> show con_id
CON_ID
------------------------------
4
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release [Link].0 - 64bit
Production
[oracle@rhps01 ~]$
[oracle@rhps01 ~]$
[oracle@rhps01 ~]$ sqlplus / as sysdba
SQL*Plus: Release [Link].0 Production on Sun Jun 27 11:19:30 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release [Link].0 - 64bit Production
SQL>
SQL>
SQL> select name, open_mode from V$database;
NAME OPEN_MODE
--------- --------------------
TEST READ WRITE
SQL>
SQL>
SQL> SELECT * FROM v$controlfile;
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
--- ---------- -------------- ----------
/oracle/app/oradata/test/[Link]
NO 16384 1142 0
/oracle/app/oradata/test/[Link]
NO 16384 1142 0
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
--- ---------- -------------- ----------
SQL> set pages 300 lines 300
SQL>
SQL>
SQL>
SQL> /
STATUS
-------
NAME
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
---------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
--- ---------- -------------- ----------
/oracle/app/oradata/test/[Link]
NO 16384 1142 0
/oracle/app/oradata/test/[Link]
NO 16384 1142 0
SQL> 1
1* SELECT * FROM v$controlfile
SQL> select status, name from v$controlfile;
STATUS
-------
NAME
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
---------------------------------------------------
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/[Link]
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SELECT * FROM v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX
LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ -----------
---------- ---------- ----------
DATABASE 316 1 1 0 0 0
0
CKPT PROGRESS 8180 11 0 0 0
0 0
REDO THREAD 256 8 1 0 0 0
0
REDO LOG 72 16 3 0 0 3
0
DATAFILE 520 1024 20 0 0 454
0
FILENAME 524 4146 28 0 0 0
0
TABLESPACE 180 1024 24 0 0 20
0
TEMPORARY FILENAME 56 1024 5 0 0
182 0
RMAN CONFIGURATION 1108 50 0 0 0
0 0
LOG HISTORY 56 292 20 1 20 20
0
OFFLINE RANGE 200 1063 67 1 67
67 0
ARCHIVED LOG 584 28 19 1 19
19 0
BACKUP SET 96 1022 3 1 3 3
0
BACKUP PIECE 780 1006 3 1 3
3 0
BACKUP DATAFILE 200 1063 3 1 3 3
0
BACKUP REDOLOG 76 215 0 0 0
0 0
DATAFILE COPY 736 1000 3 1 3
3 0
BACKUP CORRUPTION 44 1115 0 0 0 0
0
COPY CORRUPTION 40 1227 0 0 0 0
0
DELETED OBJECT 20 818 4 1 4
4 0
PROXY COPY 928 1004 0 0 0 0
0
BACKUP SPFILE 124 131 3 1 3
3 0
DATABASE INCARNATION 56 292 2 1 2
2 0
FLASHBACK LOG 84 2048 0 0 0
0 0
RECOVERY DESTINATION 180 1 0 0 0
0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0
0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0
0 0
RMAN STATUS 116 141 3 1 3 3
0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0
0 0
MTTR 100 8 1 0 0 0
0
DATAFILE HISTORY 568 57 0 0 0 0
0
STANDBY DATABASE MATRIX 400 128 128 0 0
0 0
GUARANTEED RESTORE POINT 256 2048 0 0 0
0 0
RESTORE POINT 256 2108 0 0 0
0 0
DATABASE BLOCK CORRUPTION 80 8384 0 0 0
0 0
ACM OPERATION 104 64 10 0 0
0 0
FOREIGN ARCHIVED LOG 604 1002 0 0 0
0 0
PDB RECORD 780 10 6 0 0 20
0
AUXILIARY DATAFILE COPY 584 128 0 0 0
0 0
MULTI INSTANCE REDO APPLY 556 1 0 0 0
0 0
PDBINC RECORD 144 113 0 0 0
0 0
TABLESPACE KEY HISTORY 108 151 0 0 0
0 0
42 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> desc v$datafile
Name
Null? Type
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
------- --------
-----------------------------------------------------------------------------------
---------------------------------
FILE#
NUMBER
CREATION_CHANGE#
NUMBER
CREATION_TIME
DATE
TS#
NUMBER
RFILE#
NUMBER
STATUS
VARCHAR2(7)
ENABLED
VARCHAR2(10)
CHECKPOINT_CHANGE#
NUMBER
CHECKPOINT_TIME
DATE
UNRECOVERABLE_CHANGE#
NUMBER
UNRECOVERABLE_TIME
DATE
LAST_CHANGE#
NUMBER
LAST_TIME
DATE
OFFLINE_CHANGE#
NUMBER
ONLINE_CHANGE#
NUMBER
ONLINE_TIME
DATE
BYTES
NUMBER
BLOCKS
NUMBER
CREATE_BYTES
NUMBER
BLOCK_SIZE
NUMBER
NAME
VARCHAR2(513)
PLUGGED_IN
NUMBER
BLOCK1_OFFSET
NUMBER
AUX_NAME
VARCHAR2(513)
FIRST_NONLOGGED_SCN
NUMBER
FIRST_NONLOGGED_TIME
DATE
FOREIGN_DBID
NUMBER
FOREIGN_CREATION_CHANGE#
NUMBER
FOREIGN_CREATION_TIME
DATE
PLUGGED_READONLY
VARCHAR2(3)
PLUGIN_CHANGE#
NUMBER
PLUGIN_RESETLOGS_CHANGE#
NUMBER
PLUGIN_RESETLOGS_TIME
DATE
CON_ID
NUMBER
SQL>
SQL>
SQL>
SQL> select file#, ts#, name, status, bytes from v$datafile;
FILE# TS#
---------- ----------
NAME
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
---------------------------------------------------
STATUS BYTES
------- ----------
1 0
/oracle/app/oradata/test/[Link]
SYSTEM 849346560
3 1
/oracle/app/oradata/test/[Link]
ONLINE 513802240
4 2
/oracle/app/oradata/test/[Link]
ONLINE 73400320
5 0
/oracle/app/oradata/test/pdbseed/[Link]
SYSTEM 262144000
6 1
/oracle/app/oradata/test/pdbseed/[Link]
ONLINE 346030080
7 4
/oracle/app/oradata/test/[Link]
ONLINE 5242880
8 2
/oracle/app/oradata/test/pdbseed/[Link]
ONLINE 104857600
9 0
/oracle/app/oradata/test/testpdb1/[Link]
SYSTEM 262144000
10 1
/oracle/app/oradata/test/testpdb1/[Link]
ONLINE 367001600
11 2
/oracle/app/oradata/test/testpdb1/[Link]
ONLINE 104857600
12 5
/oracle/app/oradata/test/testpdb1/[Link]
ONLINE 5242880
13 0
/oracle/app/oradata/test/andy/[Link]
SYSTEM 849346560
14 1
/oracle/app/oradata/test/andy/[Link]
ONLINE 503316480
15 2
/oracle/app/oradata/test/andy/[Link]
ONLINE 886046720
16 4
/oracle/app/oradata/test/andy/[Link]
ONLINE 5242880
17 0
/oracle/app/oradata/test/abc/[Link]
SYSTEM 849346560
18 1
/oracle/app/oradata/test/abc/[Link]
ONLINE 513802240
19 2
/oracle/app/oradata/test/abc/[Link]
ONLINE 886046720
20 4
/oracle/app/oradata/test/abc/[Link]
ONLINE 5242880
19 rows selected.
SQL> col name for a60
SQL> /
FILE# TS# NAME STATUS BYTES
---------- ---------- ------------------------------------------------------------
------- ----------
1 0 /oracle/app/oradata/test/[Link] SYSTEM
849346560
3 1 /oracle/app/oradata/test/[Link] ONLINE
513802240
4 2 /oracle/app/oradata/test/[Link] ONLINE
73400320
5 0 /oracle/app/oradata/test/pdbseed/[Link] SYSTEM
262144000
6 1 /oracle/app/oradata/test/pdbseed/[Link] ONLINE
346030080
7 4 /oracle/app/oradata/test/[Link] ONLINE
5242880
8 2 /oracle/app/oradata/test/pdbseed/[Link] ONLINE
104857600
9 0 /oracle/app/oradata/test/testpdb1/[Link] SYSTEM
262144000
10 1 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
367001600
11 2 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
104857600
12 5 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
5242880
13 0 /oracle/app/oradata/test/andy/[Link] SYSTEM
849346560
14 1 /oracle/app/oradata/test/andy/[Link] ONLINE
503316480
15 2 /oracle/app/oradata/test/andy/[Link] ONLINE
886046720
16 4 /oracle/app/oradata/test/andy/[Link] ONLINE
5242880
17 0 /oracle/app/oradata/test/abc/[Link] SYSTEM
849346560
18 1 /oracle/app/oradata/test/abc/[Link] ONLINE
513802240
19 2 /oracle/app/oradata/test/abc/[Link] ONLINE
886046720
20 4 /oracle/app/oradata/test/abc/[Link] ONLINE
5242880
19 rows selected.
SQL> 1
1* select file#, ts#, name, status, bytes from v$datafile
SQL> select file#, ts#, name, status, bytes, con_id from v$datafile;
FILE# TS# NAME STATUS BYTES
CON_ID
---------- ---------- ------------------------------------------------------------
------- ---------- ----------
1 0 /oracle/app/oradata/test/[Link] SYSTEM
849346560 1
3 1 /oracle/app/oradata/test/[Link] ONLINE
513802240 1
4 2 /oracle/app/oradata/test/[Link] ONLINE
73400320 1
5 0 /oracle/app/oradata/test/pdbseed/[Link] SYSTEM
262144000 2
6 1 /oracle/app/oradata/test/pdbseed/[Link] ONLINE
346030080 2
7 4 /oracle/app/oradata/test/[Link] ONLINE
5242880 1
8 2 /oracle/app/oradata/test/pdbseed/[Link] ONLINE
104857600 2
9 0 /oracle/app/oradata/test/testpdb1/[Link] SYSTEM
262144000 3
10 1 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
367001600 3
11 2 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
104857600 3
12 5 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
5242880 3
13 0 /oracle/app/oradata/test/andy/[Link] SYSTEM
849346560 6
14 1 /oracle/app/oradata/test/andy/[Link] ONLINE
503316480 6
15 2 /oracle/app/oradata/test/andy/[Link] ONLINE
886046720 6
16 4 /oracle/app/oradata/test/andy/[Link] ONLINE
5242880 6
17 0 /oracle/app/oradata/test/abc/[Link] SYSTEM
849346560 4
18 1 /oracle/app/oradata/test/abc/[Link] ONLINE
513802240 4
19 2 /oracle/app/oradata/test/abc/[Link] ONLINE
886046720 4
20 4 /oracle/app/oradata/test/abc/[Link] ONLINE
5242880 4
19 rows selected.
SQL>
SQL>
SQL> select file#, ts#, name, status, bytes, con_id from v$datafile order by
con_id;
FILE# TS# NAME STATUS BYTES
CON_ID
---------- ---------- ------------------------------------------------------------
------- ---------- ----------
3 1 /oracle/app/oradata/test/[Link] ONLINE
513802240 1
1 0 /oracle/app/oradata/test/[Link] SYSTEM
849346560 1
4 2 /oracle/app/oradata/test/[Link] ONLINE
73400320 1
7 4 /oracle/app/oradata/test/[Link] ONLINE
5242880 1
5 0 /oracle/app/oradata/test/pdbseed/[Link] SYSTEM
262144000 2
6 1 /oracle/app/oradata/test/pdbseed/[Link] ONLINE
346030080 2
8 2 /oracle/app/oradata/test/pdbseed/[Link] ONLINE
104857600 2
10 1 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
367001600 3
9 0 /oracle/app/oradata/test/testpdb1/[Link] SYSTEM
262144000 3
11 2 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
104857600 3
12 5 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
5242880 3
17 0 /oracle/app/oradata/test/abc/[Link] SYSTEM
849346560 4
18 1 /oracle/app/oradata/test/abc/[Link] ONLINE
513802240 4
19 2 /oracle/app/oradata/test/abc/[Link] ONLINE
886046720 4
20 4 /oracle/app/oradata/test/abc/[Link] ONLINE
5242880 4
13 0 /oracle/app/oradata/test/andy/[Link] SYSTEM
849346560 6
14 1 /oracle/app/oradata/test/andy/[Link] ONLINE
503316480 6
15 2 /oracle/app/oradata/test/andy/[Link] ONLINE
886046720 6
16 4 /oracle/app/oradata/test/andy/[Link] ONLINE
5242880 6
19 rows selected.
SQL>
SQL>
SQL> select file#, ts#, name, status, bytes/1024/1024 ,con_id from v$datafile order
by con_id;
FILE# TS# NAME STATUS
BYTES/1024/1024 CON_ID
---------- ---------- ------------------------------------------------------------
------- --------------- ----------
3 1 /oracle/app/oradata/test/[Link] ONLINE
490 1
1 0 /oracle/app/oradata/test/[Link] SYSTEM
810 1
4 2 /oracle/app/oradata/test/[Link] ONLINE
70 1
7 4 /oracle/app/oradata/test/[Link] ONLINE
5 1
5 0 /oracle/app/oradata/test/pdbseed/[Link] SYSTEM
250 2
6 1 /oracle/app/oradata/test/pdbseed/[Link] ONLINE
330 2
8 2 /oracle/app/oradata/test/pdbseed/[Link] ONLINE
100 2
10 1 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
350 3
9 0 /oracle/app/oradata/test/testpdb1/[Link] SYSTEM
250 3
11 2 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
100 3
12 5 /oracle/app/oradata/test/testpdb1/[Link] ONLINE
5 3
17 0 /oracle/app/oradata/test/abc/[Link] SYSTEM
810 4
18 1 /oracle/app/oradata/test/abc/[Link] ONLINE
490 4
19 2 /oracle/app/oradata/test/abc/[Link] ONLINE
845 4
20 4 /oracle/app/oradata/test/abc/[Link] ONLINE
5 4
13 0 /oracle/app/oradata/test/andy/[Link] SYSTEM
810 6
14 1 /oracle/app/oradata/test/andy/[Link] ONLINE
480 6
15 2 /oracle/app/oradata/test/andy/[Link] ONLINE
845 6
16 4 /oracle/app/oradata/test/andy/[Link] ONLINE
5 6
19 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------------------- ---------- --------- ------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
SQL> show con_id
CON_ID
------------------------------
1
SQL>
SQL>
SQL>
SQL> desc dba_tablespaces
Name
Null? Type
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
------- --------
-----------------------------------------------------------------------------------
---------------------------------
TABLESPACE_NAME
NOT NULL VARCHAR2(30)
BLOCK_SIZE
NOT NULL NUMBER
INITIAL_EXTENT
NUMBER
NEXT_EXTENT
NUMBER
MIN_EXTENTS
NOT NULL NUMBER
MAX_EXTENTS
NUMBER
MAX_SIZE
NUMBER
PCT_INCREASE
NUMBER
MIN_EXTLEN
NUMBER
STATUS
VARCHAR2(9)
CONTENTS
VARCHAR2(21)
LOGGING
VARCHAR2(9)
FORCE_LOGGING
VARCHAR2(3)
EXTENT_MANAGEMENT
VARCHAR2(10)
ALLOCATION_TYPE
VARCHAR2(9)
PLUGGED_IN
VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT
VARCHAR2(6)
DEF_TAB_COMPRESSION
VARCHAR2(8)
RETENTION
VARCHAR2(11)
BIGFILE
VARCHAR2(3)
PREDICATE_EVALUATION
VARCHAR2(7)
ENCRYPTED
VARCHAR2(3)
COMPRESS_FOR
VARCHAR2(30)
DEF_INMEMORY
VARCHAR2(8)
DEF_INMEMORY_PRIORITY
VARCHAR2(8)
DEF_INMEMORY_DISTRIBUTE
VARCHAR2(15)
DEF_INMEMORY_COMPRESSION
VARCHAR2(17)
DEF_INMEMORY_DUPLICATE
VARCHAR2(13)
SHARED
VARCHAR2(13)
DEF_INDEX_COMPRESSION
VARCHAR2(8)
INDEX_COMPRESS_FOR
VARCHAR2(13)
DEF_CELLMEMORY
VARCHAR2(14)
DEF_INMEMORY_SERVICE
VARCHAR2(12)
DEF_INMEMORY_SERVICE_NAME
VARCHAR2(1000)
LOST_WRITE_PROTECT
VARCHAR2(7)
CHUNK_TABLESPACE
VARCHAR2(1)
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------------------- ---------- --------- ------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
SQL>
SQL>
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL>
SQL> create table test (id number);
Table created.
SQL>
SQL> insert into test values (5);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> select * from test;
ID
----------
5
SQL> update test set id=1;
1 row updated.
SQL> select * from test;
ID
----------
1
SQL>
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL>
SQL> select * from test;
ID
----------
5
SQL>
SQL>
SQL> update test set id=1;
1 row updated.
SQL>
SQL> select * from test;
ID
----------
1
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> select * from test;
ID
----------
1
SQL>
SQL>
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from cdb_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------------------- ---------- --------- ------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
15 rows selected.
SQL> desc cdb_tablespaces
Name
Null? Type
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
------- --------
-----------------------------------------------------------------------------------
---------------------------------
TABLESPACE_NAME
NOT NULL VARCHAR2(30)
BLOCK_SIZE
NOT NULL NUMBER
INITIAL_EXTENT
NUMBER
NEXT_EXTENT
NUMBER
MIN_EXTENTS
NOT NULL NUMBER
MAX_EXTENTS
NUMBER
MAX_SIZE
NUMBER
PCT_INCREASE
NUMBER
MIN_EXTLEN
NUMBER
STATUS
VARCHAR2(9)
CONTENTS
VARCHAR2(21)
LOGGING
VARCHAR2(9)
FORCE_LOGGING
VARCHAR2(3)
EXTENT_MANAGEMENT
VARCHAR2(10)
ALLOCATION_TYPE
VARCHAR2(9)
PLUGGED_IN
VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT
VARCHAR2(6)
DEF_TAB_COMPRESSION
VARCHAR2(8)
RETENTION
VARCHAR2(11)
BIGFILE
VARCHAR2(3)
PREDICATE_EVALUATION
VARCHAR2(7)
ENCRYPTED
VARCHAR2(3)
COMPRESS_FOR
VARCHAR2(30)
DEF_INMEMORY
VARCHAR2(8)
DEF_INMEMORY_PRIORITY
VARCHAR2(8)
DEF_INMEMORY_DISTRIBUTE
VARCHAR2(15)
DEF_INMEMORY_COMPRESSION
VARCHAR2(17)
DEF_INMEMORY_DUPLICATE
VARCHAR2(13)
SHARED
VARCHAR2(13)
DEF_INDEX_COMPRESSION
VARCHAR2(8)
INDEX_COMPRESS_FOR
VARCHAR2(13)
DEF_CELLMEMORY
VARCHAR2(14)
DEF_INMEMORY_SERVICE
VARCHAR2(12)
DEF_INMEMORY_SERVICE_NAME
VARCHAR2(1000)
LOST_WRITE_PROTECT
VARCHAR2(7)
CHUNK_TABLESPACE
VARCHAR2(1)
CON_ID
NUMBER
SQL> 1
1* select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from cdb_tablespaces
SQL>
SQL>
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
, con_id from cdb_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN CON_ID
------------------------------ --------------------- ---------- --------- ------
----------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL 6
SYSAUX PERMANENT LOCAL SYSTEM AUTO
6
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL 6
TEMP TEMPORARY LOCAL UNIFORM MANUAL 6
USERS PERMANENT LOCAL SYSTEM AUTO 6
SYSTEM PERMANENT LOCAL SYSTEM MANUAL 1
SYSAUX PERMANENT LOCAL SYSTEM AUTO
1
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL 1
TEMP TEMPORARY LOCAL UNIFORM MANUAL 1
USERS PERMANENT LOCAL SYSTEM AUTO 1
SYSTEM PERMANENT LOCAL SYSTEM MANUAL 4
SYSAUX PERMANENT LOCAL SYSTEM AUTO
4
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL 4
TEMP TEMPORARY LOCAL UNIFORM MANUAL 4
USERS PERMANENT LOCAL SYSTEM AUTO 4
15 rows selected.
SQL>
SQL>
SQL>
SQL> show con_id
CON_ID
------------------------------
1
SQL>
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB1 MOUNTED
4 ABC READ WRITE NO
6 ANDY READ WRITE NO
SQL>
SQL>
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/pdbseed/[Link]
/oracle/app/oradata/test/pdbseed/[Link]
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/pdbseed/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
19 rows selected.
SQL>
SQL>
SQL> alter session set container=abc;
Session altered.
SQL>
SQL>
SQL> show cnon_id
SP2-0158: unknown SHOW option "cnon_id"
SQL>
SQL> show con_id
CON_ID
------------------------------
4
SQL>
SQL> show con_name
CON_NAME
------------------------------
ABC
SQL>
SQL>
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
SQL>
SQL>
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------
/oracle/app/oradata/test/abc/[Link]
SQL>
SQL>
SQL>
SQL>
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------------------- ---------- --------- ------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
SQL>
SQL>
SQL>
SQL> CREATE TABLESPACE AI DATAFILE '/oracle/app/oradata/test/abc/[Link]' SIZE 5M
autoextend on;
Tablespace created.
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------------------- ---------- --------- ------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
AI PERMANENT LOCAL SYSTEM AUTO
6 rows selected.
SQL>
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
SQL>
SQL>
SQL> alter tablespace ai add datafile '/oracle/app/oradata/test/abc/[Link]' size
5m;
Tablespace altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
6 rows selected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release [Link].0 - 64bit
Production
[oracle@rhps01 ~]$
[oracle@rhps01 ~]$ sqlplus / as sysdba
SQL*Plus: Release [Link].0 Production on Sun Jun 27 12:29:50 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release [Link].0 - 64bit Production
SQL> SQL>
SQL>
SQL>
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------------------- ---------- --------- ------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
SQL>
SQL>
SQL>
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL>
SQL>
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/pdbseed/[Link]
/oracle/app/oradata/test/pdbseed/[Link]
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/pdbseed/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
21 rows selected.
SQL> show con_id
CON_ID
------------------------------
1
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create undo tablespace undotbs2 datafile
'/oracle/app/oradata/test/undotbs2_01.dbf' size 10m;
Tablespace created.
SQL>
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------------------- ---------- --------- ------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
UNDOTBS2 UNDO LOCAL SYSTEM MANUAL
6 rows selected.
SQL>
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL> alter system set undo_tablespace='UNDOTBS2';
System altered.
SQL> how parameter undo
SP2-0734: unknown command beginning "how parame..." - rest of line ignored.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>
SQL>
SQL>
SQL> alter tablespace undotbs2 add datafile
'/oracle/app/oradata/test/undotbs2_02.dbf' size 5m;
Tablespace altered.
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/pdbseed/[Link]
/oracle/app/oradata/test/pdbseed/[Link]
/oracle/app/oradata/test/[Link]
/oracle/app/oradata/test/pdbseed/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
/oracle/app/oradata/test/testpdb1/[Link]
NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/andy/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/abc/[Link]
/oracle/app/oradata/test/undotbs2_01.dbf
NAME
--------------------------------------------------------------------------------
/oracle/app/oradata/test/undotbs2_02.dbf
23 rows selected.
SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/oracle/app/oradata/test/temp2_01.dbf' SIZE 10M; 2
Tablespace created.
SQL>
SQL>
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------------------- ---------- --------- ------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
UNDOTBS1 UNDO LOCAL SYSTEM MANUAL
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
UNDOTBS2 UNDO LOCAL SYSTEM MANUAL
TEMP2 TEMPORARY LOCAL UNIFORM MANUAL
7 rows selected.
SQL>
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL> select
TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT
from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ --------------------- ---------- --------- ------
SYSTEM PERMANENT LOCAL SYSTEM MANUAL
SYSAUX PERMANENT LOCAL SYSTEM AUTO
TEMP TEMPORARY LOCAL UNIFORM MANUAL
USERS PERMANENT LOCAL SYSTEM AUTO
UNDOTBS2 UNDO LOCAL SYSTEM MANUAL
TEMP2 TEMPORARY LOCAL UNIFORM MANUAL
6 rows selected.
SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
SQL> select * from database_properties where property_name like '%DEFAULT%';
PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TBS_TYPE
SMALLFILE
Default tablespace type
DEFAULT_EDITION
ORA$BASE
Name of the database default edition
PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_PERMANENT_TABLESPACE
USERS
Name of default permanent tablespace
DEFAULT_TEMP_TABLESPACE
TEMP
PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
Name of default temporary tablespace
SQL> select * from database_properties where property_name like '%DEFAULT';
no rows selected
SQL> select * from database_properties where property_name like 'DEFAULT%';
PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TBS_TYPE
SMALLFILE
Default tablespace type
DEFAULT_EDITION
ORA$BASE
Name of the database default edition
PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_PERMANENT_TABLESPACE
USERS
Name of default permanent tablespace
DEFAULT_TEMP_TABLESPACE
TEMP
PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
Name of default temporary tablespace
SQL>
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------------- ------------- --------- ------------ --------- ----------
1 1 19 209715200 512 1 YES
INACTIVE 1817176 24-JUN-21 1924283 27-JUN-21 0
2 1 20 209715200 512 1 YES
INACTIVE 1924283 27-JUN-21 1968214 27-JUN-21 0
3 1 21 209715200 512 1 NO
CURRENT 1968214 27-JUN-21 1.8447E+19 0
SQL> set pages 300 lines 300
SQL> /
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---
---------------- ------------- --------- ------------ --------- ----------
1 1 19 209715200 512 1 YES INACTIVE
1817176 24-JUN-21 1924283 27-JUN-21 0
2 1 20 209715200 512 1 YES INACTIVE
1924283 27-JUN-21 1968214 27-JUN-21 0
3 1 21 209715200 512 1 NO CURRENT
1968214 27-JUN-21 1.8447E+19 0
SQL>
SQL>
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/app/oradata/test/arch
Oldest online log sequence 19
Next log sequence to archive 21
Current log sequence 21
SQL>
SQL>
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---
---------------- ------------- --------- ------------ --------- ----------
1 1 22 209715200 512 1 NO CURRENT
1993576 27-JUN-21 1.8447E+19 0
2 1 20 209715200 512 1 YES INACTIVE
1924283 27-JUN-21 1968214 27-JUN-21 0
3 1 21 209715200 512 1 YES ACTIVE
1968214 27-JUN-21 1993576 27-JUN-21 0
SQL> /
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---
---------------- ------------- --------- ------------ --------- ----------
1 1 22 209715200 512 1 NO CURRENT
1993576 27-JUN-21 1.8447E+19 0
2 1 20 209715200 512 1 YES INACTIVE
1924283 27-JUN-21 1968214 27-JUN-21 0
3 1 21 209715200 512 1 YES ACTIVE
1968214 27-JUN-21 1993576 27-JUN-21 0
SQL> alter system switch logfile;
System altered.
SQL>
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---
---------------- ------------- --------- ------------ --------- ----------
1 1 22 209715200 512 1 YES ACTIVE
1993576 27-JUN-21 1993657 27-JUN-21 0
2 1 23 209715200 512 1 NO CURRENT
1993657 27-JUN-21 1.8447E+19 0
3 1 21 209715200 512 1 YES ACTIVE
1968214 27-JUN-21 1993576 27-JUN-21 0
SQL>
SQL>
SQL>
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
---------------------------------------------------
IS_ CON_ID
--- ----------
3 ONLINE
/oracle/app/oradata/test/[Link]
NO 0
2 ONLINE
/oracle/app/oradata/test/[Link]
NO 0
1 ONLINE
/oracle/app/oradata/test/[Link]
NO 0
SQL> col member for a60
SQL> /
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- -------
------------------------------------------------------------ --- ----------
3 ONLINE /oracle/app/oradata/test/[Link] NO
0
2 ONLINE /oracle/app/oradata/test/[Link] NO
0
1 ONLINE /oracle/app/oradata/test/[Link] NO
0
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> ALTER DATABASE ADD LOGFILE GROUP 4
('/oracle/app/oradata/test/redo04_a.log','/oracle/app/oradata/test/redo04_b.log')
SIZE 5M;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---
---------------- ------------- --------- ------------ --------- ----------
1 1 22 209715200 512 1 YES INACTIVE
1993576 27-JUN-21 1993657 27-JUN-21 0
2 1 23 209715200 512 1 NO CURRENT
1993657 27-JUN-21 1.8447E+19 0
3 1 21 209715200 512 1 YES INACTIVE
1968214 27-JUN-21 1993576 27-JUN-21 0
4 1 0 5242880 512 2 YES UNUSED
0 0 0
SQL>
SQL>
SQL>
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- -------
------------------------------------------------------------ --- ----------
3 ONLINE /oracle/app/oradata/test/[Link] NO
0
2 ONLINE /oracle/app/oradata/test/[Link] NO
0
1 ONLINE /oracle/app/oradata/test/[Link] NO
0
4 ONLINE /oracle/app/oradata/test/redo04_a.log NO
0
4 ONLINE /oracle/app/oradata/test/redo04_b.log NO
0
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oracle/app/oradata/test/redo01_b.log' TO
GROUP 1;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- -------
------------------------------------------------------------ --- ----------
3 ONLINE /oracle/app/oradata/test/[Link] NO
0
2 ONLINE /oracle/app/oradata/test/[Link] NO
0
1 ONLINE /oracle/app/oradata/test/[Link] NO
0
4 ONLINE /oracle/app/oradata/test/redo04_a.log NO
0
4 ONLINE /oracle/app/oradata/test/redo04_b.log NO
0
1 INVALID ONLINE /oracle/app/oradata/test/redo01_b.log NO
0
6 rows selected.
SQL>
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- ---
---------------- ------------- --------- ------------ --------- ----------
1 1 22 209715200 512 2 YES INACTIVE
1993576 27-JUN-21 1993657 27-JUN-21 0
2 1 23 209715200 512 1 NO CURRENT
1993657 27-JUN-21 1.8447E+19 0
3 1 21 209715200 512 1 YES INACTIVE
1968214 27-JUN-21 1993576 27-JUN-21 0
4 1 0 5242880 512 2 YES UNUSED
0 0 0
You might also like
- RAC Database From 12.1 To 12.2 - WorkshopNo ratings yetRAC Database From 12.1 To 12.2 - Workshop27 pages
- Knowledge - Base - Converting Physical Standby To Snapshot StandbyNo ratings yetKnowledge - Base - Converting Physical Standby To Snapshot Standby9 pages
- Step 1: Confirm Database Name and Identify Tablespace Name To Be Used For DR TestNo ratings yetStep 1: Confirm Database Name and Identify Tablespace Name To Be Used For DR Test10 pages
- ASM Instance Setup and Configuration GuideNo ratings yetASM Instance Setup and Configuration Guide7 pages
- SQL Training: Database Management Essentials100% (1)SQL Training: Database Management Essentials36 pages
- Restore and Recover A Lost System Datafile Oracle CDB - 19cNo ratings yetRestore and Recover A Lost System Datafile Oracle CDB - 19c4 pages
- Oracle Flashback Log Management ExplainedNo ratings yetOracle Flashback Log Management Explained3 pages
- SQL Select FORCE - LOGGING From V$database For - NO SQL Archive LogNo ratings yetSQL Select FORCE - LOGGING From V$database For - NO SQL Archive Log11 pages
- Data Pump Backup and Recovery TechniquesNo ratings yetData Pump Backup and Recovery Techniques10 pages
- Database Backup and Decommission ReportNo ratings yetDatabase Backup and Decommission Report42 pages
- Step by Step Guide On Creating Physical Standby UsingNo ratings yetStep by Step Guide On Creating Physical Standby Using14 pages
- DBaaS CLI Commands for Database ManagementNo ratings yetDBaaS CLI Commands for Database Management6 pages
- Oracle 12c Software and Database Installation GUINo ratings yetOracle 12c Software and Database Installation GUI48 pages
- Time-Based Incomplete User Managed RecoveryNo ratings yetTime-Based Incomplete User Managed Recovery5 pages
- DBA Class File From Muhammad Nur E Alam For DDD 60 (20 Jan 2025)No ratings yetDBA Class File From Muhammad Nur E Alam For DDD 60 (20 Jan 2025)10 pages
- Oracle 10g Database Backup & Recovery GuideNo ratings yetOracle 10g Database Backup & Recovery Guide54 pages
- Ec Grade 10 Phys Sci 2026 National Annual Teaching Plan Eng FinalNo ratings yetEc Grade 10 Phys Sci 2026 National Annual Teaching Plan Eng Final10 pages
- Bonus Payment Guidelines and Key SectionsNo ratings yetBonus Payment Guidelines and Key Sections71 pages
- Crow's Foot Gang War: Rise of Black CrowsNo ratings yetCrow's Foot Gang War: Rise of Black Crows4 pages
- 04 & 05-Property Relations and Tax Credit & Distributable EstateNo ratings yet04 & 05-Property Relations and Tax Credit & Distributable Estate11 pages
- Age-Wise Hip Joint Blood Supply AnalysisNo ratings yetAge-Wise Hip Joint Blood Supply Analysis35 pages
- Ebook & Testbank Advanced Financial Accounting 11th Ed ChristensenNo ratings yetEbook & Testbank Advanced Financial Accounting 11th Ed Christensen234 pages