- Alter tablespace and datafile
show user;
/*
USER is "PDB10_ADMIN"
*/
show con_name;
/*
CON_NAME
------------------------------
PDB10
*/
[oracle@test datafile]$ pwd
/u01/app/oracle/oradata/ORCL/PDB10/ORCL/0BD9769D27CD39DCE063E102A8C059FB/datafile
[oracle@test datafile]$ ll
합계 102408
-rw-r----- 1 oracle oinstall 104865792 12월 11 16:05 o1_mf_t1_lqg7x03o_.dbf
create tablespace t3;
/*
Tablespace T3 created.
*/
[oracle@test datafile]$ ll
합계 204816
-rw-r----- 1 oracle oinstall 104865792 12월 11 16:05 o1_mf_t1_lqg7x03o_.dbf
-rw-r----- 1 oracle oinstall 104865792 12월 11 16:34 o1_mf_t3_lqggj6jk_.dbf
[oracle@test datafile]$
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','T3') FROM dual;
/*
CREATE TABLESPACE "T3" DATAFILE
SIZE 104857600
AUTOEXTEND ON NEXT 1
*/
select TABLESPACE_NAME,BLOCK_SIZE,STATUS,CONTENTS,LOGGING,SEGMENT_SPACE_MANAGEMENt,COMPRESS_FOR
from dba_tablespaces;
/*
TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS LOGGING SEGMEN COMPRESS_FOR
------------------ ---------- --------- ----------- --------- ------ --------------
SYSTEM 8192 ONLINE PERMANENT LOGGING MANUAL
SYSAUX 8192 ONLINE PERMANENT LOGGING AUTO
UNDOTBS1 8192 ONLINE UNDO LOGGING MANUAL
TEMP 8192 ONLINE TEMPORARY NOLOGGING MANUAL
USERS 8192 ONLINE PERMANENT LOGGING AUTO
T1 8192 ONLINE PERMANENT LOGGING AUTO
T3 8192 ONLINE PERMANENT LOGGING AUTO
7 rows selected.
*/
select * from v$tablespace
/*
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
6 T1 YES NO YES 8
8 T3 YES NO YES 8
0 SYSTEM YES NO YES 8
1 SYSAUX YES NO YES 8
2 UNDOTBS1 YES NO YES 8
3 TEMP NO NO YES 8
5 USERS YES NO YES 8
7 rows selected.
*/
--select * from v$datafile
select TS#, status, enabled, name, status from v$datafile
/*
TS# STATUS ENABLED NAME .. ...
---------- ------- ---------- -----..----------------------------...----------------------------
0 SYSTEM READ WRITE /u01/../ORCL/PDB10/system01.dbf ...
1 ONLINE READ WRITE /u01/../ORCL/PDB10/sysaux01.dbf ...
2 ONLINE READ WRITE /u01/../ORCL/PDB10/undotbs01.dbf ...
5 ONLINE READ WRITE /u01/../ORCL/PDB10/users01.dbf ...
6 ONLINE READ WRITE /u01/../ORCL/PDB10/ORCL/0BD9769D27C...ile/o1_mf_t1_lqg7x03o_.dbf
8 ONLINE READ WRITE /u01/../ORCL/PDB10/ORCL/0BD9769D27C...ile/o1_mf_t3_lqggj6jk_.dbf
6 rows selected.
*/
CREATE USER pdb10_test identified by pdb10_test
default tablespace t3;
/*
User PDB10_TEST created.
*/
grant create session,create table, unlimited tablespace to pdb10_test;
/*
Grant succeeded.
*/
create table pdb10_test.emp( id number, name varchar2(200));
/*
Grant succeeded.
*/
insert into pdb10_test.emp values (1,'ford');
insert into pdb10_test.emp values (2,'sara');
insert into pdb10_test.emp values (3,'ali');
commit;
/*
1 row inserted.
1 row inserted.
1 row inserted.
Commit complete.
*/
ALTER TABLESPACE T3 READ ONLY;
/*
TABLESPACE T3 altered.
*/
select * from v$datafile
select TS#, status, enabled, name from v$datafile
/*
TS# STATUS ENABLED NAME ... ..
---------- ------- ---------- ---------...----------------------------..----------------------------------------
0 SYSTEM READ WRITE /u01/app/.../ORCL/PDB10/system01.dbf ..
1 ONLINE READ WRITE /u01/app/.../ORCL/PDB10/sysaux01.dbf ..
2 ONLINE READ WRITE /u01/app/.../ORCL/PDB10/undotbs01.dbf ..
5 ONLINE READ WRITE /u01/app/.../ORCL/PDB10/users01.dbf ..
6 ONLINE READ WRITE /u01/app/.../ORCL/PDB10/ORCL/0BD9769D27C..DB/datafile/o1_mf_t1_lqg7x03o_.dbf
8 ONLINE READ ONLY /u01/app/.../ORCL/PDB10/ORCL/0BD9769D27C..DB/datafile/o1_mf_t3_lqggj6jk_.dbf
6 rows selected.
*/
insert into pdb10_test.emp values (4,'DAVE');
/*
Error starting at line : 138 in command -
insert into pdb10_test.emp values (4,'DAVE')
Error at Command Line : 138 Column : 24
Error report -
SQL Error: ORA-00372: file 52 cannot be modified at this time
ORA-01110: data file 52: '/u01/app/oracle/oradata/ORCL/PDB10/ORCL/0BD9769D27CD39DCE063E102A8C059FB/datafile/o1_mf_t3_lqggj6jk_.dbf'
00372. 00000 - "file %s cannot be modified at this time"
*Cause: attempting to modify the contents of a file that cannot be
modified. The file is most likely part of a read only tablespace
but may be in the process of going offline, or the database may
be in the process of closing.
*Action: check the status of the file and its tablespace
*/
ALTER TABLESPACE T3 READ WRITE;
/*
TABLESPACE T3 altered.
*/
insert into pdb10_test.emp values (4,'DAVE');
COMMIT;
/*
1 row inserted.
Commit complete.
*/
select * from v$datafile;
select TS#, status, enabled, name from v$datafile
/*
TS# STATUS ENABLED NAME .. ..
---------- ------- ---------- ---------..----------------------------..------------------------------------
0 SYSTEM READ WRITE /u01/app/../ORCL/PDB10/system01.dbf ..
1 ONLINE READ WRITE /u01/app/../ORCL/PDB10/sysaux01.dbf ..
2 ONLINE READ WRITE /u01/app/../ORCL/PDB10/undotbs01.dbf ..
5 ONLINE READ WRITE /u01/app/../ORCL/PDB10/users01.dbf ..
6 ONLINE READ WRITE /u01/app/../ORCL/PDB10/ORCL/0BD9769D27C..FB/datafile/o1_mf_t1_lqg7x03o_.dbf
8 ONLINE READ WRITE /u01/app/../ORCL/PDB10/ORCL/0BD9769D27C..FB/datafile/o1_mf_t3_lqggj6jk_.dbf
6 rows selected.
*/
select TS#, name, bytes, create_bytes from v$datafile;
/*
TS# NAME .. .. BYTES CREATE_BYTES
------ ---------..---------------------------..--------------------------------------- ---------- ------------
0 /u01/app/../ORCL/PDB10/system01.dbf .. 283115520 283115520
1 /u01/app/../ORCL/PDB10/sysaux01.dbf .. 356515840 346030080
2 /u01/app/../ORCL/PDB10/undotbs01.dbf .. 104857600 104857600
5 /u01/app/../ORCL/PDB10/users01.dbf .. 5242880 5242880
6 /u01/app/../ORCL/PDB10/ORCL/0BD9769D27..B/datafile/o1_mf_t1_lqg7x03o_.dbf 104857600 104857600
8 /u01/app/../ORCL/PDB10/ORCL/0BD9769D27..B/datafile/o1_mf_t3_lqggj6jk_.dbf 104857600 104857600
6 rows selected.
*/
--LET US REZIE THE DATAFIE
ALTER DATABASE DATAFILE
'/u01/app/oracle/oradata/ORCL/PDB10/ORCL/0BD9769D27CD39DCE063E102A8C059FB/datafile/o1_mf_t3_lqggj6jk_.dbf'
RESIZE 200M;
/*
Database altered.
*/
select TS#, name, bytes, create_bytes from v$datafile;
/*
TS# NAME BYTES CREATE_BYTES
-------- ----------------------------------------------------------------------------- ---------- ------------
0 /u01/app/../ORCL/PDB10/system01.dbf 283115520 283115520
1 /u01/app/../ORCL/PDB10/sysaux01.dbf 356515840 346030080
2 /u01/app/../ORCL/PDB10/undotbs01.dbf 104857600 104857600
5 /u01/app/../ORCL/PDB10/users01.dbf 5242880 5242880
6 /u01/app/../ORCL/PDB10/ORCL/0BD9769D27...B/datafile/o1_mf_t1_lqg7x03o_.dbf 104857600 104857600
8 /u01/app/../ORCL/PDB10/ORCL/0BD9769D27...B/datafile/o1_mf_t3_lqggj6jk_.dbf 209715200 104857600
6 rows selected.
*/
ALTER TABLESPACE T3
ADD DATAFILE '/u01/app/oracle/oradata/ORCL/PDB10/ORCL/T3_02.dbf' SIZE 10M
/*
TABLESPACE T3 altered.
*/
select * from v$datafile
select TS#, status, enabled, name from v$datafile
/*
TS# STATUS ENABLED NAME .. ..
----- ------- ---------- ---------..---------------------------..---------------------------------------
0 SYSTEM READ WRITE /u01/app/../ORCL/PDB10/system01.dbf ..
1 ONLINE READ WRITE /u01/app/../ORCL/PDB10/sysaux01.dbf ..
2 ONLINE READ WRITE /u01/app/../ORCL/PDB10/undotbs01.dbf ..
5 ONLINE READ WRITE /u01/app/../ORCL/PDB10/users01.dbf ..
6 ONLINE READ WRITE /u01/app/../ORCL/PDB10/ORCL/0BD9769D27..B/datafile/o1_mf_t1_lqg7x03o_.dbf
8 ONLINE READ WRITE /u01/app/../ORCL/PDB10/ORCL/0BD9769D27..B/datafile/o1_mf_t3_lqggj6jk_.dbf
8 ONLINE READ WRITE /u01/app/../ORCL/PDB10/ORCL/T3_02.dbf ..
7 rows selected.
*/
