Category: Oracle Database
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.
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——-...
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...
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...
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 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...
When you work with xml file you should be solve the scenery that have an node with its properly sub node. In our case the following nodes () ; () . It is quite delicate find exactly approach that gives to you the requirement expected. The problems that I have got they were two different: – Duplicate row – ORA-19025 EXTRACTVALUE returns value of only one node or ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence The following image shows the scenery described: Duplicate row means the query extract the values that are not...
Oracle Databse 18c comes with some cool new features for DBAs. In this article I’ll list my preferred ones. Just one note: I think the name “Autonomous Database” is quite misleading, as it means that some of the new features COULD be used to configure your database as “autonomous” where previous versions required some maintenance, and this will be the direction of the next Oracle Database generations. 1. PRIVATE TEMPORARY TABLES You can create temporary tables with these characteristics: – NAME must be prefixed by “ORA$PTT_” (or the current value of PRIVATE_TEMP_TABLE_PREFIX initialization parameter) – DATA is automatically deleted at...
Database version : from Oracle Database 12cR1 Create sequence with session or global option When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. SESSION option makes opportunity to create sequence that can used in association with global temporary table then its visibility is just for session. The following options CACHE, NOCACHE, ORDER, or NOORDER are ignored when SESSION is declared. 1) statement SQL> CREATE GLOBAL TEMPORARY TABLE glb_tmp_table (a number); Table created. SQL> CREATE SEQUENCE sq_sess SESSION; Sequence created. 2) Script execution SQL> INSERT INTO glb_tmp_table VALUES (sq_sess.NEXTVAL); 1 row created....
Introducing the topic: In list partitioning, the database uses a list of discrete values as the partition key for each partition. You can use list partitioning to control how individual rows map to specific partitions. By using lists, you can group and organize related sets of data when the key used to identify them is not conveniently ordered. Here an example: 1. This example shows how the index works Assume that you create list_sales as a list-partitioned table using the following statement. The channel_id column is the partition key. check partition: Table Partition HIGH_VALUE Table Partition MEDIUM_VALUE Table Partition LOWER_VALUE...
