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. 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 ~ Madrid. key{ adm con sto } Reclaiming Unused Space in Datafiles. There are a number of scenarios that can lead to unused space in datafiles. The two most common I see are: A lack of housekeeping/maintenance means that one or more tables have grown excessively. After the data is pruned the datafiles contain unused space that needs to be reclaimed. One or more segments (tables, partitions or indexes) have been moved to another tablespace leaving empty areas in the datafiles that previously held them. In this article I will discuss a few of the ways of reclaiming this unused space.
Note. Setup Test Environment Before we can look at the solutions we need to create a test environment so we can clearly see the problem. CONN / AS SYSDBA -- Create a tablespace and user for the test. We can see both table segments are made up of multiple extents, each extent being made up of multiple blocks. Enterprise Manager gives us a nice image of the contents of the tablespace by doing the following: Click on the "Server" tab. Identify Tablespaces with Free Space Export/Import.
Reducing datafile size to recover free space – Oracle Database 10g « IN ORACLE MILIEU … At time, we want to recover some space from database just to allocate the same to some other tablespace or to return it back to OS disk. This situation arises many times. And many time we hit with error “ORA-03297: file contains used data beyond requested RESIZE value“. The concept is simple and many of you must be knowing, but just putting in a simpler words. Lets take an example of one of the datafile in a database. Lets see the total free space in a datafile 194.
SQL> select sum(bytes)/1024/1024 from dba_free_space 2 where tablespace_name = ‘APPS_TS_TX_DATA’ 3 and file_id = 194; Now lets see the distribution for the file. SQL> select file_id, block_id, blocks, bytes, ‘Free’ from dba_free_space 2 where tablespace_name = ‘APPS_TS_TX_DATA’ 3 and file_id = 194 4 and rownum < 7 5 order by block_id desc; We can see that there are so many blocks which are free.
You might be wondering that after block 35001, we have all free space and also we have free space at blocks 13401, 13417, 13433 etc. HWM (high watermark) of a tablespace ? How many times have you thought of calculating HWM (high watermark) of a tablespace as a DBA ? Have you ever found out that a tablespace is overallocated in a database, as compared to the data being used in it ? In such a case, you'd want to reduce the wasted space & resize the tablespace to a smaller size, although greater than the current usage.
HWM is at the maximum blocks ever occupied by a SEGMENT (unless we perform a truncate on it). Segments span over datafiles however remain within a tablespace. Set verify offcolumn file_name format a50 word_wrappedcolumn smallest format 999,990 heading "Smallest|Size|Poss. " You may save this above data output as a snapshot of starting point. Dear reader, I've been able to make use of this procedure successfully. How to adjust the high watermark in ORACLE 10g – ALTER TABLE SHRINK « lutz hartmann as sysdba. Hanging around in my hotel in Chicago because it is raining, I have been cruising around in the OTN forums which inspired me to write something about the High Watermark and the Oracle 10gR1 New Feature SEGMENT SHRINKING.
The High Watermark is the maximum fill-grade a table has ever reached. Above the high watermark are only empty blocks. These blocks can be formatted or unformatted. First let’s have a look at the question when space is allocated - when you create a table at least one extent (contiguous blocks) is allocated to the table - if you have specified MINEXTENTS the number of MINEXTENTS extents will be allocated immedaitely to the table - if you have not specified MINEXTENTS then exactely one extent will be allocated (we will look at extent sizes later in another post).
Immediately after creation of the segment (table) the high watermark will be at the first block of the first extent as long as there are no inserts made. Let’s asume that we have filled a table with 100’0000 rows. High Watermark (was White watermark)" Submitted on 15-Oct-2007 15:43 UTC Last updated 22-Apr-2013 18:55 You Asked What is the main purpose of white watermark and in a case secnario where all the records have been deleted and we try to lookup that table why does the data is looked untill it reaches to an end of whte watermark and we said... I believe you mean "high watermark" The database doesn't know what is on a block unless and until...... It goes to the block. So, when you delete the information, the block is still "a block", it is just a block that once had active rows - but no longer does. And when you full scan the table - we have to read all blocks that at any time contained data - because - they could contain data now.
Suppose you deleted all but ONE row - we have to look in every block (unless of course we use an index) to see if that row is on a given block. <quote src=expert oracle database architecture> High-water Mark This is a term used with table segments stored in the database. Figure 10-1. Oracle: Tablespace verwalten. Konfiguration von Tablespaces[Bearbeiten] Nach der Installation von Oracle gibt es nur den SYSTEM-Tablespace (bei Oracle Version < 10g), ab Oracle Version 10g gibt es mind. den SYSTEM und den SYSAUX Tablespace. Es ist empfehlenswert, die System-Daten und die Anwendungsdaten in getrennten Tablespaces zu speichern. Für die Systemdaten kann man eigene Tablespaces einrichten für Datadictionary (existiert schon)ToolsRollback-SegmenteSort-Bereich (temporärer Tablespace) Für die Anwendungsdaten kann man Tablespaces einrichten für die einzelnen FachgebieteIndices Neuen Tablespace erstellen[Bearbeiten] CREATE TABLESPACE user_ts DATAFILE 'c:\oracle\oradata\ora\userts.dbf' SIZE 10M; Der Tablespace wird mit einer festen Größe von 10MB angelegt.
CREATE TABLESPACE user_ts DATAFILE 'c:\oracle\oradata\ora\userts.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K; Die Extent-Verwaltung erfolgt bei diesem Tablespace lokal im Tablespace durch ein BITMAP und nicht über das Datadictionary. 1. 2. 3. Oder: 4. 5. Reclaiming Unused Space in Datafiles.