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
1answer
4k views

creating buckets in oracle sql

I saw another post where they showed you how to do this with a case statement and had you define your buckets in advance. how do I make it dynamic? I know about the width_bucket and ntile functions. ...
3
votes
2answers
2k views

How does Oracle handle composite index lookups?

MySQL cannot use a composite index in a lookup in which the WHERE condition doesn't include the columns forming a left-most prefix: MySQL cannot use the index to perform lookups if the columns do ...
7
votes
4answers
7k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
0
votes
3answers
240 views

How can I select data and the total to cursors?

I have a table like this: ID | Name ------------ 1 | AA 2 | AA 3 | AA 4 | BB I want to select all rows where Name like 'AA' in a cursor and get the count from this result in another ...
2
votes
4answers
970 views

Tool to monitor database data

I would like to tool that when record data changed inside all tables. so later i would get a report like table user added record - 3,user3.. deleted record - 2,user2..
2
votes
2answers
1k views

Oracle - REPCAT$ Tables

I accidently dropped the REPCAT$_USER_PARM_VALUES table from the system schema in my oracle db. Will it cause any problems in the future? What are these repcat$ tables for?
25
votes
5answers
122k views

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...
4
votes
5answers
2k views

Modifying table structure within a transaction?

In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction. We have a tool (written in Java) that should modify a schema by: adding ...
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 ...
2
votes
2answers
265 views

How to compare the database of a backup from another environment

Has anyone had any luck with taking a backup (approx 30 tables) from an Oracle database's live environment, then loading a test environment with this backup and doing a comparison between the test ...
0
votes
1answer
841 views

What network access do I need when I restoring files using RMAN?

I am restoring files from one server to another server using Oracle RMAN, what network accesses do I need?(between these servers) ftp? port 1521 should be open? or what? I appreciate your answer ...
1
vote
1answer
184 views

Oracle Backup/Recovery architecture

When we write the following: allocate channel ch1_tape type sbt; set controlfile autobackup format for device type sbt to '%F'; restore controlfile from autobackup; RELEASE CHANNEL ch1_tape; By ...
3
votes
3answers
1k views

Convert Oracle query to MySQL

I've just tried to convert this update command, which is Oracle sql, to MySQL with SQLWays and it failed with the following error: You can't specify target table 'animal_table' for update in FROM ...
7
votes
1answer
26k views

PLS-00306 Error: How to find the wrong argument?

PLS-00306: wrong number or types of arguments in call to 'string' Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The ...
3
votes
2answers
892 views

What is an Effective Way to Count the Number of Pages in Oracle?

We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results. We build implement paging by putting the original query inside a ...
4
votes
3answers
27k views

Oracle 11g - Create Database fails?

I've just installed Oracle 11g and linked it to SQL Developer. I want to create a database for my django install. CREATE database 'django'; Error starting at line 1 in command: create database '...
3
votes
1answer
714 views

Oracle RAC can you have a cluster that contains one node

Have oracle RAC production system. Two nodes in the primary, two nodes in the secondary. Getting a test system.Can we have two nodes in the primary and one in the secondary? Or to put it another way -...
1
vote
3answers
360 views

Compare content of databases in Oracle

We have a request to build an ASP.Net application that will be used as an interface to launch commands on Oracle databases. We need to find a way to compare the content of 2 databases. We prefer not ...
4
votes
2answers
619 views

Learning Oracle, does it matter if I install 10g or 11g?

I'm taking a class in Databases, they're using Oracle 10g in class, but I'm having some trouble installing the database here in Windows 8 (auth problems), so I thought I'd attempt 11g. Is 11g ...
3
votes
1answer
19k views

How to query given grants on Oracle?

On a database I had to execute the following two statements: GRANT CREATE ANY TABLE TO MyUSER; GRANT DROP ANY TABLE TO MyUSER; What I miss is the query which tells me that those grants are given. I ...
4
votes
2answers
16k views

How to replace a CLOB column by a varchar2(4000) column?

