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

2
votes
2answers
68 views

Does my associated ASM Instance have any play in Re-naming my SID?

Greetings DBA Experts, I have changed my Oracle SID in the past by updating the init.ora file, and updating my environment (vars, tns, lintener, etc..), yet not since we moved to 11g with ASM. I ...
4
votes
1answer
4k views

Atypical problem with ORA-01445

I have problem with following query Q1: SELECT ADDRESS.STREET FROM MYSALES.MYADDRESS ADDRESS INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID INNER JOIN ...
1
vote
1answer
142 views

Verify if I can use a given directory

Admin creates an Oracle directory: CREATE OR REPLACE DIRECTORY EXAMPLE AS '\\server\share\test'; GRANT READ, WRITE ON DIRECTORY EXAMPLE TO JOHN; Admin can test whether the directory exists: SELECT ...
9
votes
3answers
297 views

Can I rely on functions being executed first in SQL

Please consider the following script: create or replace function f(p_limit in integer) return integer as begin set_global_context ('limit', p_limit); return p_limit; end; / create view v as ...
5
votes
1answer
7k views

Is there a timeout for a query over database link?

Edit/Preface: This question has been migrated from SO as I am particularly interested on the question on timeouts on DB link queries. The provided workaround from SO is somewhat OK, but I'm really ...
1
vote
1answer
271 views

How to create a new DB with same data from another DB in oracle 10g

I trying to copy entire data from old database to newly created database in oracle 10g. please any one tell me the steps to do the process.
0
votes
0answers
1k views

RMAN Error ( 00601, 03004) [closed]

Please help on this RMAN error: RMAN> backup database plus archivelog; Starting backup at 08-MAY-12 current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=297 devtype=DISK ...
3
votes
3answers
6k views

Login Problem as SYSDBA ORA-01031

I have the following problem: I can connect to an Orcale database as normal user or as sysdba: sqlplus myuser/myPw@myDB sqlplus "sys/sysPw@myDB as sysdba" But when another (Windows-User) is logged ...
3
votes
2answers
8k views

Troubleshooting Oracle ORA-08103 - object no longer exists

I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against it. Since I'm truncating a different ...
0
votes
1answer
3k views

Oracle big datafile restore fails using Netbackup (ORA-19511, ORA-27190)

I am restoring datafiles about 1000GB in size. I am using Netbackup as a backup server. After 2 hours reading Netbackup fails with the following error: 7/25/2012 3:27:50 PM - Error bpbrm(pid=1176) ...
2
votes
2answers
4k views

Oracle aggregate function performance

Can Oracle be smart about aggregate functions, such as MIN(), MAX(), and (AVG)? My testing shows that it seems to be surprisingly stupid. I have the following query: SELECT COUNT(userId), AVG(age), ...
1
vote
2answers
9k views

Changing TimeZone of an Oracle Database

I am looking to change the timezone in a quite large Oracle database. What are some of the issues I might run into when trying to change the timezone in this way? I don't want to 'trash' the ...
2
votes
2answers
3k views

Advanced Replication vs Oracle Streams

I'd like to know what is advantages of Streams over Advanced Replication. I wanted to use updatable materialized views on my project, but on one forum I read comment that if we don't use very old ...
4
votes
2answers
469 views

What are the risks of the parameter PRESERVE_COMMIT_ORDER of Oracle's DBMS_LOGSTDBY?

We have the following setting, with Oracle 10g: One primary database One logical standby database with setup with data guard. The logical standby is only used for generating reports and alike. ...
4
votes
1answer
5k views

What does TOAD do, when I terminate script execution?

When it stops the execution of a long running script. The script terminating needs even longer then the time the script was running. I.e. I have to kill Toad. 2nd EDIT: Just found that this is a ...
3
votes
1answer
9k views

Is it possible to do a datapump import with a different user than did the export?

I have one db dump below is the log file for that dump Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** ...
3
votes
1answer
576 views

Oracle: Interval partitioning and dependant tables

I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to ...
1
vote
0answers
152 views

XML parsing failed

I'm using a query on a oracle db which extracts values from clobs using the xmltype function and an xpath. Unfortunately the query fails and reports a 'XML Parsing failed'. I assume that one of the ...
2
votes
0answers
1k views

oracle crash - not starting

We have a Windows Server 2008 R2 Enterprise x64 Oracle 10.2.0.3 Adempiere - ERP We had a power breakdown at 03.00 AM. At 07.30 AM we started the server At 08.00 our colleagues observed that the ...
5
votes
2answers
5k views

Does order matter for composite primary key in Oracle or any database?

I have a Composite Primary key which is combination of Column1, and Column2. What would be the difference if I have Composite key with Column2, and Column1. Does it even really matter which order ...
1
vote
1answer
505 views

Ora - 00088 error: command cannot be executed by shared server

I get this error from the database when I run the query: select status, error from v$archive_dest where dest_id=2; It's a dataguard environment. I have never seen this problem before. I'm not ...
0
votes
0answers
42 views

Resource to learn writing most efficient and highly optimized sql queries [duplicate]

Possible Duplicate: Learning to Optimize SQL Queries and Understand Execution Plans - Resources? Can some one point me to a great book/great books or some really solid reference/references in ...
1
vote
1answer
645 views

Help with getters & setters in PL/SQL

I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question: I have 2 tables one file level & another batch level. In a day, only 1 file_id is ...
0
votes
1answer
5k views

