DBORCLOUD Blog

How to drop and create a Temporary Tablespace

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialization parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

How to restore the OCR and Voting disk when they are lost

11gR2 – Restore OCR/ VOTEDISK based on ASM, when loss of CRS Diskgroup. Labels: 11GR2, loss of CRS Diskgroup, lost OCR on ASM, RAC, RESTORE OCR, RESTORE OCR ON ASM, RESTORE VOTEDISK Locate the latest automatic OCR backu When using a non-shared CRS home, automatic OCR backups can be located on any node of the cluster, consequently all nodes need to be checked for the most recent backup: $ ls -lrt $CRS_HOME/cdata/rac_cluster1/ -rw——- 1 root root 7331840 Mar 10 18:52 week.ocr -rw——- 1 root root 7651328 Mar 26 01:33 week_.ocr -rw——- 1 root root 7651328 Mar 29 01:33 day.ocr -rw——-...

Create Oracle SQL Profile For Tuning

Looking for how to tune a SQL statement by creating a SQL Profile? The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Automatic SQL Tuning deals with this problem with its SQL Profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization...

Pivot and Unpivot

These keyword was introduced in Oracle 11g. The UNPIVOT operator transforms the data arranged on a column into separate rows. With this utility it is possible to convert the display of the data to have them displayed on columns to have them on lines. In this example we talk about the prices associated with a ticket: UNPIVOT : SELECT * FROM tickets ; Applying the unpivot: pivoting on the ticket column it is possible to see the opening and closing price on separate lines. SELECT ticket,to_char(ticket_date,’YYYYMMDD’)ticket_date,price_type,price FROM tickets UNPIVOT ( price FOR price_type IN ( opening_price AS ‘OPEN’, closing_price AS...

What is cloud computing?

Aspects and definition of the SaaS, PaaS, DaaS, IaaS platforms. What is cloud computing? Possibility of relying on a specialized provider for the management of one or more IT resources that, from that moment on, are provided via the Web through an outsourcing contract. All this, without the company having to bear the costs of purchasing licenses or machines to take advantage of services indispensable to the business. the supplier to maintain all the infrastructure necessary to manage and distribute the services based on the request (on demand) and with a pay per use formula. All with a subscription to...

How to solve ORA-04021: timeout occurred while waiting to lock object

Compile an object can give the following error select s.sid, s.serial#, p.spid, s.username, s.osuser, s.program, s.terminal, s.module, s.action , p.tracefile , s.type , s.logon_time , s.state , s.wait_time, s.seconds_in_wait, s.service_name from v$session s, v$process p where s.paddr = p.addr ; Quire the process to find the session that still running select a.object, a.type, b.sid,b.serial#,s.inst_id, b.username, b.osuser, b.program, s.status, s.sql_exec_start from v$access a, v$session b, gv$session s where a.sid = b.sid and a.owner = DECODE(UPPER(‘&1’), ‘ALL’, a.object, upper(‘&1’)) and a.object = DECODE(UPPER(‘&2’), ‘ALL’, a.object, upper(‘&2’)) and b.sid = s.sid and b.serial# = s.serial# AND s.status = ‘ACTIVE’ order by a.object ,...

ORACLE 12C : Listener service registration

With previous releases PMON (Process Monitor) was responsible for registering database services to the Listener. Starting from Oracle 12C there is a change in architecture with respect to the service registration in Oracle Database Release 12c. Oracle has introduced a new mandatory background process dedicated for “database service registration to the Listener” and named LREG (Listener Registration Process). The listener registration process (LREG) registers information about the database instance and dispatcher processes with the Oracle Net listener (see “The Oracle Net Listener”). When an instance starts, LREG polls the listener to determine whether it is running. If the listener is running,...

Nested table

In PL SQL exists an efficient and adaptable collection of data: Nested table. Nested table is similar to one dimensional array but with some differences : An array has declare number of element – Nested table not. The size of nested table can increase using extend method. An array has always consecutive elements – Nested table has consecutive elements initially but it can become sparse when elements are deleted. To create a nested table you have to use this syntax : type type_name is table of element_type[size element]; table_name type_name; Here below an example to creation and use of a...

ORACLE – enq: TX row lock contention

Enqueues are locks that coordinate access to database resources. enq: wait eventindicates that the session is waiting for a lock that is held by another session. The amount of wait time associated with this wait event is excessive and can be responsible for performance issues observed in the application. TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.  Some situations of TX enqueue: – Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another...

ORACLE 12C: Dataguard Far Sync

Oracle Dataguard Far Sync instance is a new feature introduced in Oracle Database 12c, which simply works like a archive/redo repeater.. Far sync instance is something different than the Oracle Database instance, as its mission is accepting redo from the primary database and then shipping that redo to other members of the Oracle Data Guard configuration.. Far Sync instance is an instance without a database.. Although; it has controlfile, and capability to read from standby redolog files and write them into its local archive log files, Far Sync instance does not map any database file, in other words, does not...