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, ...
3
votes
1answer
419 views
Oracle query against SQL Server database giving inconsistent results
We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following ...
3
votes
1answer
87 views
best way to create multiple similar tables - CTAS vs INSERT
I am creating a few dozen tables as CREATE TABLE AS SELECT (CTAS). The only difference in the SELECT statements is specifying a different value in the WHERE clause for TEAM_ID. I'm an Oracle newbie ...
3
votes
1answer
999 views
How can I view what NLS settings are active for other session?
I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2. What can I query to show the NLS settings for a different active session - all of the ...
3
votes
1answer
364 views
Objects in v$bh but not dba_objects
The Oracle documentation has a section in the 11.2 Performance Tuning Guide with the title Determining Which Segments Have Many Buffers in the Pool. The section lists the following query:
SELECT ...
3
votes
2answers
690 views
Oracle 11gR2 expdp table converted to CSV from Linux
With Oracle expdp, I can export the tables I want with
expdp INCLUDE=TABLE:table1,table2,table3
I need a way to convert this into CSV on a linux machine. I've seen Windows tools but don't know ...
3
votes
1answer
1k views
Installing 10g client next to 11g database on same computer
I have Oracle 11.2.0.1.0 installed on Windows 7 64 bit
I want to import data from warehouse which has Oracle 10.2.0.4.0 installed.
When I try to import
impdp usr/pass@WAREHOUSE TABLES=TABLE ...
3
votes
1answer
2k views
Optimal value for LOG_BUFFER parameter
The wait event “log buffer space” according to the documentation can indicate that the log buffer is too small or the I/O is slow. We have a system seeing this event in which the redo buffer ...
3
votes
2answers
166 views
Periodic Oracle restarts…intentionally
Taking on a new dba role for an Oracle 11gR2 2 node RAC, I've been told that about 9 times a year, in schedule with other software releases, we intentionally shutdown and restart the database. It ...
3
votes
3answers
264 views
Why does the query cost change so much and how to prevent it
The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that:
The query has an structure like:
SELECT
... a lot of stuff ...
...
3
votes
2answers
318 views
Help to understand explain plan in Oracle
I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
3
votes
1answer
52 views
INDEX on a table where I'm only filtering on a row_number()
I've got the following table (scaled down to a MWE):
Fiddle here.
CREATE TABLE weekly
( yearnum int,
weeknum int,
location varchar2(5),
weekly_count int,
weekly_sum int);
INSERT INTO weekly ...
3
votes
1answer
861 views
Unable to create a tablespace, unusual error
I'm having difficulty creating tablespaces with Oracle 11gR2 on a fiber attached disk array. Permissions are correct and there is ample space available. I can create tablespaces on other mounts that ...
3
votes
1answer
245 views
Is there a way to allow a proxy connection to use passed authentication in a public database link?
I think the answer to this is no, but I could be wrong. If nothing else it might be nice for others to be aware of this limitation of proxy connections.
3
votes
1answer
785 views
Add an additional listener to Oracle Database Appliance
After deployment of Oracle Database Appliance with OAK 2.3.0 the standard listeners are configured for the public network on bond0. That includes SCAN listeners and one VIP listener per node. This ...
3
votes
1answer
1k views
How to free up disk space? which logs/directories to clean? [ORACLE 11g]
I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size
/u01/app/11.2.0/grid/cv/log
...
3
votes
2answers
293 views
Shrinking the SYSTEM tablespace in Oracle
Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table.
We have truncated SYS.AUD$ but the datafile is still very big (~30G).
Resize doesn't work because the file contains used data ...
3
votes
2answers
237 views
Increase Oracle buffer cache gets slower
So we're contemplating increasing our Oracle 11gR2 buffer cache. They said they tried it in the past and things appeared to get worse - so they undid the change. They, however, have no idea why ...
2
votes
2answers
2k views
Why can't we specify group functions in Oracle in the where clause?
SQL> desc tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID ...
2
votes
2answers
257 views
How does a database use the SYS schema even when we change the password?
From the thread What is the difference between sys and system accounts in Oracle databases?, I understand that sys schema is supposed to be used only by the database.
But still an administrator can ...
2
votes
1answer
396 views
Flashback feature in Oracle 11g?
I am using Oracle 11gR1. As a part of a project, I have to execute multiple update statements by disabling a few constraints (triggers and check constraints) which could result in a corrupted ...
2
votes
2answers
2k views
In Oracle, how to globally enable parallel execution?
In Oracle 11g, is there a way to tell the database to use parallel execution whenever possible by setting a flag etc, rather than using PARALLEL hints with every SQL statement?
2
votes
1answer
74 views
Oracle Table Design, Storing Small Values
In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm ...
2
votes
1answer
2k views
Installing Oracle 11g XE on Ubuntu 12.04
I have spent the last 6 hours trying to install Oracle 11g on Ubuntu. Obviously, I'm not the only one who has struggled with this, as there is a lot of (often misleading) "documentation" online on how ...
2
votes
1answer
97 views
Oracle start database with incorrect password
I have a question regarding starting oracle database with in correct sys password.
I have provided incorrect password for sys user and I am still able to start database and execute statements. So ...
2
votes
2answers
3k views
Comparing date and timestamp variables in a where clause in a procedure
I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing ...
2
votes
3answers
143 views
Oracle hint failure
When I run the PL/SQL block after the CREATE statement, I receive the duplicate key error I would expect if I did not add the hint. Any ideas to why the hint does not work?
CREATE TABLE Sandbox.Clue
...
2
votes
2answers
6k views
SGA_MAX_SIZE and SGA_TARGET
We recently increased sga_max_size to 4g from 2g but didnt increase sga_target, is it ok not to increase sga_target?? reason we wanted to increase sga is to reduce wait time on sequential reads in our ...
2
votes
2answers
647 views
Add additional disks to FRA diskgroup in ASM
We're running Oracle 11.2.0.3 with a two node RAC and our FRA diskgroup is running low on space. I need to allocate some more disks to it through ASM.
I'm not sure how to go about doing this.
I can ...
2
votes
1answer
534 views
Why Is oracle ignoring my index?
This query is not too complex:
select
trunc(created_date, 'MONTH') as created_date,
al.op_name,
al.region,
al.alarm_type,
COUNT(1) as total_new,
SUM(
(SELECT
...
2
votes
2answers
2k views
How to convert postgresql dump files to oracle dump files?
How can I convert postgresql dump files to oracle dump files? If anyone has experience in this, or knows of an open source tool I can use, please share it with me.
2
votes
1answer
1k views
How to display a number with fixed number of digits before the decimal?
I am looking for result 0123.56 and the following query should have worked.
SQL> select to_number(123.56,'0999.99') from dual;
select to_number(123.56,'0999.99') from dual
*
ERROR ...
2
votes
2answers
51 views
How to remove all hidden parameters from Oracle Database
How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended?
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ...
2
votes
2answers
103 views
Duplicating a Database with RMAN
I would like to duplicate to the same server Oracle Database with RMAN. I've configured flash recovery area for Oracle Database. My database is using SPFILE as shown below.
SQL> SELECT ...
2
votes
1answer
241 views
how to create and insert into a table in an anonymous block
I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table.
When the block compiles, it gives me an ORA-00942: table or view does not exist ...
2
votes
1answer
162 views
Can you have multiple Oracle ACL files?
I have an application which needs to send emails through Oracle triggers. I understand that in Oracle 11g you must have an ACL file to grant certain schemas access to the network. My plan was to ...
2
votes
1answer
2k views
How to create oracle linked server in oracle server itself?
Is it possible to create oracle linked server in other oracle server? I actually wanted to do this with sql server but in sql server I couldn't run long query (8000 limit).
I have access to ...
2
votes
1answer
230 views
Oracle: UTL_RAW.BIT_OR(2,1) gives me 2 instead of 3. Why?
Why am I getting 2 instead of 3 when I perform BIT_OR(2,1)?
SELECT UTL_RAW.CAST_TO_NUMBER ( --> decimal: 3? nope, 2
UTL_RAW.BIT_OR( --> binary: 11? ...
2
votes
1answer
135 views
Oracle ash report
Recently, i have been handed over with an ash report from DBA.
To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
2
votes
2answers
380 views
How to compare performances of two databases
There are two database servers and I want to compare their performances.
Both have Windows operating system and and Oracle (10.2.0.4 and 11.0.2) database.
Both are OLAP databases with 15-20 users ...
2
votes
1answer
1k views
How do I create a database+tablespace+user in Oracle 11gR2?
Good evening,
I'm trying to figure out how to create a database+tablespace+user in Oracle 11g.
I've tried some tutorials, but nothing seems to work.
I cannot find Database Configuration Assistant ...
2
votes
1answer
390 views
Changing a materialized view column's length after a change in the base table?
We changed the underlying table column size (length). So the materialized view which is built on that table throws an error now since the MV column still has the old size.
This is an Oracle 11gR2 ...
2
votes
1answer
712 views
Expanding system tablespace
I need to add new data file into the system tablespace because its running out of space. Are there any precautionary measures I should consider since this is a production environment?
2
votes
1answer
489 views
Recover database until cancel without prompt?
The statement:
RECOVER DATABASE UNTIL CANCEL
prompts once to select the archive log file. Is there a way to pre specify the value so as to be able to use it in a script without prompt?
I have ...
2
votes
1answer
236 views
Virtual Column - Using it in view (Oracle)
I have made a virtual column in Oracle, using the following code.
CREATE OR REPLACE function email_address (ID_ varchar2)
return varchar2
deterministic
as
lname varchar2 (256);
snumber varchar2 ...
2
votes
1answer
549 views
Differing results from gv$session
The following query returns no results, yet it seems like it should.
SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');
In my 11.2.0.2 Standard Edition RAC environment the above ...
2
votes
1answer
204 views
Decision tables in the database: a valid solution?
I have to extend a .Net 4/C# case management application that uses Oracle 11g to handle legal cases.
I have minimal budget, my experience with Oracle and a very junior C# developer to accomplish ...
2
votes
1answer
564 views
Oracle imp crashes without error
I have Oracle 10g XE database locally. I export schema with something like
exp %USER%/%PASSWD%@XE statistics=NONE owner=%USER% file=dump.dmp
Exp is 10.2.0.1.0 version - so is the DB.
Than I take ...
2
votes
2answers
2k views
getting error installing oracle 11gR2: cannot access node
I'm installing Oracle 11.2 on dedicated hosted server (centOS 5.5). I've gone through the Oracle Quick Installation guide diligently. I believe I've set everything up correctly, although I'm not a DBA ...
2
votes
1answer
61 views
Completely uninstall Oracle server on Linux
I have searched lot in Google for how to uninstall Oracle on Linux, but I can't figure it out.
I think that I need to reinstall the server because I get a lot of errors when I try to startup the ...
2
votes
1answer
64 views
Blocking incoming database links
Let's say there is an Oracle database A and another Oracle database B. Our database is A.
Is there a way to block database links from B to A?
