All Questions

Tagged with
Filter by
Sorted by
Tagged with
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....
Leigh Riffel's user avatar
  • 23.9k
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 ...
pilcrow's user avatar
  • 360
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 ...
Jay Sullivan's user avatar
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 ...
Josh Smeaton's user avatar
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?
Leigh Riffel's user avatar
  • 23.9k
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.
user419534's user avatar
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 ...
orbfish's user avatar
  • 255
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 ...
orbfish's user avatar
  • 255
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 ...
orbfish's user avatar
  • 255
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 ...
orbfish's user avatar
  • 255
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 ...
user419534's user avatar
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 ...
Mike's user avatar
  • 1
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 ...
Lars's user avatar
  • 233
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 =&...
wweicker's user avatar
  • 133
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 ...
Peter Penzov's user avatar
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 ...
Leigh Riffel's user avatar
  • 23.9k
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)) ) ...
kinkajou's user avatar
  • 479
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 ...
Leigh Riffel's user avatar
  • 23.9k
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. ...
kinkajou's user avatar
  • 479
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/...
kupa's user avatar
  • 1,707
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 ...
Franz Kafka's user avatar
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 ...
kinkajou's user avatar
  • 479
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 ...
kupa's user avatar
  • 1,707
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 ...
Glenn Moss's user avatar
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 ...
Michael-O's user avatar
  • 510
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 ...
David Garcia's user avatar
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 ...
David Garcia's user avatar
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.
David Garcia's user avatar
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 ...
kinkajou's user avatar
  • 479
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 ...
Leigh Riffel's user avatar
  • 23.9k
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 ...
Jake Feasel's user avatar
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 ...
René Nyffenegger's user avatar
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? ...
acorello's user avatar
  • 397
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 ...
Ben's user avatar
  • 462
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 ...
Femme Fatale's user avatar
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,...
Leigh Riffel's user avatar
  • 23.9k
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 ...
hdaz's user avatar
  • 31
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 ...
Leigh Riffel's user avatar
  • 23.9k
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 ...
Michael-O's user avatar
  • 510
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 ...
user613114's user avatar
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). ...
stmoebius's user avatar
  • 197
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 ...
Franz Kafka's user avatar
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 ...
Franz Kafka's user avatar
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 * ...
Jake Feasel's user avatar
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 ...
Leigh Riffel's user avatar
  • 23.9k
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 ...
Mehmet Balioglu's user avatar
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: ...
user1182411's user avatar
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 ...
michelle daniel's user avatar
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 ...
michelle daniel's user avatar
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 ...
Ben's user avatar
  • 462