Subscribe: Oracle FAQ
http://www.orafaq.com/node/feed
Added By: Feedage Forager Feedage Grade B rated
Language: English
Tags:
bone connected  bone  index  insert  lib security  operand  required usr  security pam  select  sql  usr lib  values insert  values 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Oracle FAQ

Oracle FAQ



Home page of The Oracle Frequently Asked Questions Website. A valuable resource for all Oracle professionals! You may not get the Oracle company line, but you will surely get an answer to just about any question concerning the software giant and its produ



 



Tips to install Oracle 11gr2 RAC on AIX (6.1/7.1)

Thu, 25 Aug 2016 02:56:54 +0000

articles: Technical ArticlesAIX is like an Unix environment awesome original, same to HP-Unix, and, if you have a plan to install Oracle RAC, you need to pay attention. I note some tips in this article to help. 1. Checking Operating System Packages # lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat \ bos.perf.perfstat bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte \ xlC.aix61.rte If not, install on AIX source by smity. It's easy, but remember, some packaged requires your IBM's account to download. 2. Verify UDP and TCP Kernel Parameters # /usr/sbin/no -a | fgrep ephemeral If you expect your workload to require a high number of ephemeral ports, then update the UDP and TCP ephemeral port range to a broader range. For example: # /usr/sbin/no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500 # /usr/sbin/no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500 3. Checking Resource Limits: To ensure that these resource limits are honored, confirm that the line login session required /usr/lib/security/pam_aix is set in /etc/pam.conf.For example: dtsession auth required /usr/lib/security/pam_aix dtlogin session required /usr/lib/security/pam_aix ftp session required /usr/lib/security/pam_aix imap session required /usr/lib/security/pam_aix login session required /usr/lib/security/pam_aix rexec session required /usr/lib/security/pam_aix rlogin session required /usr/lib/security/pam_aix rsh session required /usr/lib/security/pam_aix snapp session required /usr/lib/security/pam_aix su session required /usr/lib/security/pam_aix swrole session required /usr/lib/security/pam_aix telnet session required /usr/lib/security/pam_aix xdm session required /usr/lib/security/pam_aix OTHER session required /usr/lib/security/pam_prohibit websm_rlogin session required /usr/lib/security/pam_aix websm_su session required /usr/lib/security/pam_aix wbem session required /usr/lib/security/pam_aix 4. Tuning AIX System Environment Confirm the aio_maxreqs value using the procedure for your release: AIX 6.1 and 7.1: # ioo -o aio_maxreqs aio_maxreqs = 65536 The aio is Asynchronous Input Output is an exciting parameter, I tried to control and modified it many times, but it's strongly to do from Oracle advices, Quote: Adjust the initial value of aio_maxservers to 10 times the number of logical disks divided by the number of CPUs that are to be used concurrently but no more than 80 Oracle document refer: https://docs.oracle.com/database/121/AXDBI/app_manual.htm#AXDBI7880 5. Tuning Virtual Memory Manager vmo -p -o minperm%=3 vmo -p -o maxperm%=90 vmo -p -o maxclient%=90 vmo -p -o lru_file_repage=0 vmo -p -o strict_maxclient=1 vmo -p -o strict_maxperm=0 Note: You must restart the system for these changes to take effect 6. Increase System block size allocation # /usr/sbin/chdev -l sys0 -a ncargs='128' 7. Configure SSH LoginGraceTime Parameter On AIX systems, the OpenSSH parameter LoginGraceTime by default is commented out, and the default behavior of OpenSSH on AIX can sometimes result in timeout errors. To avoid these errors, complete the following procedure: 7.1. Log in as root. 7.2. Using a text editor, open the OpenSSH configuration file /etc/ssh/sshd_config. 7.3. Locate the comment line #LoginGraceTime 2m. 7.4. Uncomment the line, and change the value to 0 (unlimited). For example: LoginGraceTime 0 7.5. Save /etc/ssh/sshd_config. 7.6. Restart SSH. 8. Setting priviledge to Oracle ASM Luns Same to Solaris, HP-Unix. Remember, when you've got failure of ASM configuration, you need to flush out the disk's slice/partition by OS command "dd". And the slice/partition/LUN allocated from storage to IBM, has got different first alphabet to other platform. The alphabet begins by "r", example: 7.1 ORC and Voting disk # chown grid:asmadmin /dev/rhdisk5 -> OCR # chmod 660 /dev/rhdisk5 # chown grid:asmadmin /dev/rhdisk6 -> Voting Disk # chmod 660 /dev/rhdisk6 7.2 Datafile, Archivelog and Backup # chown grid:asmadmin /dev/rhdisk2 # chmod 660 /dev/rhdisk2 # chown grid:asmadmin /dev/rhd[...]