The following code seems to do the job, but I doubt, that it is robust enough. SET serveroutput on DECLARE v_isclob PLS_INTEGER; v_cmd VARCHAR2(4000); BEGIN SELECT CASE when exists (...
1
vote
1answer
691 views

If archiving is disabled and the last redo log file is full, why LGWR continues by overwriting the first available active file, not inactive file?

I found the following sentence from Oracle documentation. If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting ...
2
votes
1answer
278 views

Does 'LGWR' writes to online redo log files strictly in circular fashion?

Does 'LGWR' writes to online redo log files strictly in circular fashion? I saw this sentence from Oracle documentation. If archiving is disabled (the database is in NOARCHIVELOG mode), then when ...
2
votes
1answer
148 views

Oracle Backup and Recovery Issue

I have backed up database located on Host1 and restored/recovered it on Host2. After a while, I backed up DB on Host1 again. Now I want DBs on Host1 and Host2 be the same using last backup. How can ...
2
votes
2answers
6k 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 ...
5
votes
3answers
1k views

Gathering information to tune oracle row prefetching

I'm trying to determine a value for the number of rows an oracle client will prefetch (i.e. OCI_ATTR_PREFETCH_ROWS, exposed as setFetchSize in JDBC) that will minimize the number of server round trips ...
4
votes
2answers
4k views

Consequences of changing NLS_TIMESTAMP_FORMAT

I administer a 9.2.0.8 database where the NLS_TIMESTAMP_FORMAT is set to 'DD-MON-RR HH.MI.SSXFF AM';. This leads to odd results when using the CAST TO TIMESTAMP function: select CAST('14-SEP-2011' ...
8
votes
1answer
4k views

When is an application using too many sessions?

I'm working on a client-server application that uses OCI to communicate with Oracle. In the past our application often treated sessions as an infinite resource, quite possibly to the detriment of ...
1
vote
2answers
412 views

What does it mean by '4M blocks' or '4G blocks' in Oracle?

The following 2 sentences are excerpt from Oracle documentation. You can easily fine them by google them directly. My question is, 4G and 4M are the unite of digital information storage. What's the ...
9
votes
1answer
2k views

Do Oracle databases use Java internally?

My question is fairly simple. Is Java used internally by Oracle in their DBMSs?
6
votes
3answers
1k views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
0
votes
1answer
714 views

Any disadvantage to using oraToolkit for installing oracle

I am going to install oracle 11gR2 on a redhat enterprise 5.6 machine. Is there any disadvantage to using oraToolkit? For example, could it cause any problem or difficulty for future complimentary ...
4
votes
4answers
18k views

What is difference between Oracle Apps and Oracle Application Server?

I am very new to oracle, could anybody tell me what Oracle Apps and Oracle Application Server are.
7
votes
2answers
276 views

Why is Oracle using the index here?

Name Null Type -------- -------- --------- ID NOT NULL NUMBER(4) GROUP_ID NUMBER(4) TEXT CLOB There is a btree index on group_id. Here's how many ...
4
votes
1answer
762 views

Is it safe to let application administrators look at v$session, v$process, and v$sql?

In an Oracle database shared among multiple application, is it 'safe' (from a security and system integrity perspective) to allow application administrators access to v$session, v$process, and v$sql? ...
3
votes
2answers
19k views

ORA-01113: file # needs media recovery ORA-01110

I have mistakenly added datafile to the tablespace... And was trying to drop it. I run the following: alter database datafile 'datafile_name' OFFLINE; now when I run alter database datafile '...
5
votes
1answer
325 views

DBA's Scope in datawarehousing Projects

As a DBA trainee, what needs to be known in order to be part of datawarehousing projects? I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used ...
4
votes
3answers
8k views

Does an Oracle nvarchar2 take twice as much space as varchar2?

Regarding point 4 of this question (which is in reference to MS SQL Server): VARCHAR does not store Unicode characters. NVARCHAR does store Unicode characters. Today's applications should ...
1
vote
3answers
2k views

Undo DDL statement on View in Oracle

I Want to undo DDL changes on View, how to do that? I have not set utl_file_dir parameter and can't restart the database to set it. So log miner can not be used yes? it needs this parameter... If ...
2
votes
2answers
242 views

What is an industry standard method for verifying openquery connection is open prior to running queries?

I'm a programmer, but this question is more specifically related to databases so I'm posting it among this community. It could very well cross over into stackoverflow as well. I'm seeking an ...
2
votes
3answers
947 views

Oracle get sql redo from log_history

I run into a problem with a customer which had no proper backup. His last backup (meaning exp-dump) is from 1st Aug 2011. However he lost due to batch DELETE statements all data. My plan was now to ...
4
votes
3answers
6k views

Oracle 11g setup closes without errors on pre-requisite check

I'm installing Oracle 11g and the setup wizard just closes automatically after the pre-requisite check. I have administrative access UAC is disabled IPv6 is diabled There is more than enough free ...
7
votes
3answers
36k views

What are the causes and solutions for mutating table errors?

I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but ...
2
votes
2answers
2k views

Is it possible to query uncommitted data of a different session? [duplicate]

For interactive debugging purposes I'm wondering whether it's possible to query the data another session has currently put into the database but not yet committed. Say I have an my application open ...
2
votes
1answer
6k views

How to see today compiled object in Oracle?

Is there a way to find objects that were compiled today?
7
votes
5answers
18k views

Oracle returns an error ORA-01031: insufficient privileges

Oracle returns an error "ORA-01031: insufficient privileges" When I run sqlplus / as sysdba but sqlplus sys/oracle123 as sysdba doesn't, why? Oracle version: Oracle Database 10g Enterprise Edition ...
0
votes
1answer
2k views

Materialized views master table

New to this topic - in my current environment we are not doing any replication but we use materialized views frequently in order to achieve performance gains on complex views that are in turn used by ...
1
vote
1answer
132 views

Reasons against using local indexes

For Oracle 10g and above: except in cases where a local index cannot be used and a global index is needed (e.g. unique constraint), is there any reason to favor a global index over a local index?
0
votes
1answer
3k views

V$SQL table elapsed time

Does the elapsed_time column in the v$sql table indicate the time per query exeuction or is it a sum of all executions?
2
votes
1answer
729 views

Using the special Dual table to read sequences

It there a performance impact when using the dual table to read a sequence' nextval over an direct insert? I'm currently optimizing the performance in a large IT system and what I've found is that ...