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——- Read More

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 Read More

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 Read More

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 Read More

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 , Read More