Recursive WITH, part III: IS_LEAF

Mon, 13 Jun 2016 19:25:35 +0000

articles: SQL & PL/SQLThe CONNECT BY syntax provides a useful pseudocolumn, CONNECT_BY_ISLEAF, which identifies leaf nodes in the data: it’s 1 when a row has no further children, 0 otherwise. In this post, I’ll look at emulating this pseudocolumn using recursive WITH. Let’s continue with the example from my previous posts about hierarchical data: the skeleton from the old song “Dem Dry Bones”. UPDATE skeleton SET connected_to_the=NULL WHERE bone='head'; SELECT * FROM skeleton; BONE CONNECTED_TO_THE ---------------------------------------- ---------------------------------------- shoulder neck back shoulder hip back thigh hip knee thigh leg knee foot heel head neck head toe foot arm shoulder wrist arm ankle leg heel ankle finger wrist a rib back b rib back c rib back With CONNECT BY, we can use the CONNECT_BY_ISLEAF pseudocolumn to identify leaf nodes: SELECT bone, level, ltrim(sys_connect_by_path(bone,' -> '),' -> ') AS path FROM skeleton WHERE connect_by_isleaf=1 START WITH connected_to_the IS NULL CONNECT BY prior bone=connected_to_the ORDER siblings BY 1; BONE LEVEL PATH --------- ----- ----------------------------------------------------------------------------------------------- finger 6 head -> neck -> shoulder -> arm -> wrist -> finger a rib 5 head -> neck -> shoulder -> back -> a rib b rib 5 head -> neck -> shoulder -> back -> b rib c rib 5 head -> neck -> shoulder -> back -> c rib toe 12 head -> neck -> shoulder -> back -> hip -> thigh -> knee -> leg -> ankle -> heel -> foot -> toe This pseudocolumn takes a little more thought to replicate using recursive WITH than the LEVEL pseudocolumn and the SYS_CONNECT_BY_PATH, which, as we saw in my last post, fall naturally out of the recursion. We can imitate CONNECT_BY_ISLEAF by searching DEPTH FIRST and using the LEAD function to peek at the next row’s the_level value. If the next row’s level is higher than the current row, then it’s a child of the current row; otherwise, it’s not a child. Since, with DEPTH FIRST, all the children of a row come out before any siblings, if the next row isn’t a child, then the current row is a leaf. WITH skellarchy (bone, parent, the_level) AS ( SELECT bone, connected_to_the, 0 FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1 FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SEARCH DEPTH FIRST BY bone SET bone_order CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N' SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree , the_level, lead(the_level) OVER (ORDER BY bone_order) AS next_level, CASE WHEN the_level < lead(the_level) OVER (ORDER BY bone_order) THEN NULL ELSE 'LEAF' END is_leaf FROM skellarchy ORDER BY bone_order; BONE_TREE THE_LEVEL NEXT_LEVEL IS_L --------------------------------------------- ---------- ---------- ---- head [...]



Recursive WITH, part II: Hierarchical queries

Fri, 03 Jun 2016 09:38:25 +0000

articles: SQL & PL/SQLIn my last post, I looked at using recursive WITH to implement simple recursive algorithms in SQL. One very common use of recursion is to traverse hierarchical data. I recently wrote a series of posts on hierarchical data, using Oracle’s CONNECT BY syntax and a fun example. In this post, I’ll be revisiting the same data using recursive WITH. There are dozens of examples of hierarchical data, from the EMP table to the Windows Registry to binary trees, but I went with something more fun: the skeleton from the old song “Dem Dry Bones”. Quote:Toe bone connected to the foot bone Foot bone connected to the heel bone Heel bone connected to the ankle bone Ankle bone connected to the shin bone Shin bone connected to the knee bone Knee bone connected to the thigh bone Thigh bone connected to the hip bone Hip bone connected to the back bone Back bone connected to the shoulder bone Shoulder bone connected to the neck bone Neck bone connected to the head bone Since every bone has only one ancestor, and there is a root bone with no ancestor, this is hierarchical data and we can stick it in a table and query it. SELECT * FROM skeleton; BONE CONNECTED_TO_THE ---------------------------------------- ---------------------------------------- shoulder neck back shoulder hip back thigh hip knee thigh leg knee foot heel head neck head toe foot arm shoulder wrist arm ankle leg heel ankle finger wrist a rib back b rib back c rib back You can see that I added some ribs and an arm to make the skeleton more complete! Using Oracle’s CONNECT BY syntax: SQL> col bone FOR a10 SQL> col connected_to_the FOR a9 SQL> col level FOR 99 SQL> col bone_tree FOR a27 SQL> col path FOR a65 SELECT bone, connected_to_the, level, lpad(' ',2*level, ' ') || bone AS bone_tree , ltrim(sys_connect_by_path(bone,'>'),'>') AS path FROM skeleton START WITH connected_to_the IS NULL CONNECT BY prior bone=connected_to_the ORDER siblings BY 1 BONE CONNECTED LEVEL BONE_TREE PATH ---------- --------- ----- --------------------------- ----------------------------------------------------------------- head 1 head head neck head 2 neck head>neck shoulder neck 3 shoulder head>neck>shoulder arm shoulder 4 arm head>neck>shoulder>arm wrist arm 5 wrist head>neck>shoulder>arm>wrist finger wrist 6 finger head>neck>shoulder>arm>wrist>finger back shoulder 4 back head>neck>shoulder>back a rib back 5 a rib head>neck>shoulder>back>a rib b rib back 5 b rib head>neck>shoulder>back>b rib c rib back 5 c rib head>neck>shoulder>back>c rib hip back 5 hip head>neck>shoulder>back>hip thigh hip 6 thigh head>neck>shoulder>back>hip>thigh knee thigh 7 knee head>neck>shoulder>back>hip>thigh>knee leg knee 8 [...]



Recursion with recursive WITH

Tue, 24 May 2016 09:03:09 +0000

articles: SQL & PL/SQLI recently had the opportunity to talk with Tom Kyte (!), and in the course of our conversation, he really made me face up to the fact that the SQL syntax I use every day is frozen in time: I’m not making much use of the analytic functions and other syntax that Oracle has introduced since 8i. Here’s a brief history of these additions to Oracle SQL, from Keith Laker, Oracle’s Product Manager for Analytical SQL: 8i Window functions 9i Rollup, grouping sets, cube, enhanced window functions 10g SQL Model clause, statistical functions, partition outer join 11g SQL Pivot clause, Recursive WITH, Listagg, Nth value 12c Pattern matching, Top N Not only do these make complex queries much, much simpler and easier, they are also much faster for the same result than non-analytic SQL, as Tom Kyte has shown repeatedly on his blog and in his books. So, I was sold and I wanted to jump in with Recursive WITH. The WITH clause lets you define inline views to use across an entire query, and the coolest thing about this is that you can define the subquery recursively – so that the inline view calls itself. Recursive WITH basic syntax: WITH Tablename (col1, col2, ...) AS (SELECT A, B, C... FROM dual --anchor member UNION ALL SELECT A', B', C'... from Tablename where... --recursive member ) select ... from Tablename where ... Refactoring the Factorial One fun thing about recursive WITH, aka recursive subquery refactoring, is the ease with which we can implement a recursive algorithm in SQL. Let’s warm up with a classic example of recursion: finding the factorial of a number. Factorial(n) = n! = 1*2*3*…*n . It’s a classic example because Factorial(n) can be defined recursively as: Factorial(0) = 1 Factorial(n) = Factorial(n-1) * n Here’s a first pass at implementing that directly in SQL to find the factorial of 5, using a recursive WITH subquery: WITH Factorial (operand,total_so_far) AS (SELECT 5 operand, 5 total_so_far FROM dual -- Using anchor member to pass in "5" UNION ALL SELECT operand-1, total_so_far * (operand-1) FROM Factorial WHERE operand > 1) SELECT * FROM Factorial; OPERAND TOTAL_SO_F ---------- ---------- 5 5 4 20 3 60 2 120 1 120 and to display just the result, we select it from Factorial: WITH Factorial (operand,total_so_far) AS (SELECT 5 operand, 5 total_so_far FROM dual -- Find the factorial of 5 UNION ALL SELECT operand-1, total_so_far * (operand-1) FROM Factorial WHERE operand > 1) SELECT MAX(operand) || '! = ' || MAX(total_so_far) AS RESULT FROM Factorial; RESULT ----------------- 5! = 120 Ahem! I have cheated a little for simplicity here. The query doesn’t take into account that Factorial(0) = 1: WITH Factorial (operand,total_so_far) AS (SELECT 0 operand, 0 total_so_far FROM dual -- Find the factorial of 0 UNION ALL SELECT operand-1, total_so_far * (operand-1) FROM Factorial WHERE operand > 1) -- This is going to get me nowhere fast... SELECT * FROM Factorial; OPERAND TOTAL_SO_F ---------- ---------- 0 0 To do it properly, we need to include Factorial(0) = 1 in the recursive subquery: WITH Factorial (operand,total_so_far) AS (SELECT 0 operand, 0 total_so_far FROM dual -- Find the factorial of 0 UNION ALL SELECT operand-1, CASE -- Factorial (0) = 1 WHEN operand=0 THEN 1 ELSE (total_so_far * (operand-1)) END FROM Factorial WHERE operand >= 0) SELECT MAX(operand) || '! = ' || MAX(total_so_far) AS RESULT FROM Factorial; RESULT ------------------------------------------------------------------------------------ 0! = 1 We can also reverse direction and recursively build a table of factorials, multiplying as we go. That’s the approach Lucas Jellema takes in his excellent blog post on factorials in SQL. WITH Factorial (operand,output[...]



SQL*Plus error logging – New feature release 11.1

Fri, 02 May 2014 14:39:27 +0000

articles: SQL & PL/SQLOne of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it’s own user defined error logging table(s). Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1 A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it’s a really helpful feature, at least during the unit testing of the code. I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily : NOTE : SQL*Plus error logging is set OFF by default. So, you need to “set errorlogging on” to use the SPERRORLOG table. SP2 Error Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> desc sperrorlog; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) SCRIPT VARCHAR2(1024) IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOB SQL> truncate table sperrorlog; Table truncated. SQL> set errorlogging on; SQL> selct * from dual; SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored. SQL> select timestamp, username, script, statement, message from sperrorlog; TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 11-SEP-13 01.27.29.000000 AM SCOTT TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- selct * from dual; SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored. ORA Error SQL> truncate table sperrorlog; Table truncated. SQL> select * from dula; select * from dula * ERROR at line 1: ORA-00942: table or view does not exist SQL> select timestamp, username, script, statement, message from sperrorlog; TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 11-SEP-13 01.36.08.000000 AM SCOTT TIMESTAMP --------------------[...]



Finding gaps with analytic functions

Sun, 12 Jan 2014 12:20:24 +0000

articles: 

Finding gaps is classic problem in PL/SQL. The basic concept is that you have some sort of numbers (like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26), where there’s supposed to be a fixed interval between the entries, but some entries could be missing. The gaps problem involves identifying the ranges of missing values in the sequence. For these numbers, the solution will be as follows:
START_GAP END_GAP
4 4
7 7
11 14
16 19
24 24

First, run the following code, to create tab1 table:


CREATE TABLE tab1
(
col1 INTEGER
);

Then, insert a few rows:


INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (2);
INSERT INTO tab1 VALUES (3);
INSERT INTO tab1 VALUES (5);
INSERT INTO tab1 VALUES (6);
INSERT INTO tab1 VALUES (8);
INSERT INTO tab1 VALUES (9);
INSERT INTO tab1 VALUES (10);
INSERT INTO tab1 VALUES (15);
INSERT INTO tab1 VALUES (20);
INSERT INTO tab1 VALUES (21);
INSERT INTO tab1 VALUES (22);
INSERT INTO tab1 VALUES (23);
INSERT INTO tab1 VALUES (25);
INSERT INTO tab1 VALUES (26);

COMMIT;

With data, you can take care of solving the gaps problem…

One of the most efficient solutions to the gaps problem involves using analytic functions (also known as window functions)


WITH aa AS
(SELECT col1 AS cur_value, LEAD (col1) OVER (ORDER BY col1) AS next_value
FROM tab1)
SELECT cur_value + 1 AS start_gap, next_value - 1 AS end_gap
FROM aa
WHERE next_value - cur_value > 1
ORDER BY start_gap

Using the LEAD function, you can return for each current col1 value (call it cur_value) the next value in the sequence (call it next_value). Then you can filter only pairs where the difference between the two is greater than the one.




Inverted tables: an alternative to relational structures

Sun, 08 Sep 2013 08:52:30 +0000

articles: WarehousingThe inverted table format can deliver fast and flexible query capabilities, but is not widely used. ADABAS is probably the most successful implementation, but how often do you see that nowadays? Following is a description of how to implement inverted structures within a relational database. All code run on Oracle Database 12c, release 12.1.0.1. Consider this table and a few rows, that describe the contents of my larder: create table food(id number,capacity varchar2(10),container varchar2(10),item varchar2(10)); insert into food values(1,'large','bag','potatoes'); insert into food values(2,'small','box','carrots'); insert into food values(3,'medium','tin','peas'); insert into food values(4,'large','box','potatoes'); insert into food values(5,'small','tin','carrots'); insert into food values(6,'medium','bag','peas'); insert into food values(7,'large','tin','potatoes'); insert into food values(8,'small','bag','carrots'); insert into food values(9,'medium','box','peas'); The queries I run against the table might be "how many large boxes have I?" or "give me all the potatoes, I don't care about how they are packed". The idea is that I do not know in advance what columns I will be using in my predicate: it could be any combination. This is a common issue in a data warehouse. So how do I index the table to satisfy any possible query? Two obvious possibilities: First, build an index on each column, and the optimizer can perform an index_combine operation on whatever columns happen to be listed in the predicate. But that means indexing every column - and the table might have hundreds of columns. No way can I do that. Second, build a concatenated index across all the columns: in effect, use an IOT. That will give me range scan access if any of the predicated columns are in the leading edge of the index key followed by filtering on the rest of the predicate. Or if the predicate does not include the leading column(s), I can get skip scan access and filter. But this is pretty useless, too: there will be wildly divergent performance depending on the predicate. The answer is to invert the table: create table inverted(colname varchar2(10),colvalue varchar2(10),id number); insert into inverted select 'capacity',capacity,id from food; insert into inverted select 'container',container,id from food; insert into inverted select 'item',item,id from food; Now just one index on each table can satisfy all queries: create index food_i on food(id); create index inverted_i on inverted(colname,colvalue); To retrieve all the large boxes: orclz> set autotrace on explain orclz> select * from food where id in 2 (select id from inverted where colname='capacity' and colvalue='large' 3 intersect 4 select id from inverted where colname='container' and colvalue='box'); ID CAPACITY CONTAINER ITEM ---------- ---------- ---------- ---------- 4 large box potatoes Execution Plan ---------------------------------------------------------- Plan hash value: 1945359172 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | C --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 141 | | 1 | MERGE JOIN | | 3 | 141 | | 2 | TABLE ACCESS BY INDEX ROWID | FOOD | 9 | 306 | | 3 | INDEX FULL SCAN | FOOD_I | 9 | | |* 4 | SORT JOIN | | 3 | 39 | | 5 | VIEW | VW_NSO_1 | 3 | 39 | | 6 | INTERSECTION | | | | | 7 | SORT UNIQUE | | [...]



Three impossibilities with partitioned indexes

Sun, 01 Sep 2013 16:22:11 +0000

articles: RDBMS ServerThere are three restrictions on indexing and partitioning: a unique index cannot be local non-prefixed; a global non-prefixed index is not possible; a bitmap index cannot be global. Why these limitations? I suspect that they are there to prevent us from doing something idiotic. This is the table used for all examples that follow: CREATE TABLE EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ) PARTITION BY HASH (EMPNO) PARTITIONS 4; the usual EMP table, with a partitioning clause appended. It is of course a contrived example. Perhaps I am recruiting so many employees concurrently that a non-partitioned table has problems with buffer contention that can be solved only with hash partitioning. Why can't I have a local non-prefixed unique index? A local non-unique index is no problem, but unique is not possible: orclz> create index enamei on emp(ename) local; Index created. orclz> drop index enamei; Index dropped. orclz> create unique index enamei on emp(ename) local; create unique index enamei on emp(ename) local * ERROR at line 1: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index You cannot get a around the problem by separating the index from the constraint (which is always good practice): orclz> create index enamei on emp(ename) local; Index created. orclz> alter table emp add constraint euk unique (ename); alter table emp add constraint euk unique (ename) * ERROR at line 1: ORA-01408: such column list already indexed orclz> So what is the issue? Clearly it is not a technical limitation. But if it were possible, consder the implications for performance. When inserting a row, a unique index (or a non-unique index enforcing a unique constraint) must be searched to see if the key value already exists. For my little four partition table, that would mean four index searches: one of each local index partition. Well, OK. But what if the table were range partitioned into a thousand partitions? Then every insert would have to make a thousand index lookups. This would be unbelievably slow. By restricting unique indexes to global or local prefixed, Uncle Oracle is ensuring that we cannot create such an awful situation. Why can't I have a global non-prefixed index? Well, why would you want one? In my example, perhaps you want a global index on deptno, partitioned by mgr. But you can't do it: orclz> create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4; create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4 * ERROR at line 1: ORA-14038: GLOBAL partitioned index must be prefixed orclz>This index, if it were possible, might assist a query with an equality predicate on mgr and a range predicate on deptno: prune off all the non-relevant mgr partitions, then a range scan. But exactly the same effect would be achieved by using global nonpartitioned concatenated index on mgr and deptno. If the query had only deptno in the predicate, it woud have to search each partition of the putative global partitioned index, a process which would be just about identical to a skip scan of the nonpartitioned index. And of course the concatenated index could be globally partitioned - on mgr. So there you have it: a global non-prefixed index would give you nothing that is not available in other ways. Why can't I have a global partitioned bitmap index? This came up on the Oracle forums recently, https://forums.oracle.com/thread/2575623 Global indexes must be prefixed. Bearing that in mind, the question needs to be re-phrased: why would anyone ever want a[...]



