Storage Management in Oracle -3
In article we will learn creating tablespaces with storage parameters(DEMO).
Before reading this article please check previous articles.
Demo On Storage Management :
To view information about storage management use DBA_TABLESPACES or V$TABLESPACE
|
1 2 3 4 5 6 7 8 9 10 11 |
SYS>> desc dba_tablespaceselect TABLESPACE_NAME,EXTENT_MANAGEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces; TS-Name Extent-Man Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc ---------- ---------- ---------- ---------- ---------- ---------- ---------- SYSTEM DICTIONARY 16384 16384 1 505 50 SYSAUX LOCAL 65536 1 2147483645 UNDOTBS1 LOCAL 65536 1 2147483645 TEMP LOCAL 1048576 1048576 1 0 USERDATA LOCAL 65536 1 2147483645 TEST LOCAL 65536 1 2147483645 6 rows selected. |
Note :
If you have system tablespace as Dictionary then only Oracle allows us to create dictionary management tablespaces.
|
1 2 3 4 5 6 |
[oracle@primary ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 9 09:26:37 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options |
Check instance information
|
1 2 3 4 5 |
SYS>>select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PRIMARY - primary@primary.orcl.com - primary - 12.1.0.2.0 - READ WRITE - 02-FEB-2019 10:39:14 - PRIMARY |
Lets create tablespace with dictionary Management
|
1 2 |
SYS>>create tablespace dmts1 datafile '/u01/oradata/primary/dmts1.dbf' size 10m extent management dictionary; Tablespace created. |
Check again DBA_TABLESPACES
|
1 2 3 4 5 6 7 8 9 10 11 |
SYS>>select TABLESPACE_NAME,EXTENT_MANAGEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces; TS-Name Extent-Man Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc ---------- ---------- ---------- ---------- ---------- ---------- ---------- SYSTEM DICTIONARY 16384 16384 1 505 50 SYSAUX LOCAL 65536 1 2147483645 UNDOTBS1 LOCAL 65536 1 2147483645 TEMP LOCAL 1048576 1048576 1 0 USERDATA LOCAL 65536 1 2147483645 TEST LOCAL 65536 1 2147483645 DMTS1 DICTIONARY 40960 40960 1 505 50 7 rows selected. |
We can create dictionary management tablespace with storage parameters like INITIAL,NEXT,MINEXTENTS etc…,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SYS>>create tablespace dmts2 datafile '/u01/oradata/primary/dmts2.dbf' size 10m extent management dictionary default storage(initial 120k next 60k minextents 1 maxextents 30 pctincrease 40); Tablespace created. ------CHECK INFORMATION SYS>>select TABLESPACE_NAME,EXTENT_MANAGEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces; TS-Name Extent-Man Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc ---------- ---------- ---------- ---------- ---------- ---------- ---------- SYSTEM DICTIONARY 16384 16384 1 505 50 SYSAUX LOCAL 65536 1 2147483645 UNDOTBS1 LOCAL 65536 1 2147483645 TEMP LOCAL 1048576 1048576 1 0 USERDATA LOCAL 65536 1 2147483645 TEST LOCAL 65536 1 2147483645 DMTS1 DICTIONARY 40960 40960 1 505 50 DMTS2 DICTIONARY 122880 65536 1 30 40 8 rows selected. |
Lets create user and assign user to dictionary managed tablespace.
|
1 2 3 4 5 |
SYS>>grant dba to DMU identified by DMU; Grant succeeded. SYS>>alter user DMU default tablespace dmts2; User altered. |
Connect to DMU user and insert some data.
|
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 |
SYS>>conn DMU/dmu Connected. DMU>>create table t1(sno number); Table created. DMU>>insert into test values(&sno); Enter value for sno: 10 old 1: insert into test values(&sno) new 1: insert into test values(10) 1 row created. Enter value for sno: 20 old 1: insert into test values(&sno) new 1: insert into test values(20) 1 row created. DMU>>/ Enter value for sno: 30 old 1: insert into test values(&sno) new 1: insert into test values(30) 1 row created. DMU>>commit; Commit complete. DMU>>select * from test; SNO ---------- 10 20 30 |
We can check information whether the table has stored as per storage parameters or not using below statement.
|
1 2 3 4 5 |
DMU>>select TABLE_NAME,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables; TABLE_NAME TS-Name Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc ------------ ---------- ---------- ---------- ---------- ---------- -------- TEST DMTS2 122880 65536 1 30 40 |
The table also stores as per dictionary management tablespace dmts2 values .
Now we will create table with manual storage values.
|
1 2 3 4 5 6 7 8 9 10 11 |
A1>>create table t2(sno number) storage (initial 80k next 40k pctincrease 50); Table created. DMU>>select TABLE_NAME,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from user_tables; TABLE_NAME TS-Name Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc -------------------- ---------- ---------- ---------- ---------- ---------- ---------- TEST DMTS2 122880 65536 1 30 40 TEST1 DMTS2 81920 40960 1 30 50 |
Finally we understood that we can assign storage parameter values while creating the table and also tablespace .
Converting Dictionary Management tablespaces to Local :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SYS>>select TABLESPACE_NAME,EXTENT_MANAGEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces; TS-Name Extent-Man Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc ---------- ---------- ---------- ---------- ---------- ---------- ---------- SYSTEM DICTIONARY 16384 16384 1 505 50 SYSAUX LOCAL 65536 1 2147483645 UNDOTBS1 LOCAL 65536 1 2147483645 TEMP LOCAL 1048576 1048576 1 0 USERDATA LOCAL 65536 1 2147483645 0 TEST LOCAL 65536 1 2147483645 DMTS1 DICTIONARY 40960 40960 1 505 50 DMTS2 DICTIONARY 122880 65536 1 30 40 8 rows selected. |
Execute Pl*SQL packaged procedure to change Dictionary Management tablespaces to Local :
|
1 2 3 |
SYS>>exec dbms_space_admin.tablespace_migrate_to_local('DMTS2'); PL/SQL procedure successfully completed. |
Now check changes
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SYS>>select TABLESPACE_NAME,EXTENT_MANAGEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces; TS-Name Extent-Man Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc ---------- ---------- ---------- ---------- ---------- ---------- ---------- SYSTEM DICTIONARY 16384 16384 1 505 50 SYSAUX LOCAL 65536 1 2147483645 UNDOTBS1 LOCAL 65536 1 2147483645 TEMP LOCAL 1048576 1048576 1 0 USERDATA LOCAL 65536 1 2147483645 0 TEST LOCAL 65536 1 2147483645 DMTS1 DICTIONARY 40960 40960 1 505 50 DMTS2 DICTIONARY 122880 65536 1 30 40 8 rows selected. |
Converting Local Management tablespace to Dictionary :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SYS>>exec dbms_space_admin.tablespace_migrate_from_local('DMTS2'); PL/SQL procedure successfully completed. SYS>>select TABLESPACE_NAME,EXTENT_MANAGEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces; TS-Name Extent-Man Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc ---------- ---------- ---------- ---------- ---------- ---------- ---------- SYSTEM DICTIONARY 16384 16384 1 505 50 SYSAUX LOCAL 65536 1 2147483645 UNDOTBS1 LOCAL 65536 1 2147483645 TEMP LOCAL 1048576 1048576 1 0 USERDATA LOCAL 65536 1 2147483645 TEST LOCAL 65536 1 2147483645 DMTS1 DICTIONARY 40960 40960 1 505 50 DMTS2 DICTIONARY 122880 65536 1 30 40 |
Thank you …..
Note: Please test scripts in Non Prod before trying in Production.
Loading...
