-1

i am stuck at creating a dictionary management tablespace in 11g Release 2 Oracle 32 bit , I can't create a normal tablespace with dictionary management because my System tablespace is locally management. Is there any way to create System Tablespace to be dictionary management tablespace? I tried to create new database with database configuration assistant in oracle but can't find any step to create tablespace system with dictionary management.i noticed that they say

The DICTIONARY keyword is deprecated. It is still supported for backward compatibility.

in https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7003.htm#i2096553

I am really confuse about this line. i cant understand it fully , i think there is still a way to create dictionary management, because we have ways to convert from local to dictionary and vice versa but if the system tablespace is locally management,we can't do that convert . I am just a newbie in Oracle. Can you guys give me some advices please?

closed as unclear what you're asking by Philᵀᴹ, hot2use, Max Vernon, mustaccio, Mr.Brownstone Nov 22 '18 at 20:57

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Why do you want dictionary managed tablespaces? – miracle173 Nov 20 '18 at 12:51
  • 2
    You don't need to create a dictionary managed tablespace. Sometimes you have to take a step back and think about why you are doing something – Philᵀᴹ Nov 20 '18 at 12:53
2

CREATE DATABASE

extent_management_clause::=

enter image description here

Use this clause to create a locally managed SYSTEM tablespace. If you omit this clause, then the SYSTEM tablespace will be dictionary managed.

So to create a database with a dictionary mananged SYSTEM tablespace on 11.2:

[oracle@o61 ~]$ cat $ORACLE_HOME/dbs/init$ORACLE_SID.ora
*.audit_file_dest='/u01/app/oracle/admin/D112/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/D112/control01.ctl','/oradata/D112/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='D112'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=D112XDB)'
*.filesystemio_options='setall'
*.open_cursors=300
*.pga_aggregate_target=268435456
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1073741824
*.undo_tablespace='UNDOTBS1'
[oracle@o61 ~]$

[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 20 11:58:41 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             331350920 bytes
Database Buffers          729808896 bytes
Redo Buffers                5517312 bytes

Create the database manually (I commented the EXTENT MANAGEMENT LOCAL clause):

CREATE DATABASE D112
   USER SYS IDENTIFIED BY Oracle123
   USER SYSTEM IDENTIFIED BY Oracle123
   LOGFILE GROUP 1 ('/oradata/D112/redo01a.log') SIZE 100M BLOCKSIZE 512,
           GROUP 2 ('/oradata/D112/redo02a.log') SIZE 100M BLOCKSIZE 512,
           GROUP 3 ('/oradata/D112/redo03a.log') SIZE 100M BLOCKSIZE 512
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   --EXTENT MANAGEMENT LOCAL
   DATAFILE '/oradata/D112/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/oradata/D112/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/oradata/D112/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/oradata/D112/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs1
      DATAFILE '/oradata/D112/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Database created.

SQL>

Now check the SYSTEM tablespace. At this point we do not have any dictionary views, so I need to query the dictionary table directly:

SQL> select name, decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL') from ts$ ts;

NAME                           DECODE(TS.
------------------------------ ----------
SYSTEM                         DICTIONARY
SYSAUX                         LOCAL
UNDOTBS1                       LOCAL
TEMPTS1                        LOCAL
USERS                          LOCAL

Then create a dictionary managed tablespace:

SQL> create tablespace dict_ts datafile '/oradata/D112/dictts01.dbf' size 100M 
     extent management dictionary;

Tablespace created.

SQL> select name, decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL') from ts$ ts;

NAME                           DECODE(TS.
------------------------------ ----------
SYSTEM                         DICTIONARY
SYSAUX                         LOCAL
UNDOTBS1                       LOCAL
TEMPTS1                        LOCAL
USERS                          LOCAL
DICT_TS                        DICTIONARY

6 rows selected.

But don't do this, just use locally managed tablespaces.

  • Can i ask that do we need to shutdown or dellete initial database when we installed first then do this ? because i have error says: database already mounted ? – Van Nov 20 '18 at 12:59
  • @Van I created a new database in the above example. If you shut down your database then start the instance in NOMOUNT mode, you will be able to do this, but that way you lose your current database. Just create a new one with a new instance. – Balazs Papp Nov 20 '18 at 13:37
  • Hey i have some problems after i created database like your instruction but i can;t use dba anymore , it keep saying error dba table or view does not exist. I tried to grant dba to 2 user sys and system but it 's not working. What is that problem ? – Van Nov 21 '18 at 6:49
  • @Van The above is an incomplete database, but it is good enough to demonstrate what I wanted. If you want to create a fully functional database using this method, follow the instructions at: docs.oracle.com/cd/E11882_01/server.112/e25494/… – Balazs Papp Nov 21 '18 at 7:59
0

I started working with Oracle in 7.1.4. It was a 16 bit version of Oracle that ran on Netware 4.1. Back then all table spaces were dictionary managed and the number of extents an object had were tied to the block size for the database. If someone created a database with a 2k block size there were limited to 121 extents for that object. If they picked an 8k block size they could possibly go to 512 extents, not sure of the number. It became difficult to manage larger databases, because you had to be conscious of extent sizes and fragmentation was a concern.

Once locally managed table spaces became the default, we had the option of letting Oracle manage extent sizes or using uniform extent sizes and DBA's could focus on more important things than managing extent sizes. There was a pdf that came out around 2000 which summarized the need to let Oracle manage extent sizes.

HOW TO STOP DEFRAGMENTING AND START LIVING: THE DEFINITIVE WORD ON FRAGMENTATION

If you are new to Oracle, there is enough to learn without going back to using dictionary managed table spaces. You should look and RMAN backup and recovery and analytics as well as changes to the optimizer in versions 12cR2 and 18c.

Not the answer you're looking for? Browse other questions tagged or ask your own question.