ORA-12906 cannot drop default temporary tablespace

If you try to drop default temporary tablespace of database as it will result in error as:- SQL>SELECT TABLESPACE_NAME FROM DBA_TEMP_FILES; TABLESPACE_NAME --------------- TEMP SQL>alter tablespace temp offline; alter tablespace temp offline * Error at line 1; ORA-12905:default temporary tablepspace cannot be offline SQL>drop tablespace temp including contents and datafiles; drop tablespace TEMP including contents and datafiles * Error at line2: ORA-12906:cannot drop default Temporary tablespace In order to solve this problem you...

Read More

Recover or Recreate TEMPORARY Tablespace in Oracle

In database you may discover that your temporary tablespace is deleted from OS or it might got corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode. Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users. In order to do that follow the steps here: 1)Find out the temporary datafiles.  SQL> select file_name,file_id,...

Read More

Space Allocation in a TEMPORARY Tablespace

Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement: SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users.To see the default temporary tablespace for a database:SQL>SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';  Space Allocation in a TEMPORARY Tablespace You can view the allocation and deallocation...

Read More

TEMPORARY Tablespace And TEMPFILE

TEMPORARY TABLESPACE  Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables.A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts.If you join two large tables and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing...

Read More

Refresh Materialized View

Oracle maintains the data in materialized views by refreshing them after changes are made to their master tables. The refresh method can be incremental (fast refresh) or complete. For materialized views that use the fast refresh method, a materialized view log or direct loader log keeps a record of changes to the master tables. Materialized views can be refreshed either on demand or at regular time intervals. Alternatively, materialized views in the same database as their master tables can be refreshed whenever a transaction commits its changes...

Read More

Materialized Views

Materialized views are schema objects that can be used to summarize, compute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed or mobile computing:  In data warehouses, materialized views are used to compute and store aggregated data such as sums and averages. Materialized views in these environments are typically referred to as summaries because they store summarized data. They can also be used to compute joins with or without aggregations. If compatibility...

Read More

Extents Overview

An extent is a contiguous set (side-by-side) of Oracle data blocks allocated to a segment within a tablespace. The size of an extent is controlled by storage parameters used when you CREATE or ALTER the segment (INITIAL, NEXT and PCT_INCREASE) and tablespace parameters.  When Extents Are Allocated  When you create a table or index then oracle automatically creates an initial extent with a number of specified data blocks under the table's segment.If the data blocks of a segment's initial extent become full and more space is required to...

Read More

Row Chaining, Row Migrating, PCTFREE, PCTUSED

Row Chaining   The row is too large to fit into an EMPTY data block that is known as chained row. Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. If  you have a 2k blocksize but your row size is 4k, we will use 3 blocks to store that row in pieces.  Any table with a long/long raw will have chained rows.Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining...

Read More

Row Format In DB

Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece.If all of a row's data cannot be inserted into a single data block or an update to an existing row causes the row to outgrow its data block, Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in...

Read More

Oracle Data Block Format

The Format of Oracle Data Block is as follows: Header  Table Directory  Row Directory  Row Data  Free Space    Header  The header contains general block information, such as the block address and the type of segment (for example, data or index).  Table Directory This portion of the data block contains information about the table having rows in this block.  Row Directory This portion of the data block contains information about the actual rows in the...

Read More

Oracle Data Block Overview

Oracle manages the storage space in the datafiles of a database in units called data blocks.   Data block is the smallest unit of data used by a database.   At the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.   The standard block size is specified by the initialization parameter DB_BLOCK_SIZE.  You can specify of up to five nonstandard block sizes.  The data block sizes should be...

Read More

Rules To Specify Values In An Initialization Parameter

•Parameter within initialization file can be specified in any order. •If a line starts with pound sign(#) the rest words of the line is ignored. # indicates it is comment. •If OS is case sensitive then value of filename within initialization parameter is case sensitive. •To enter several parameters in a line use spaces between the parameters. Like, db_name=dbase sga_target=300M sga_max_size=300M •The parameter that take multiple values enter multiple values enclosed in parentheses and separated by commas. Like, control_files=('/oradata1/arju/control01.ctl','/oradata1/arju/control02.ctl') Alternatively...

Read More

Solution Of Startup Files With An Error ORA-00119 and ORA-00132

Error Description: Database Startup fails with oracle error ORA-00119, ORA-00132 SQL> startup mountORA-00119: invalid specification for system parameter LOCAL_LISTENERORA-00132: syntax error or unresolved network name 'LISTENER_DBASE' Cause of The problem: If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to...

Read More

Solution OF An Error ORA-27102

Scenario of The problem: In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error, SQL> startupORA-27102: out of memorySolaris-AMD64 Error: 22: Invalid argument Reason of The problem:The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order...

Read More

Oracle Initialization Parameters File Types

There are different types of initialization parameters in oracle, such as, 1)Derived Parameters 2)Operating System-Dependent Parameters 3)Variable Parameters 1)Derived Parameters:As the name indicates these parameter values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value. For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES...

Read More

Oracle Database Configuration Assistant

The Database Configuration Assistant (DBCA) an Oracle supplied tool that enables you to create an Oracle database, configure database options for an existing Oracle database, delete an Oracle database, or manage database templates. DBCA is launched automatically by the Oracle Universal Installer, but it can be invoked standalone from the Windows operating system start menu (under Configuration Assistants) or by entering the following on the UNIX command line: dbca The Database Configuration Assistant...

Read More

Logminer Fails With ORA-01284, ORA-00308

Problem Description: Whenever you try to run the logminer in the mining database (different from source database. Source database is one in which redo or archived redo logs are generated and mining database is one in which you try to analysis those logs) to add logfile using DBMS_LOGMNR.ADD_LOGFILE it generates error ORA-01284, ORA-00308, ORA-27047. SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( - LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc', - OPTIONS => DBMS_LOGMNR.NEW); BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc',...

Read More

How Oracle Logminer Is Used To Analysis Logfile

Any changes to database is recored in online redo logfiles. If your database archival mode on then online redo log files are archived which contains the database changes. With these logfile we can analysis any changes in database.  we can say an undo entry provides the values of data stored before a change and the redo entry provides the values of data stored after a change. Because undo segments are database changes, they also generate redo entries. So we can get them from online redo logs and then to archived logs. So from online redo...

Read More