![oracle 10g tablespace fragmentation oracle 10g tablespace fragmentation](https://docs.rackspace.com/blog/manage-the-sysaux-tablespace/Picture2.png)
Shrinking the file is possible but the location of segments in the file is critical. Space that the data file uses on storage. This will also change the location of the segment in the data file, which can be relevant for.
Oracle 10g tablespace fragmentation free#
Truncating a table shrinks the segments by default, or if a segment has a lot of free space in it you can move the table and/or rebuild the indexes. Deleting rows is generally enough to free up space for new data in that segment, but that does not free up space that the segment occupies in the data file. The space used by rows in table and index segments. There are basically three levels of space allocation to think about. So am I able to perform a defragmentation operation on a datafile somehow? Without defragmentation I get an exception: ORA-03297: file contains used data beyond requested RESIZE value I truncated several tables there (or I know there's much free space in the datafile, it's allocated but not occupated by data, were used before for some operations) and now need to defragment MAIN tablespace's datafile in order to decrease it's size. Now I do it like this: truncate table schema_name.log_table Īlter database datafile '/path/to/the/log/datafile/log1.dbf' resize 128M Īnd it works, because truncate gets rid of all the information in the datafile.īut what if I came across a situation when I need to free some size for LOG tablespace from MAIN one. Since I use express edition of oracle, I need them to be not more than 4GB in sum.Īnd it happens sometimes when I need to reduce the size of LOG datafile in order to free some space for necessary data. Both datafiles maxsize is set to 4GB, their initial sizes are 8MB, and they autoextend on next 8MB. So there are two tablespaces MAIN and LOG. It's done by storing log tables (user-defined program log, don't mix up with system log) in a different tablespace from the main one. How Oracle 10g makes life easier in a day to day a.In my system permanent data is separated from some temporary (like log).
![oracle 10g tablespace fragmentation oracle 10g tablespace fragmentation](https://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/img/xedba006.gif)
In my next post, I will be discussing few of other useful dictionary and dynamic views of Oracle10gR2. SELECT instance_name,startup_time,version If you want to query the history (usage of any partitcular tablespace),you need to play around with RTIME column.Īs per my knowledge, prior to 10g, I don't see any dictionary view or table keeps the history of database startup time and version.ġ0g, provides, DBA_HIST_DATABASE_INSTANCE which keeps the log about database startup time and the version. TABLESPACE_SIZE TABLESPACE_USEDSIZE RTIME Select tablespace_size,tablespace_usedsize,rtime TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENTĭBA_HIST_TBSPC_SPACE_USAGE - This dictionary table contains the history information about tablespace usage metrics, each row contains 1 hr historical information. Select * from dba_tablespace_usage_metrics (for me, I dont get the real values in this view.) In earlier version, it required to join few dictionary views and some computationto know the used percent of any tablespace.10g, makes life easier by providing all them in a view, dba_tablespace_usage_metrics. IDLE_TIME 609528 (value measured by hundredths of a second)ĭBA_HIST_OSSTAT displays historical operating system statistics.ĭBA_HIST_OSSTAT_NAME displays the names of the operating system statistics. NUM_CPUS 4 Number of CPUs / processors on the DB Server Its a good starting point toĪnalyze if there are hardware level resource issues. V$osstat - view captures machine level information in the database. Prior to 10g, to know the OS level resource consumption, one need to login into the server and use OS levelutilities, like sar, top,vmstat to know the server resource usage.Now in 10g, just viewing v$osstat view can provide some useful information about OS resource consuption status. Please note that you need performance pack and diagnostic licences from Oracle to use these features and they are available only in Oracle Enterprise Edition. Although, Oracle10g is around past couple of years, we have just started migrating our databases to 10g.No question that Oracle 10g comes with few great features of Performance tuning.I have come across of few of new dictionary and dynamic views of Oracle 10g,which are useful in day by day administration.Prior to 10g, to get this information, you required a little bit work, but, in 10g, its simply selectingthese views/dictionary tables.