oracle

The Problem Users cannot connect to the database : 0RA-00257:archiver error, connect internal only until freed ORA-16014:log 2 sequence# 231 not archived, no available destinations ORA-00312:online log 2 thread 1:'/[path]/redo02.log' The Solution The most probable cause for this error is that the flashback recovery area must have gone full. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive Destination USE_DB_RECOVERY_FILE_DEST Oldest online … [Read more...] about ORA-00257:Archiver Error, Connect Internal Only Until Freed

Database triggers are PL/SQL code that executes based on event in the database. A trigger is an Oracle statement (or a group of statements enclosed within BEGIN and END statements) that are automatically executed by Oracle in response to any of these statements: ALTER CREATE DROP DELETE INSERT UPDATE The last three are of the greatest interest—triggers that are executed in response to table row changes. How to enable a trigger To enable a database trigger do the following: SQL> … [Read more...] about How to Enable a Database Trigger

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