All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

12
votes
1answer
9k views

What are the benefits of packages over standalone procedures and functions?

Looking for a canonical answer to the question of why packages should be preferred in an Oracle database over stand alone procedures and functions.
4
votes
2answers
28k views

Determine Oracle session client character set?

I know how the database characterset (NLS_CHARACTERSET in select * from v$nls_parameters;) and the client character set (the client environment setting NLS_LANG) interact. What I can't find out ...
1
vote
2answers
644 views

Converting an SVN repository to SQL database

I have downloaded the svn repository of Atunes using TortoiseSVN. I would like to transfer the logs of activity of the developers to a SQL database, preferably an Oracle one. Is there any software ...
1
vote
1answer
637 views

ORA-24237: object id argument passed to DBMS_UTILITY.INVALIDATE is not legal

I have had invalid public synonyms in my database. Some of them references to ordinary schema objects and others to sys schema objects. I compiled all these public synonyms by the following command: ...
2
votes
2answers
182 views

State of the art self healing and self protection in DBMS

At the moment I'm writing a paper and I need some input about the state of the art of self healing and self protection in DBMSs. Does somebody read some interesting articles or books about this topics ...
3
votes
1answer
844 views

In a two-server dataguard setup, can I store the recovery catalog in the same database?

We currently have an Oracle 11gR2 database that is backed up with rman, with the rman records stored in the control files. Control file auto-backup is turned on. Backups are to disk (and, ultimately, ...
1
vote
1answer
497 views

schema shows through standard client connection but not through ODBC connection?

Hoping you can help! The Gist When I log into an Oracle 11g DB using SQL Developer or DBVisualizer, I can see schemas and execute queries against them. However, when I use the same credentials to ...
2
votes
2answers
11k views

How to monitor backup jobs on Oracle 11g?

Background: I'm trying to monitor that backups run as expected on several databases. Zenoss is used to monitor most other stuff. A couple of KPIs are already monitored using Zenoss in combination ...
1
vote
0answers
3k views

Convert the Highvalue of user_partition in varchar2

I have a requirement where I would like to use the HIGH_VALUE field of user_partition table as a Varchar2 or TimeStamp value and I would like to get it used as a Function in SQL Query. Something like ...
1
vote
1answer
2k views

Oracle XE 11.2 Export / Import Charset

I created a database in oracle XE 11.2 that uses charset AL32UTF8 that I now want to export. When I perform the export, the resulting data appears in WE8MSWIN1252 character set and AL16UTF16 NCHAR ...
0
votes
2answers
8k views

work with json in oracle

Is there an easy way to work with JSON within Oracle? I have a stored procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but ...
1
vote
1answer
144 views

Use of '@' '.' and 'public operator/keywords in Oracle database?

I got into a weird SQL query-patch which looks for @ . and public keywords. I was wondering what is the purpose of these operators/keywords? I suspect that it might be looking for SQL injection or ...
4
votes
2answers
6k views

RMAN-06054: media recovery requesting unknown archived log, Where is SCN kept?

I have the following situation: Backups of DB1(located on srv1 server) database are taken everyday at 1:00 AM. I took backed up files(datafile,archivelog,controlfile,spfile) of 24/10/2011 and ...
5
votes
2answers
6k views

Oracle RAC failover not working correctly

I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both ...
0
votes
1answer
286 views

How to find equivalent of Oracle's privileges in SQL Server?

I have a user in Oracle for which the permissions given are CONNECT and SELECT_CATALOG_ROLE. How do you find the equivalents when setting up a similar user in SQL Server?
2
votes
1answer
6k views

Oracle Wallet Setup ORA-12578

I am having trouble setting up the Oracle Wallet on a new box connecting to a new database. I have added the following lines to my SQLNET.ORA: SQLNET.WALLET_OVERRIDE = TRUE WALLET_LOCATION = ...
1
vote
1answer
3k views

function based index as a conditional unique key?

I have a search table which has a generated primary key PK_ID,and also has primary keys from three or four other tables in the FK_ID column CREATE TABLE METADATA ( PK_ID NUMBER(10) ...
1
vote
1answer
410 views

How to implement a low resource privileged user on Oracle 10g using Resource Manager?

In my database I have users and their respective grants using the least amount of privileges principle. How do I start taking advantage of resource manager to prevent users from hogging all the ...
13
votes
8answers
19k views

Tools to monitor Oracle Database Performance on Standard Edition

Here is what I am looking for: Works on Standard Edition i.e. No Diagnostic Pack. Provides both current and historical performance data. Good visuals for easy spotting of trends and presentation ...
2
votes
1answer
430 views

Oracle Change Data Capture: Unsubscribed column results in change set record

I am using Oracle 11g's Change Data Capture to track changes on a database table. I am using synchronous mode to minimize latency. Here's an example to illustrate my problem: Published_Table ...
7
votes
2answers
3k views

Oracle 10g - How to prevent one user from hogging all resources?

I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run ...
4
votes
2answers
6k views

Generate sample data for an Oracle database [closed]

Good morning, What free [or trial] software can I use to generate sample data for an Oracle database? I've been trying with various tools, but none work. Thanks for all suggestions, Alec Taylor
2
votes
1answer
2k views

ORA-01555: snapshot too old: rollback segment number with name “” too small

This error appears when I am trying to move LOB segment from one tablespace to another one. Also during exporting the same error appears. What can be a problem?
1
vote
2answers
1k views

Oracle hierarchical query question (start with … connect by … )

From the Oracle documentation: To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row ...
2
votes
1answer
3k views

Eliminate database fragmentation (export, drop and re-import tables)

I have one datafile that is highly fragmented. Its size is 10GB now and real size that is used is 2.5GB,so 7.5 is free. I decided to export data, drop that datafile, recreate it and import data. ...
4
votes
2answers
727 views

Migrating to Oracle Flashback for Historization on Oracle 11g

Our application built using Oracle 11g requires that we keep historic data. The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically ...
8
votes
1answer
7k views

What specifically does OracleBulkCopy do, and how can I optimize its performance?

To summarize the specifics: We need to stage approximately 5 million rows into a vendor (Oracle) database. Everything goes great for batches of 500k rows using OracleBulkCopy (ODP.NET), but when we ...
2
votes
1answer
102 views

What is the best architecture to run Oracle sucessfully run on VMWare?

Due to a data center move we are mandated to migrate all servers to virtual machines. We currently have a 3 Node Oracle RAC running on physical servers. There seems to be a great debate about the ...
3
votes
1answer
3k views

Oracle permission issue

Please see the following statements. My question is, the table t1 was created in the users tablespace, so it will take up some space. But why it can be created before I impose the quota on the users ...
2
votes
1answer
5k views

In Oracle, if parallel_max_servers is set to be significantly less than the DOP can the explain plan be sub-optimal?

For tables that have DEGREE DEFAULT, the degree of parallelism (DOP) is calculated by Oracle using number of CPUs and number of threads per CPU amongst other things. As I understand it, when costing ...
4
votes
1answer
3k views

What's the difference between quiesce the instance and enable restricted session?

You can set the database in quiesced mode by the following command. alter system quiesce restricted; After running this command all sessions except the ones that have DBA privileges will hang ...
2
votes
1answer
2k views

Find fragmented space free below HWM Oracle

That High Watermark Value always eludes me when calculating free space in my tablespaces. Will the following query consider fragmented free spaces of object below the HWM value for each TS? select * ...
2
votes
1answer
133 views

Oracle Control and Webserver

How does the Database Control work, since Apache is not running on this computer? The Url says https://localhost:1158/em/console/logon/logon. I do have a WAMP apache server on here but I have to shut ...
7
votes
5answers
10k views

Auto-Commit in SQL Server and Oracle

When I worked on Oracle 8 many years before, I used to execute COMMIT command manually after each bulk INSERT. In SQL Server, Auto-Commit is ON by default which has advantages as well as hazards. I ...
2
votes
2answers
5k views

min(column) with nulls?

I am new to databases, but I hope you can help me to understand something. Take part of this query: select min(date) as date... Assume that some of the dates are null. What effect would it ...
2
votes
1answer
4k views

Best methods for ingesting XML into Oracle 11gR2

I have a large Oracle 11g Instance. I now have a requirement to convert large amounts of XML into records in several related tables. The xml will arrive frequently at irregular intervals. The ...
5
votes
4answers
242 views

What's the best way to list groups of values that are contained in other groups

For example, starting with a table like this: create table t as select 'A' as x, level as y from dual connect by level<=5 union all select 'B' as x, level+2 as y from dual connect by level<=5 ...
2
votes
1answer
979 views

How to disable instance recovery in ORACLE?

I want my DB to stop instance recovery during opening, how to do it? I want it because it crashes when trying to do instance recovery, I am testing some cases and want to disable it Is there any ...
1
vote
1answer
589 views

Oracle 11gR2 - Limits on Materialised View replication between databases

We are about to deploy a 3rd party product running on Oracle 11gR2. The product allows very generic text searches. We'd like to mitigate the performance impact of the searches. Our current design is ...
2
votes
2answers
1k views

Is it possible for me to be notified if an 'after insert or update trigger' stops working?

Last week, I found my after insert or update trigger wasn't working. After I disabled and enabled it, it started working again. I do not yet know why it stopped working. Is there any way to deal with ...
1
vote
2answers
9k views

How to connect to a local server in Oracle SQL Developer like SQL*Plus with no TNSlistener?

How can I use SQL Developer to connect to a local Oracle 11g server? I didn't enable TNS Listener, but SQL*Plus works well. @update1: I copied all *.jars from ...
3
votes
1answer
747 views

Will {fn concat} work without issue in MySQL, SQL Server and Oracle?

According to this (quite old) article the principle behind ODBC escape sequences is that The ODBC driver reads the escape sequence and translates it into the DBMS-specific syntax before sending ...
0
votes
1answer
649 views

What are the other types of database architectures? [closed]

Oracle, for example has a typical client server architecture, and a memory cache (SGA) with some background processes. This is one way of designing a database. Are there databases out there which ...
1
vote
1answer
1k views

How to drop materialized view logs if underlying log table ($MLOG_…) is dropped?

In an attempt to work around another problem, I have mistakenly dropped the underlying tables for a materialized view log, and now I can neither drop that log nor the MV relating to it. Our DBA says ...
2
votes
3answers
565 views

Book for Learning RAC

What is a good book to read to learn about RAC? I am most interested in the practical end rather than the theory behind it.
0
votes
1answer
2k views

Problem with dropping materialized views and mv logs taking a long time (or never) to complete

Apologies if it's a simple question, DBA is not my main area of expertise! I have a materialized view that's fast refreshable, joining two tables. When I try to drop either the MV itself or logs ...
1
vote
1answer
1k views

The Database Session is automatically Getting Killed

I am running a java program which connects to the oracle db and retrieves some information. The program contains 12 threads, each thread interns execute sql select commands with multiple join ...
4
votes
3answers
1k views

Recreating mat. view in Oracle failing due to dba_summaries entry, how to prevent?

I have run into the issue described here several times in our current project and I would like to understand how it happens and how to prevent it in the future. What is the meaning of DBA_SUMMARIES ...
3
votes
3answers
13k views

Oracle Enterprise Manager (OEM) locking SYSMAN account

Why does Oracle Enterprise Manager reset my SYSMAN account to LOCKED(TIMED) status? I've tried rebuilding Enterprise Manager, and re-installation of Enterprise Manager, which always works, until the ...
0
votes
1answer
2k views

32-bit versus 64-bit ODP.NET to Oracle connection

I seem to be having an issue connecting a 32-bit .NET application to a 64-bit server, without a full Oracle client. If I build a 64-bit application, using the 64-bit ODP.NET dll's or a 64-bit instant ...