How to take oracle 10g backup? [duplicate]

Possible Duplicate: How to take oracle 10g backup? I followed this http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php link to create a db backup. I used this command to create db ...
1
vote
1answer
595 views

Oracle out-of-place upgrade on same host: impdp issues

I am testing an out-of-place Oracle upgrade from 11.2.0.2 to 11.2.0.3 on the same host. From what I've read, expdp and impdp is the way to go. My problem is that since I am importing to another ...
0
votes
3answers
609 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
3
votes
1answer
3k views

how to find oracle calculated/computed column list using data dictionary views

I would like to find computed column list in oracle database using Oracle Data Dictionary Views. I would like to add more information. Suppose I have following computed/calculated column in database....
3
votes
1answer
450 views

Log rotation of diag logs?

In on of our Oracle 11.2.0 installations the directory database\diag\rdbms\$DB\$DB\alert is filled with hundreds of xml files (log.xml, log1.xml ...). Can Oracle be configured to do log rotation there?...
3
votes
2answers
1k views

Disabling DBMS_SCHEDULER queue

Despite a lot of searching, I can't seem to find a solution to this problem we are having. Our system enables users to submit certain processes to be run in background while they continue working. ...
6
votes
2answers
1k views

Does it make sense to consolidate Oracle databases?

I am more experienced with SQL Server and Sybase than Oracle, and understand those products well. I've been asked to look for ways to reduce the server estate running Oracle. I understand that an ...
2
votes
1answer
1k views

When is a row added to DBA_HIST_SQLSTAT?

I don't understand the criteria that entails an additional record in the dba_hist_sqlstat view. I have expected there to be at most one entry per sql_id and snap_id, that is, the following statement ...
3
votes
1answer
1k 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....
2
votes
1answer
138 views

Constructing Circularly Referenced Tables

I'm attempting to construct two tables in Oracle, A and B; each of these tables reference each other. In my script, I have the foreign key constraints 'in-line' of the create table statements. Of ...
0
votes
1answer
6k views

Is there a way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases?

I have a master table, and several remote read-only materialized views on pre-built tables that are being fast refreshed to keep in sync with the master table, using a dblink. The table definitions ...
9
votes
3answers
5k views

Oracle: How do I query a Hierarchical table?

Background This is for the construction of some views we'll be using for reporting. I have an table of locations, the key fields being "location" and "parent". The structure that these two fields ...
4
votes
1answer
792 views

How to use simple output parameters?

I'm new to Oracle, so I apologize for such a simple question, but I've already wasted several hours on it. I have the following stored procedure: create or replace procedure MyTestProc (outparam OUT ...
3
votes
1answer
2k views

How do I have RMAN treat my restored data files like they are originals?

I am planning a migration for this ancient-but-critical Oracle database from one Windows Server 2003 cluster to another. The database has many data files and they are all getting new paths on the ...
4
votes
1answer
2k views

oracle failsafe - how to start instance in nomount

Using Oracle Failsafe, how can a instance be started up in nomount mode? If the instance is shutdown via sqlplus, the windows cluster manager just starts it up again. If the instance is shutdown via ...
2
votes
1answer
792 views

Skip running trigger during cascade delete?

I have a legacy application where I have two tables X and Y. Table Y has a trigger that performs an action automatically on delete. There is also a foreign key with cascade delete defined between X ...
4
votes
1answer
2k views

Oracle subquery column aliasing syntax?

I have something like: SELECT a, b INTO v_a, v_b FROM ( SELECT x, y FROM whatever UNION SELECT z, w FROM something ) AS subquery (a, b) Which of course doesn't work in Oracle (...
4
votes
1answer
3k views

How, if possible, can an .sql file be executed against an oracle database from a bash script

Is it possible to execute an sql file (a text file containing several SQL statements) against an oracle database from a bash shell command line (e.g. no graphical GUI and no interactive clients) ...
1
vote
1answer
7k views

€ Euro sign/symbol not showing up on Oracle 10gr2 and aix 6.1

this is Oracle 10gr2 on aix 6100-02 The issue is that I do not manage to see the € sign from Oracle. This is an easy way to see the issue: SQL> insert into prueba values ('€'); SQL> commit; ...
10
votes
1answer
759 views

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
3
votes
0answers
744 views

Mismatched Oracle drivers - need upgrade (10.2.0.1 to 10.2.0.5)

I was forwarded here by recommendation from Stack Overflow ...... We are running Oracle Enterprise software on various client and DB servers and we are experiencing a problem (outlined here) where ...
0
votes
0answers
60 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 ...
0
votes
1answer
3k views

Incremental level 1 backup with no Incremental level 0?

I have done the following steps: Backed up database with INCREMENTAL LEVEL 0 Delete INCREMENTAL LEVEL 0 backup without telling controlfile. So deleted it by hand. And did not make crosscheck. Backed ...
2
votes
2answers
106 views

Incremental backups - differences

What is the differene between: BACKUP INCREMENTAL LEVEL 0 DATABASE; and BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; ? I read some manuals, but I don't get the idea beacuse of my poor ...
4
votes
2answers
2k 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 ...
3
votes
2answers
1k 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 ...
2
votes
1answer
96 views

Why does connecting to a database use tempspace?

Earlier today we had a problem logging into a database that had just been created automatically. When on the actual box (Linux) sqlplus schema/pw, i.e. a local connection, was fine, though, it was ...