show user
/*
USER is "PDB10_ADMIN"
*/
show con_name
/*
CON_NAME
------------------------------
PDB10
*/
create tablespace ts1;
/*
Tablespace TS1 created.
*/
# 오라클에서 자동으로 설정되는 옵션들은 아래와 같다
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','TS1') FROM dual;
/*
CREATE TABLESPACE "TS1" DATAFILE
SIZE 104857600
AUTOEXTEND ON NEXT
*/
/*
CREATE TABLESPACE "TS1" DATAFILE
SIZE 104857600
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
*/
select * from dba_tablespaces where tablespace_name='TS1' ;
# 추가된 테이블 스페이스와 데이터 파일 정보
select TS#, status, enabled, name from v$datafile;
/*
TS# STATUS ENABLED NAME .. ..
----- ------- ---------- ---------..----------------------..------------------------------
0 SYSTEM READ WRITE /u01/app/../PDB10/system01.dbf ..
1 ONLINE READ WRITE /u01/app/../PDB10/sysaux01.dbf ..
2 ONLINE READ WRITE /u01/app/../PDB10/undotbs01.dbf ..
5 ONLINE READ WRITE /u01/app/../PDB10/users01.dbf ..
10 ONLINE READ WRITE /u01/app/../PDB10/ORCL/0BD9769D27..B/datafile/o1_mf_ts1_lqjm98f3_.dbf
*/
--REMEMBER THE RELATION
--TABLESPACE-SEGMENT-EXTENT-BLOCK
# 새로운 사용자에게 테이블 스페이스를 할당
CREATE USER alex identified by alex
default tablespace ts1
/*
User ALEX created.
*/
grant create session, create table, unlimited tablespace to alex;
/*
Grant succeeded.
*/
select username, account_status, created, profile from dba_users
where username='ALEX';
/*
USERNAME ACCOUNT_STATUS CREATED PROFILE
---------- --------------- --------- ----------
ALEX OPEN 12-DEC-23 DEFAULT
*/
CREATE TABLE ALEX.EMP ( EMP_ID NUMBER, NAME VARCHAR2(100), NOTE VARCHAR2(1000) );
/*
Table ALEX.EMP created.
*/
SELECT owner, table_name, tablespace_name FROM DBA_TABLES
WHERE TABLE_NAME='EMP' AND OWNER='ALEX';
/*
OWNER TABLE_NAME TABLESPACE_NAME
---------- -------------------- --------------------
ALEX EMP TS1
*/
# 테이블을 만들었지만 데이터가 없으므로 SEGMENTS, EXTENTS 에서 조회가 되지 않는다
SELECT owner, segment_name, bytes, blocks, extents, header_file, header_block FROM DBA_SEGMENTS
WHERE OWNER='ALEX' AND SEGMENT_NAME='EMP';
-- SO ORACLE DID NOT CREATE SEGMENT, NO ROWS IN THE TABLE
/*
no rows selected
*/
INSERT INTO ALEX.EMP VALUES (1,'KHALED','the best teacher ever : )');
commit;
/*
1 row inserted.
Commit complete.
*/
# 테이블에 데이터를 삽입한 후 SEGMENTS, EXTENTS에 데이터 용량에 대한 정보가 조회된다
SELECT owner, segment_name, bytes, blocks, extents, header_file, header_block FROM DBA_SEGMENTS
WHERE OWNER='ALEX' AND SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS HEADER_FILE HEADER_BLOCK
---------- --------------- ---------- ---------- ---------- ----------- ------------
ALEX EMP 65536 8 1 55 130
*/
select owner, segment_name, extent_id, bytes, blocks from dba_extents
where owner='ALEX'
and SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME EXTENT_ID BYTES BLOCKS
---------- --------------- ---------- ---------- ----------
ALEX EMP 0 65536 8
*/
--now let us insert 1000 rows
begin
for i in 1..1000
loop
insert into ALEX.EMP values (i,'just test name', 'just test comments');
end loop;
commit;
end;
/*
PL/SQL procedure successfully completed.
*/
# 데이터가 추가된 용량에 따라서 DBA_SEGMENTS.BYTES 값 및 DBA_SEGMENTS.EXTENTS 값이 증가된다
SELECT owner, segment_name, bytes, blocks, extents, header_file, header_block FROM DBA_SEGMENTS
WHERE OWNER='ALEX' AND SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS HEADER_FILE HEADER_BLOCK
---------- --------------- ---------- ---------- ---------- ----------- ------------
ALEX EMP 131072 16 2 55 130
*/
# 데이터가 추가된 용량에 따라서 dba_extents.EXTENT_ID 값 및 BYTES, BLOCKS 값이 증가된다
select owner, segment_name, extent_id, bytes, blocks from dba_extents
where owner='ALEX' and SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME EXTENT_ID BYTES BLOCKS
---------- --------------- ---------- ---------- ----------
ALEX EMP 0 65536 8
ALEX EMP 1 65536 8
*/
--now let us insert 1000000 rows
begin
for i in 1..1000000
loop
insert into ALEX.EMP values (i,'just test name', 'just test comments');
end loop;
commit;
end;
/*
PL/SQL procedure successfully completed.
*/
# 데이터가 추가된 용량에 따라서 DBA_SEGMENTS.BYTES 값 및 DBA_SEGMENTS.EXTENTS 값이 증가된다
SELECT owner, segment_name, bytes, blocks, extents, header_file, header_block FROM DBA_SEGMENTS
WHERE OWNER='ALEX' AND SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS HEADER_FILE HEADER_BLOCK
---------- --------------- ---------- ---------- ---------- ----------- ------------
ALEX EMP 51380224 6272 64 55 130
*/
# 데이터가 추가된 용량에 따라서 dba_extents.EXTENT_ID 값 및 BYTES, BLOCKS 값이 증가된다
select owner, segment_name, extent_id, bytes, blocks from dba_extents
where owner='ALEX' and SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME EXTENT_ID BYTES BLOCKS
---------- --------------- ---------- ---------- ----------
ALEX EMP 0 65536 8
ALEX EMP 1 65536 8
ALEX EMP 2 65536 8
....
ALEX EMP 61 1048576 128
ALEX EMP 62 1048576 128
ALEX EMP 63 1048576 128
64 rows selected.
*/
select rowid,emp_id,name,note from ALEX.emp
where emp_id=1000000
--example
--rowid OOOOOOFFFBBBBBBRRR
--OOOOOO object number
--FFF Relative File Number
--BBBBBB The data block number
--RRR The row number
/*
ROWID EMP_ID NAME NOTE
------------------ ---------- -------------------- --------------------
AAAR1PAA3AAABhGABS 1000000 just test name just test comments
*/
delete from ALEX.EMP;
commit;
/*
1,001,001 rows deleted.
Commit complete.
*/
# 데이터를 삭제한다고 사용중인 데이터 공간이 반환되지 않는다
SELECT owner, segment_name, bytes, blocks, extents, header_file, header_block FROM DBA_SEGMENTS
WHERE OWNER='ALEX' AND SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS HEADER_FILE HEADER_BLOCK
---------- --------------- ---------- ---------- ---------- ----------- ------------
ALEX EMP 51380224 6272 64 55 130
*/
select owner, segment_name, extent_id, bytes, blocks from dba_extents
where owner='ALEX' and SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME EXTENT_ID BYTES BLOCKS
---------- --------------- ---------- ---------- ----------
ALEX EMP 0 65536 8
ALEX EMP 1 65536 8
ALEX EMP 2 65536 8
....
ALEX EMP 61 1048576 128
ALEX EMP 62 1048576 128
ALEX EMP 63 1048576 128
64 rows selected.
*/
truncate table ALEX.EMP;
/*
Table ALEX.EMP truncated.
*/
# truncate 명령어로 테이블을 삭제해야 사용중인 데이터 공간이 줄어든다
SELECT owner, segment_name, bytes, blocks, extents, header_file, header_block FROM DBA_SEGMENTS
WHERE OWNER='ALEX' AND SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME BYTES BLOCKS EXTENTS HEADER_FILE HEADER_BLOCK
---------- --------------- ---------- ---------- ---------- ----------- ------------
ALEX EMP 65536 8 1 55 130
*/
select owner, segment_name, extent_id, bytes, blocks from dba_extents
where owner='ALEX'
and SEGMENT_NAME='EMP';
/*
OWNER SEGMENT_NAME EXTENT_ID BYTES BLOCKS
---------- --------------- ---------- ---------- ----------
ALEX EMP 0 65536 8
*/
