Home » Posts tagged 'Tablespace'

Tag Archives: Tablespace

Oracle 9i : Some of limitation on Oracle 9i

Physical Database Limits

Item Type of Limit Limit Value
Database Block Size Minimum 2048 bytes; must be a multiple of operating system physical block size
Maximum Operating system dependent; never more than 32 KB
Database Blocks Minimum in initial extent of a segment. 2 blocks
Maximum per datafile Platform dependent; typically 222-1 blocks
Controlfiles Number of control files 1 minimum; 2 or more (on separate devices) strongly recommended
Size of a control file Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files Maximum per tablespace Operating system dependent; usually 1022
Maximum per database 65533

May be less on some operating systems

Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance

Database extents Maximum 4 GB, regardless of the maximum file size allowed by the operating system
Database file size Maximum Operating system dependent. Limited by maximum operating system file size; typically 222 or 4M blocks
MAXEXTENTS Default value Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
Maximum Unlimited
Redo Log Files Maximum number of logfiles Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement

Control file can be resized to allow more entries; ultimately an operating system limit

Maximum number of logfiles per group Unlimited
Redo Log File Size Minimum size 50 KB
Maximum size Operating system limit; typically 2 GB
Tablespaces Maximum number per database 64 K

Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file


source (http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch43.htm#287916)

Oracle 9i : Max size of datafiles

Data files are not exactly unlimited in size, so the term “Unlimited” refers to the ceiling your datafile is able to reach, and it depends on the Oracle Block Size. To find the absolute maximum file size multiply block size by 4194303. This is the actual maximum size. You may want to read the Metalink Note:112011.1.

A datafile cannot be oversized, otherwise it could get corrupted. Let’s say if your database is 8k blocks that means that one file can not exceed approximately 34GB (34,359,730,176 bytes) without having database corruption.

Sizing datafiles is a matter of manageability, it depends on your storage, the amount of space allocated in a single managed storage unit.

128G is the maximum datafile size in 10g, but considering the maximum number of datafiles a Database can have, it can make a database to potentially size 8E (exabytes = 8,388,608 T).

The maximum data file size is calculated by:
Maximum datafile size = db_block_size * maximum number of blocks

The maximum amount of data in an Oracle database is calculated by:
Maximum database size = maximum datafile size * maximum number of datafile

The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.

The maximum amount of data for a 32K block size database is eight petabytes (8,192 Terabytes) in Oracle9i.

Maximum database size is 8Pb in Oracle9i & 10g (Small file Tablespaces).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

2,048 8 512 4,096 16 1,024 8,192 32 2,048 16,384 64 4,096 32,768 128 8,192

The maximum database size is 8Eb in Oracle 10g (Big file tablespaces).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

2,048 8,192 524,264 4,096 16,384 1,048,528 8,192 32,768 2,097,056 16,384 65,536 4,194,112 32,768 131,072 8,388,224


Table Reorganization using BRSPACE via temporary tablespace

On my previous tutorial, you have already learned how to create new tablespace. I done it for this purpose. As you see, I already created PSAPIDS_REORG tablespace.

You can check it via DB02 tcode. Now, I want to reorg MSEG table. Right now, MSEG table is located on PSAPIDS tablespace. I check it by double click-in PSAPIDS from above screen. and write down MSEG on Object Name field like this.

Press the green check (v).

Here is information about MSEG table I get.

Now, open your MS-DOS prompt (coz I am using Windows based IDES server) and using [sid]adm user.

Type this command : >brspace -f tbreorg -t MSEG -m PSAPIDS_REORG

This command will reorg MSEG table and move it to PSAPIDS_REORG table.

Confirmation required to start reorganization process.

Table reorganization status starting.

Reorganization process has completed successfully.

To make sure that MSEG table has been moved to PSAPIDS_REORG tablespace, check again using DB02 tcode and check on PSAPIDS_REORG tablespace.

Now, you can see that MSEG table has been moved to PSAPIDS_REORG. You can start reorganize othe table. For your safety, use it on offline mode (no user access SAP server).

Create New Tablespace on SAP R3 server using BRTOOLS

By default, SAP R3 4.7 has 6 tablespace. You can check them using tcode  DB02. I already show you how to extend tablespace that already exist.

Now I want to show step by step adding new tablespace. I am going to use BRTOOLS, to add new tablespace. Open your MS-DOS prompt using user [sid]adm. Use ora[sid] user for your UNIX box.

To add new tablespace, choose option number 2 Space Management. Then ENTER.

Choose option number 2 to Create Tablespace. Then ENTER.

Enter option number 3 to propose tablespace name. Tablespace name must have prefix PSAPIDS (for my IDES box). Depend on your box. Here, I named it PSAPIDS_REORG (Coz I want to use it later for reorganization purpose).

After that, enter C then ENTER.

Tablespace creation is going to execute. You do not net to shutdown your SAP R3 nor Oracle DB to do this. You can do this online.

Additional information you need to supply such as datafile size, increment size, etc. Then type c to continue.

Tablespace creation is running. Wait for couple minutes.

Tablespace PSAPIDS_REORG has been created successfully. Type C to continue.

Back to Space Management menu. Next step, check the result using DB02 tcode.

You can see that tablespace PSAPIDS_REORG has been added in tablespace list.

Congratulations ! Now, you can continue your coffee break time :D…

Simple Table Reorganization for SAP R/3 using BRSPACE

SAP R/3 are ERP software that relied on database performance to support its performance. High performance database directly effect on SAP R/3 system performance. One of activity that effect on its are table reorganization. Reorganization improves the structure of the database and can result in improved database performance. BRSPACE performs the reorganization using the new Oracle feature, online table redefinition. The following graphic shows some of the reasons for reorganization:

One of the reason to reorganization are that large disk and RAID system with large and secure memory buffers reduce I/O hotspot. You can check more about reorganization for SAP R/3 here.

I’ll use my Windows box and use [sid]adm user. For Unix box user, use ora[sid] user. Let’s do simple table reorganization here. Open your MS-DOS prompt.

Type > brspace -f tbreorg -t MSEG

Explanation :

  • -f means function to do table reorganization
  • -t means refer to table  name. On this tutorial, MSEG table.


Additional option available for table reorganization. Just accept default option. Type C to continue.

Table reorganization is running.

Table reorganization has been completed succesfully. Type C to continue.

Now you can try to reoganization simple and little table. This tutorial only show you simple and online reorganization. Be carefull when you are using it. Test it first on your Develop system or Test system.

Extending your SAP R/3 tablespaces

I am glad that a lot of Basis visiting my blog, asking me about Basis activity, tips, etc. Based on comments and email about how to extend SAP R/3 tablespace, I try to post this little notes. On this little tutorial, I am using Windows based SAP R/3 and Oracle 9.2. You can do it on UNIX based. Of course using different user. When you using UNIX  based R/3 system, you have to use ora[sid] user.

Login to your SAP R/3 DB instance Windows using [sid]adm user. Open your command prompt.

Type BRTOOLS to enter BRTOOLS menu.

Choose menu number 2 by typing 2 and then ENTER.

There are many menus on SPACE Management including Extend tablespace, Create tablespace, etc.

Type 1 to Extend tablespace. To get information about tablespace list, you can use DB02 on your SAPGUI screen. For this tutorial, I want to extend PSAPIDS tablespace.

Type c to continue this step.

On this screen, you can see information about last added tablespace including datafile size, datafile name, etc. I use default (datafile size about 2470 MB). Type C to continue.

If you want to add more datafile, you can specified now by typing y. Right now, I just want to add 1 datafile so I type c.

Extending tablespace process is running.

Tablespace PSAPIDS has been extended successfully. Type c to continue.

Back to BRTOOLS menus. Type 9 and ENTER to exit BRTOOLS program.

To check your new datafile has been added to your tablespace you can use DB02 and then press REFRESH button.

Now, you can extend your tablespace easily and have a nice day.

NB : You don’t need to shutdown your SAP R/3 system or Oracle DB to extend tablespace. You can do this online while your SAP R/3 system and Oracle DB running.

Checking SAP R/3 tablespaces

SAP R/3 are ERP software using large database. Usually commercial databases such Oracle, DB2, Informix is used as its database server. Mine using Oracle

SAP tcode DB02 is use to check database size, tablespaces size, current statistic, checking freespace statistic, analyze detailed tablespace, checking missing indexes, and space critical objects. Actually, we can do more than that.

SAP R/3 installation created 6 tablespaces by default. Here are name of their tablespaces :

  • PSAP[SID]620

I am using SAP R/3 Enterprises 4.7 ext 200 version. It may be different if you are using old or new ones.

SAP R/3 data is saved on PSAP[SID] tablespace. So, we need to managed this tablespace carefully.

When you type DB02,

Press Space Statistic button to find out tablespace size and its growth. Tablespace’s growth can be seen daily, weekly, or monthly.

To check tablespace current size, just click Current Size button.

Click on PSAP[SID] tablespace and then, click Freespace Analysis.

If your PSAP[SID] tablespace size is reaching 95% level, you should consider increasing its size. You can use BRTOOLS to increase tablespace size. We’ll talk about BRTOOLS later.