The second and terminal release of Oracle Corporations Oracle 11g database product. 11.2 was released for Linux on September 1st, 2009. New features added include RAC One Node, Improved data compression ratios (up to 20x), Edition Based Redefinition, Recursive Subquery Factoring, Instance Caging, ...
17
votes
3answers
12k views
What is the difference between sys and system accounts in Oracle databases?
There are two ways to connect to Oracle as a administrator using sqlplus:
sqlplus sys as sysdba
sqlplus system/manager
These accounts should be uses for different purposes, I suppose.
Which tasks ...
5
votes
2answers
3k views
Learning to Optimize SQL Queries and Understand Execution Plans - Resources?
I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team.
They ...
5
votes
4answers
373 views
Does an IOT guarantee order in a select?
We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1).
The priority does not matter ...
3
votes
2answers
460 views
Oracle extents - How can I have so many with this small size?
This is a different question than the one with the almost-identical title.
Again, this is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents ...
2
votes
1answer
535 views
Drop user did not reduce size of tablespace file
I have a development environment in which I frequently copy a certain schema.
When I'm done with the copy, I will use OracleEM to drop that user/schema.
My problem is that this does not seem to ...
5
votes
1answer
3k views
Why in Oracle 11gR2 I can't drop the materialized view with the same user that created it?
I created a materialized view with the DI_TEST_AL user, let's name it MY_MVIEW. It appears in the USER_OBJECTS table as MATERIALIZED VIEW, I try to drop it, I get a success message, but the object is ...
6
votes
3answers
261 views
How can I ensure that only one copy of a procedure is running in Oracle?
We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error.
Whats the best method ...
6
votes
3answers
2k views
How to differentiate between SQL and PL/SQL?
I know the question might sound too stupid, but I never understood this part.
SQL*Plus works with both SQL and
PL/SQL. How do I know whether some
code is SQL or PL/SQL? If my code has
a for ...
9
votes
2answers
1k views
Security for Application Developers doing PL/SQL work in Oracle
How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...
7
votes
1answer
122 views
Cardinality is so far off from reality
I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2.
select count(*)
from table1
where col1 = ...
5
votes
2answers
379 views
Oracle Advanced Queuing LIFO
With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
4
votes
3answers
804 views
Is (and if how) it possible in Oracle to “insert into <table> values <rowtype-variable>” if <table> has virtual columns
Here's a table
create table tq84_virtual_test_without (
col_1 number,
col_2 number,
col_3 number,
col_4 number,
col_5 number
);
with the rule that col_5's value is the sum of the other ...
4
votes
1answer
2k views
oracle format specifiers: to_number vs to_char
SQL> select TO_NUMBER(123.56,'999.9') from dual;
select TO_NUMBER(123.56,'999.9') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select TO_CHAR(123.56,'999.9') ...
4
votes
1answer
516 views
What is the difference between these two create table statements?
a)
SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE);
Table created.
SQL> desc xml_tab1;
Name Null? Type
----------------------------------------- -------- ...
3
votes
4answers
1k views
Why does = NULL not work in Oracle?
I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL.
Why does = NULL not work here?
1
vote
1answer
93 views
Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index
One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index. Its values are just a sequence ...
1
vote
1answer
368 views
Repartition Primary Key Index online in Oracle
I have an 24x7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention. We use sequences for the ...
0
votes
2answers
423 views
“ORA-01017: invalid username/password” : Why is that?
What is the difference between the two :
SQLPLUS / AS SYSDBA
and
SQLPLUS
Enter user-name: SYSDBA
Enter password:
If I use the first command I get connected and see the prompt SQL> but upon ...
