oracle

The database has a program which performs a huge sort operation (e.g. end of the year reporting process). This may cause the TEMP tablespace to grow and occupy most of the space on the file system. In this example, the report process may run once or twice a year and there is no need to maintain/keep a huge tempfile. The TEMP tablespace was created with datafiles (dictionary managed tablespace temporary) as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid the Error: ORA-1652 Text: unable to extend temp … [Read more...] about How to shrink a Temporary Tablespace datafile in Oracle

Question: How to resize the TEMPFILE(s) for a temporary tablespace after they have grown larger than needed? Large sort operations can cause temporary tablespaces to grow very large and as such there may be a need to 'downsize' after such operations. Until Oracle 11g there was no SQL command to release the unused allocated temporary space. One workaround for this problem is to create a new empty temporary tablespace with a smaller size, assign this new tablespace to the users and then drop … [Read more...] about How To Shrink A Temporary Tablespace in Oracle Database

Question: How do you take a datafile out of autoextend mode? Oracle datafiles were given the ability to extend automatically if needed from a long time. If the given datafile is unable to allocate the space needed, it can increase the size of the datafile to make space for objects to grow. To determine if the datafile has autoextend capabilities, query the dba_data_files table. For example: SQL> select file_name, autoextensible from dba_data_files; FILE_NAME … [Read more...] about How to Disable AUTOEXTEND Mode on a datafile in Oracle Database

Question: How to check whether the datafiles are set to AUTOEXTEND and UNLIMITED? We can query the DBA_DATA_FILES view to check whether the data file is in AUTOEXTEND mode. SQL> select tablespace_name, file_name, autoextensible from dba_data_files; To confirm whether the maxsize is set to UNLIMITED we have to check for the maximum possible size, as there is no concept of an 'UNLIMITED' file size. Oracle limits the number of blocks in a datafile to approximately 4 million blocks (unless … [Read more...] about Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited

The following is a script that will determine how many extents of contiguous free space you have in Oracle as well as the total amount of free space you have in each tablespace. From these results, you can detect how fragmented your tablespace is. The ideal situation is to have one large free extent in your tablespace. The more extents of free space there are in the tablespace, the more likely you will run into fragmentation problems. The size of the free extents is also very important. … [Read more...] about Oracle SQL Script to Detect Tablespace Fragmentation

If you try changing a static parameter value in the SPFILE used at instance startup, you would get the following error message: SQL> alter system set audit_trail=DB scope=both; * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SCOPE=BOTH applies the modification at the instance level and in the spfile used at startup. Use the value that applies the modification in the spfile only, provided that the spfile was used at … [Read more...] about How to Modify Static Parameter value in spfile