All Questions
Tagged with oracle oracle-11g-r2
1,025
questions
3
votes
1
answer
2k
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 o....
0
votes
0
answers
80
views
Enumerate open oracle connections
How can I troubleshoot which processes are keeping connections open?
Background: A QA installation of several software components is generating an ORA-12516 error now and then, and I'm not sure which ...
3
votes
1
answer
61k
views
How do I grant execute permission to an Oracle database user?
I'm trying to call a Java function from Oracle database that does the following:
Process p = Runtime.getRuntime().exec("...");
When I try to run it, I get the following error:
ORA-29532: Java call ...
3
votes
2
answers
9k
views
Setting NLS params in logon trigger don't work in oracle 11g
We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session ...
9
votes
1
answer
10k
views
Comments in EXPDB parameter file
Is there a way to put comments in a data pump export parameter file?
12
votes
3
answers
68k
views
How do you get the explain plan?
Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.
3
votes
1
answer
5k
views
Can RMAN duplicate be used to "replace" an existing database?
I have an unused database B, not valuable. I'd like to make a copy of database A, but do not want to go through all the manual steps RMAN requires to set up the auxiliary database. Can I use RMAN ...
2
votes
3
answers
30k
views
ORA-39165 Schema was not found in export
I'm getting the following error on a data pump export:
ORA-39165: Schema OFDAT was not found.
First I verified that I had full export permission:
GRANT EXPORT FULL DATABASE TO OFDAT;
Then I tried ...
2
votes
2
answers
2k
views
Oracle extents - How can I have so few with this large size?
Note on duplication:
This is not a duplicate of "Oracle extents - How can I have so many with this small size?" This is a separate question I entered which happens to be in the same domain.
This is ...
5
votes
2
answers
4k
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 ...
10
votes
3
answers
18k
views
I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created?
I have powerful machine with 70 GB RAM. I created one Oracle instance with 20 GB as sga_target. I am not able to create another Oracle instance with sga_target > 10G even when I keep the ...
0
votes
1
answer
3k
views
Upgrading 10g database to 11g database
Is doing a full data export (expdp) from an Oracle 10g (10.2.0.1.0) database and then doing an import of that dump file (impdp) into an 11.2.0.2 database a valid and acceptable upgrade path from 10g ...
4
votes
1
answer
166
views
Thoughts about Oracle backup procedure for 11.2 (beginner question)
Two days ago we "found out" that our old, not taken care of, horribly neglected, Oracle DB server was extremely important to our business.
The only person who knew anything about this server was ...
3
votes
2
answers
45k
views
Why does DBMS_JOB.SUBMIT execute immediately when NEXT_DATE is in the future?
I wanted to create a job that will run tomorrow at noon and recur every day at noon, so I executed this script:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what =&...
0
votes
1
answer
190
views
Best practices in securing Oracle Standard edition one [closed]
I have a database server with Oracle Standard Edition One. I'm interested what are the best practices to secure Oracle? I have Java web application installed in the same server. The Java application ...
10
votes
2
answers
4k
views
Oracle Shutdown Method
Shutting down a database before doing an upgrade or a patch can be done several ways.
shutdown immediate;
or
shutdown abort;
startup restrict;
shutdown immediate;
or
shutdown abort;
startup ...
2
votes
1
answer
516
views
Error creating linked database to oracle
CREATE DATABASE LINK myLINK CONNECT TO username IDENTIFIED BY password USING 'PROD';
OPT=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.6.1.5)(PORT = 1521))
)
...
16
votes
4
answers
69k
views
Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes
Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
0
votes
1
answer
280
views
How do oracle date time work?
I have following where condition in query. What the time is between T1 and T2 means it includes or excludes the boundary. Also between T1 and T1 means what?I am confused as both returns result.
...
15
votes
2
answers
95k
views
How to free up disk space? which logs/directories to clean?
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
/u01/app/11.2.0/grid/log/...
3
votes
2
answers
2k
views
Oracle explain for cascading delete is wrong
My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it.
If I hit the explain button on a statement like this ...
4
votes
1
answer
5k
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 remote ...
5
votes
1
answer
6k
views
ORA-01017: invalid username/password; logon denied
I've come across the abnormal situation, and can't understand what happens.
I am connecting databases using PL/SQL Developer 8.0.4.1514(I've also tried v7,but same) . Sometimes(When I am trying to ...
1
vote
2
answers
6k
views
How do I manage column order when using PL/SQL's %ROWTYPE records?
I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like:
DECLARE
TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE;
l_data ...
8
votes
4
answers
14k
views
Return a fully dynamic table from an Oracle function
I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
2
votes
2
answers
12k
views
Sum values from two tables ORACLE
I want two values, in two different tables, to be used as a total in a third table.
For instance:
Table accommodation and flight
Add value from acc_price with value from flight_price
Add those ...
4
votes
3
answers
7k
views
Is it safe to delete dummy tables Oracle 11g XE
i created a database schema in 11g express edition, however there are some APEX tables and some dummy data...
Is it safe to delete them? also i would like to know what does apex means and why they ...
2
votes
1
answer
907
views
Reverse engineer Oracle database in visio
Trying to reverse engineer tables added into a database but when i connect, I get strange Chinese symbols with a lot of apex stuff.
I dont see anywhere the tables that i added, what am i doing wrong.
4
votes
2
answers
46k
views
Using Oracle client to connect to remote database
Although I can do a telnet to remote oracle port I am unable to connect.
I have a following tnsname.ora connecting to oracle database using sql plus:
remoteOracle =
(DESCRIPTION =
(ADDRESS_LIST ...
6
votes
2
answers
321
views
Product of n Rows
From this data (assuming the number of rows is not known in advance):
with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4)
select * from q1;
/*
C1
--
2
1
4
1
*/
I ...
4
votes
2
answers
2k
views
Oracle build_plan_xml visualizer
Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML
I now have a very nice looking Execution Plan display for SQL Server (as a result of my ...
4
votes
4
answers
12k
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 ...
2
votes
1
answer
798
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? ...
7
votes
4
answers
2k
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 ...
2
votes
1
answer
669
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
2
answers
323
views
Overhead of DBMS_Profiler
If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling? I realize this will be dependent on what the script is doing,...
3
votes
3
answers
8k
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 how ...
1
vote
1
answer
512
views
Table Statistics Affect Index Only Query?
We have a query that was getting poor performance. The root of the problem could be reproduced using a simple query accessing only one index to retrieve one column (the indexed column) from eight ...
4
votes
2
answers
5k
views
Create unique DB_NAME and DB_DOMAIN for an Oracle 11g instance
I am somewhat confused by these terms in the init.ora file:
db_domain
db_name
instance_name
plus the database name which is provided to the create database command. I do not completely understand ...
5
votes
2
answers
44k
views
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
Couple of days back, I execute shutdown on my DB but it took very long time so I shutdown my m/c. After restarting my machine, I get this exception when I try to connect to my database using TOAD.
I ...
4
votes
1
answer
15k
views
How can I delete a corrupted table space?
Problem:
On an Oracle 11.2g DB;
We have a corrupted TS that does not allow starting the database instance above mount state. The TS cannot be removed with standard methods (i.e., enterprise manager).
...
1
vote
1
answer
350
views
Oracle block usage
I am wondering about a weird deadlock and after checking all other components I am considering it to be a INITRANS problem in Oracle 11.2.
Now three questions arose:
Is there some way of analyzing ...
4
votes
2
answers
3k
views
Oracle 11g see transaction or session start time
I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records?
I've got a transaction id "TX-1234-abcd", a ...
6
votes
3
answers
15k
views
Trouble killing sessions then immediately dropping users in Oracle 11g XE
I would like to be able to kill active sessions by a given user, and then drop that user, and do so within a single database call. Here is what I have so far:
DECLARE
cursor c is select *
...
5
votes
1
answer
996
views
Isssue with Database Links converting OEM Job to DBMS_Scheduler Job
We had an OEM job that would run a PL/SQL block using a particular database users credentials. The credentials are the same for two different databases, so there was no problem for the block to use a ...
2
votes
2
answers
4k
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 ...
3
votes
2
answers
28k
views
Oracle11.2g post-installation problem on Oracle Linux kernel
After installation of Oracle 11.2g in silent mode on the Oracle Linux kernel, I have the following error:
[u01/app/oracle/product/11/bin] $ sqlplus
sqlplus error while loading shared libraries: ...
3
votes
1
answer
455
views
Rman not replacing logs with same name on networked drive
Historically, rman saved logs by replacing files of the same name so that the most recent set was always there, and logs from backup of older than the most recent set simply went away.
Rman is ...
3
votes
1
answer
7k
views
RMAN & Archivelog Deletion Policy confusion
Never been able to fully grasp the notion of delete input/delete input all when backup up Archivelog with rman.
Tied to the same issue is the configure Archivelog deletion policy to backed up X times ...
6
votes
1
answer
3k
views
Are there performance benefits to turning off archivelogging? Is it wise?
The first part of the question is pretty simple; has anyone run a DB in both archivelog and noarchivelog mode? Were there any measurable performance benefits to turning off logging that you could ...