I installed patch 10.1.0.4 (4163362) on Win2000/SP4 server. Patch Notes 126.96.36.199 say to increase SYSTEM TABLESPACE...
to minimum 10 MB, but don't give syntax. B10759-01 SQL Ref manual gives diagrams but not specific syntax.
- Can you help me increase the SYSTEM TABLESPACE size to 100 MB?
- How can I tell whether or not a tablespace is dictionary-managed? And what does "dictionary-managed" mean?
The readme for that patch states that if JServer is part of the installation you should ensure that you have at least 10 MB free in SYSTEM. The syntax to do this from SQL*Plus is:
select sum(bytes) from dba_free_space where tablespace='SYSTEM';
This will tell you how much space SYSTEM has available versus allocated.
If you need to add 100 MB to SYSTEM, the syntax is as follows if you have room to expand the current datafile from 200 MB to 300 MB for example (note that you will have to modify the datafile name for your environment):
alter database datafile 'c:oradata01system01.dbf' resize 300 MB;
If you cannot expand the current datafile then you will need to add another datafile. Here is the syntax to do that (note that you will have to modify the datafile name for your environment):
alter tablespace SYSTEM add datafile 'c:oradata01system02.dbf' size 100 MB;
You can tell whether or not a tablespace is locally or dictionary-managed by running the following SQL query:
select tablespace_name, extent_management from dba_tablespaces;
From the Oracle9i Database Administrator's Guide Release 2 (9.2): "Dictionary-managed tablespaces rely on data dictionary tables to track space utilization. Beginning with Oracle8i, you were able to create locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. These locally managed tablespaces provide better performance and greater ease of management."
There are a number of advantages to using locally managed tablespaces. Here are a few:
- Because space information is kept in the datafile header, there is less contention with the data dictionary tables.
- There is no coalescing required because local management of extents automatically tracks adjacent free space.
- Extent sizes are managed locally and determined automatically.
- There is reduced fragmentation.
Dig Deeper on Oracle database installation, upgrades and patches
Related Q&A from Maria Anderson
I have been trying to install Oracle 8.1.7 on SUSE Linux 9.0 and got the error: "Error in invoking target install of makefile /opt/oracle/...../*.mk." Continue Reading
Can I use /var/opt/oracle/oratab to specify listener information? Continue Reading
How many database instances can be created on an Oracle server with an installation drive containing 15 GB free space? Continue Reading