Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Syntax for increasing SYSTEM TABLESPACE size

Patch Notes 7.4.1.1 say to increase SYSTEM TABLESPACE to minimum 10 MB, but don't give syntax. Can you help me increase the SYSTEM TABLESPACE size to 100 MB?

I installed patch 10.1.0.4 (4163362) on Win2000/SP4 server. Patch Notes 7.4.1.1 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.

  1. Can you help me increase the SYSTEM TABLESPACE size to 100 MB?
  2. How can I tell whether or not a tablespace is dictionary-managed? And what does "dictionary-managed" mean?

Thanks.

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close