oracle

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