Are older releases of the database really unsupported?

Sun, 28 Jul 2013 10:19:47 +0000

articles: 

I see posts on Oracle related forums about various releases (anything that isn't 11.x or 12.x) being "unsupported". This is wrong. Of course you should upgrade any 9i or 10g databases, but you don't have to.

Oracle Corporation's lifetime support policy is documented here,
Lifetime Support Policy
take a look, and you'll see that release 10.2 was in premier support until end July 2010 when it went into extended support. At end July 2013, it goes into sustaining support. Sustaining support will continue indefinitely. Even release 8.1.7 will have sustaining support indefinitely.
So what is sustaining support? That is documented here,
Lifetime support benefits
To summarize, extended support gives you everything you are likely to need. What you do not get is certification against new Oracle products or new third party products (principally, operating systems). But does that matter? I don't think so. For example, release 11.2.0.3 (still in premier support) is not certified against Windows 8, but it works fine.
Sustaining support has a more significant problem: no more patches. Not even patches for security holes, or changes in regulatory requirements. The security patch issue may of course be serious, but regulatory issues are unlikely to matter (this is a database, not a tax management system.) Think about it: 10g has been around for many years. It is pretty well de-bugged by now. If you hit a problem with no work around, you are pretty unlucky. Sustaining support gives you access to technical support, available patches, software, and documentation. That is all most sites will ever need.
Right now, I am working on a 9.2.0.8 database. It cannot be upgraded because the application software is written by a company that does not permit a database upgrade. Why not? Well, the reason may be commercial: they have a replacement product that is supported on newer databases. But that is nothing to do with me. The database works, the software works. Making it work better is a challenge - but that is what a DBA is paid to do. Don't just write it off as "unsupported".
Of course I am not suggesting that users should not upgrade to current releases - but upgrades are a huge project, and can have major implications. Running out dated software is silly, unless you have an irrefutable reason for so doing. The lack of security patches make you vulnerable to data loss. The lack of regulatory patches may make it illegal. The lack of newer facilities will be restricting the utility of the system. You may be losing money by not taking of advantage of changes of newer technology that can better exploit your hardware.
If anyone is looking for consulting support to upgrade their database - my boss will be happy to give you a quote. But I won't refuse to support you in the meantime.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com




12C: IN DATABASE ARCHIVING

Sun, 28 Jul 2013 08:42:59 +0000

articles: Technical ArticlesIn this post, I will demonstrate a new feature introduced in 12c : In database archiving. It enables you to archive rows within a table by marking them as invisible. This is accomplshed  by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows are not visible to the queries but if needed, can be viewed , by setting a session parameter ROW ARCHIVAL VISIBILITY. Overview: -- Create test user uilm, tablespace ilmtbs -- Connect as user uilm -- create and populate test table (5 rows) ilmtab with row archival clause -- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created   and has the default value of 0 (indicates that row is active) -- Note that this column is not visible when we describe the table or simply issue select * from ... -- We need to access data dictionary to view the column -- Make two  rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value. -- Check that inactive rows are not visible to query -- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also -- Set the parameter ROW ARCHIVAL VISIBILITY  = active to hide inactive rows -- Issue an insert into ... select * and check that only 3 visible rows are inserted -- Set the parameter ROW ARCHIVAL VISIBILITY  = all to see inactive rows also -- Issue an insert into ... select * and check that all the rows are inserted but ORA_ARCHIVE_STATE    is not propagated in inserted rows -- Disable row archiving in the table and check that column ORA_ARCHIVE_STATE is automatically dropped -- drop tablespace ilmtbs and user uilm Implementation : -- Create test user, tablespace and test table SQL> conn sys/oracle@em12c:1523/pdb1 as sysdba sho con_name CON_NAME ------------------------------ PDB1 SQL> set sqlprompt PDB1> PDB1>create tablespace ilmtbs datafile '/u02/app/oracle/oradata/cdb1/pdb1/ilmtbs01.dbf' size 1m; grant connect, resource, dba  to uilm identified by oracle; alter user uilm default tablespace ilmtbs; conn uilm/oracle@em12c:1523/pdb1 sho con_name CON_NAME ------------------------------ PDB1-- create table with "row archival clause" PDB1>drop table ilmtab purge; create table ilmtab (id number, txt char(15)) row archival; insert into ilmtab values (1, 'one'); insert into ilmtab values (2, 'two'); insert into ilmtab values (3, 'three'); insert into ilmtab values (4, 'four'); insert into ilmtab values (5, 'five'); commit;-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created    and has the default value of 0 (indicates that row is active) PDB1>col ora_archive_state for a20 select id, txt, ora_archive_state from ilmtab; ID TXT             ORA_ARCHIVE_STATE ---------- --------------- -------------------- 1 one             0 2 two             0 3 three           0 4 four            0 5 five            0-- Note that this column is not visible when we describe the table or simply issue select * from ... PDB1>desc ilmtab Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- ID                                                 NUMBER TXT                                                CHAR(15) PDB1>select * from ilmtab; ID TXT ---------- --------------- 1 one 2 two 3 three 4 four 5 five -- Since the column is invisible, let me try and make it visible-- Note that Since the column is maintained by oracle itself, user can't modify its attributes PDB1>alter table ilmtab modify (ora_archive_state visible); alter table ilmtab modify (ora_archive_state visible) *ERROR at line[...]