1. How Many TS inside database?
1024
v$tablespace
SQL> select NAME from v$tablespace;
NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
2. How many datafiles inside single TS?
1024
v$datafile
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------
USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_ntwl11pb_.dbf
UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ntwl10n5_.dbf
SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ntwkz4g1_.dbf
SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ntwl07kk_.dbf
3. How to map each TS and associated datafile?
dba_data_files;
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------
USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_ntwl11pb_.dbf
UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ntwl10n5_.dbf
SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ntwkz4g1_.dbf
SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ntwl07kk_.dbf
4. max size of datafile
- 32GB (8k)
5. Tablespace is full?
- check all datafiles and are all datafiles reached 32GB
- Yes then add new datafile
6. fragmentation (select query taking more time) - 10%
Cause: caused by lots delete operation then HWM is reached block header and we can not use deleted space within a block
Solution: Table move, Table shrink, Export & Import of a table
7. HWM?
logical indicator of used data in a block
- Inserts will always goes about HWM
- Read or select queries will always goes below HWM
8. What data block(8k)
9. Extents
10. Segments
11. Tablesapce
12. datafiles
tablespaces (datafiles) -> segments -> extents -> data block(8k)
13. row piece
14. How to find tablespace utilization
set pages 1000 lines 1000
col tablespace_name format a16;
col file_name format a80;
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "In MB"
FROM DBA_DATA_FILES;
SQL> set pages 1000 lines 1000
col tablespace_name format a16;
col file_name format a80;
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "In MB"
FROM DBA_DATA_FILES;SQL> SQL> SQL> 2
TABLESPACE_NAME FILE_NAME In MB
---------------- -------------------------------------------------------------------------------- ----------
USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_ntwl11pb_.dbf 5
UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ntwl10n5_.dbf 340
SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ntwkz4g1_.dbf 900
SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ntwl07kk_.dbf 540
Mallik -> USERS -> user.dbf
user.dbf - 5MB used - 32GB free
Mallik user is trying to add data but he is getting Tablespace USER is full.
- strange
- engage DBA to add new datafile into USER TS
set pages 1000 lines 1000
col tablespace_name format a16;
col file_name format a80;
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "In MB",AUTOEXTENSIBLE
FROM DBA_DATA_FILES;
15. AUTOEXTENSIBLE
default any datafile is created which will create with 100MB size when this 100MB is full then it will increase by 200MB
16. OMF - oracle managed files
create tablespace TEST1; (OMF)
- default name is given for datafile
- default initial size is defined for datafile - 100MB
- once this 100MB is full automatically oracle can assign 100MB/200MB each
- autoextetion
set pages 1000 lines 1000
col tablespace_name format a16;
col file_name format a80;
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "In MB",AUTOEXTENSIBLE
FROM DBA_DATA_FILES;SQL> SQL> SQL> 2
TABLESPACE_NAME FILE_NAME In MB AUT
---------------- -------------------------------------------------------------------------------- ---------- ---
USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_ntwl11pb_.dbf 5 YES
UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ntwl10n5_.dbf 340 YES
SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ntwkz4g1_.dbf 900 YES
SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ntwl07kk_.dbf 540 YES
TEST1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_nvkom8j3_.dbf 100 YES
17. noautoextention or Non-OMF
create tablespace TEST2
datafile '/u01/app/oracle/oradata/DEVDB/datafile/test2.dbf'
size 200M;
SQL> set pages 1000 lines 1000
col tablespace_name format a16;
col file_name format a80;
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "In MB",AUTOEXTENSIBLE
FROM DBA_DATA_FILES;SQL> SQL> SQL> 2
TABLESPACE_NAME FILE_NAME In MB AUT
---------------- -------------------------------------------------------------------------------- ---------- ---
USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_ntwl11pb_.dbf 5 YES
UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ntwl10n5_.dbf 340 YES
SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ntwkz4g1_.dbf 900 YES
SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ntwl07kk_.dbf 540 YES
TEST1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_nvkom8j3_.dbf 100 YES
TEST2 /u01/app/oracle/oradata/DEVDB/datafile/test2.dbf 200 NO
user1 -> TEST1 TS
- create table TABLE1
- insert data of 100MB
- further user1 can insert data of 1G
user2 -> TEST2 TS
- create table TABLE2
- insert data of 100MB
- further user2 can try to insert data of 1G - which will fail
user2 will engage DBA
- As a DBA I will check whether datafile is AUTOEXTENSIBLE ON/OFF
- I can increate the datafile size
alter database
datafile '/u01/app/oracle/oradata/DEVDB/datafile/test2.dbf'
resize 1G;
alter database
datafile '/u01/app/oracle/oradata/DEVDB/datafile/test2.dbf'
resize 32G;
alter database
datafile '/u01/app/oracle/oradata/DEVDB/datafile/test2.dbf'
AUTOEXTEND ON;
- As a DBA I will check whether datafile is reached 32GB or not
- I can add new datafile
ORA-01686 max #file 10
17. OMF / Non-OMF
create tablespace TEST1; (OMF)
create tablespace TEST2
datafile '/u01/app/oracle/oradata/DEVDB/datafile/test2.dbf'
size 200M;
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata
alter system set db_create_file_dest='';
create tablespace TEST3;
create tablespace TEST3 datafile '/u01/app/oracle/oradata/DEVDB/datafile/test3.dbf' size 100M;
alter system set db_create_file_dest='/u01/app/oracle/oradata';
18. Row chaining and row migration:
row migration:
===============
Cause: Future update is unable to keep the row piece within the same block and we end up with moving the row piece into new block
Solution: pct free 20% or 30%
ALTER TABLE EMP PCTFREE 20;
row chaining:
===============
Cause: Row piece size (16K) is bigger than data block size (8k)
Solution: By creating bigger block size tablespace and move those tables
TEST TS (8k) -> Mallik -> EMP (row piece 16k)
- create TEST2 TS with 16K size
- move EMP table from TEST TS to TEST2 TS
19. Big file tablespace
my database OLTP - banking - DML - best / default block size is 8k
Because of this 8k block size -> datafile size is 32GB
- is there way can i create bigger datafile even though block size is 8k
create tablespace TEST1; -> test1.dbf
8k -> DF size 32GB
create bigfile tablespace TEST3; -> test3.dbf
8k -> DF size 32TB
8K -> small file 32GB -> big file 32TB
16K -> small file 64GB -> big file 64TB
32K -> small file 128GB -> big file 128TB
create tablespace TEST5
datafile '/u01/app/oracle/oradata/DEVDB/datafile/test5.dbf'
size 33G;
create bigfile tablespace TEST5
datafile '/u01/app/oracle/oradata/DEVDB/datafile/test5.dbf'
size 33G;
OLTP - (banking, retail, finance many more)
OLAP - Datawarehouse - select or reporting query (100TB / 500TB)
small file
++++++++++++++++++
OLAP - 200TB - 2,04,800 GB
How many datafiles?
- 6,400
OLAP - 400TB - 4,09,600 GB
How many datafiles?
- 12,800
Big file
++++++++++++++++++++
OLAP - 200TB - 2,04,800 GB
How many datafiles?
- 7
OLAP - 400TB - 4,09,600 GB
How many datafiles?
- 13
20. How to identify bigfile or small file?
We can create a tablespace with bigfile as a key-word
SQL> SQL> set pages 1000 lines 1000
col tablespace_name format a16;
col file_name format a80;
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "In MB",AUTOEXTENSIBLE
FROM DBA_DATA_FILES;SQL> SQL> SQL> 2
TABLESPACE_NAME FILE_NAME In MB AUT
---------------- -------------------------------------------------------------------------------- ---------- ---
USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_ntwl11pb_.dbf 5 YES
UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ntwl10n5_.dbf 340 YES
SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ntwkz4g1_.dbf 900 YES
SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ntwl07kk_.dbf 540 YES
TEST1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_nvkom8j3_.dbf 100 YES
TEST2 /u01/app/oracle/oradata/DEVDB/datafile/test2.dbf 1024 YES
TEST5 /u01/app/oracle/oradata/DEVDB/datafile/test5.dbf 33792 NO
SQL> select NAME, BIGFILE from v$tablespace;
NAME BIG
------------------------------ ---
SYSAUX NO
SYSTEM NO
UNDOTBS1 NO
USERS NO
TEMP NO
TEST1 NO
TEST2 NO
TEST5 YES
8k:
++++++
small file ts -> we can add multiple datafile up to 1024
32GB X 1024 datafile = 32,768 GB = 32TB
big file -> only single datafile
32TB X 1 datafile = 32TB
block size = 8k
create tablespace TEST1 - small file (32GB) - OLTP
- create tablespace TEST1;
- We can add 32GB * 1024 datafiles = 32TB
create bigfile tablespace TEST2 - big file (32TB) - OLAP
- create bigfile tablespace TEST2
- Big file tablespace supports only singe datafile inside tablespace
- We can add 32TB * 1 datafiles = 32TB
create tablespace TEST1;
create bigfile tablespace TEST2;
8k -> DF size 32GB DF size 32TB
16k -> DF size 64GB DF size 64TB
32k -> DF size 128GB DF size 128TB
Regards,
Mallikarjun / Vismo Technologies
WhatsApp: +91 9880616848 / +91 9036478079
Cell: +91 9880616848 / +91 9036478079
Email: mallikarjun.ramadurg@gmail.com / vismotechnologies@gmail.com
No comments:
Post a Comment