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

10
votes
1answer
1k views

Do Oracle databases use Java internally?

My question is fairly simple. Is Java used internally by Oracle in their DBMSs?
6
votes
3answers
858 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
694 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
3answers
14k 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
274 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 ...
3
votes
1answer
716 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
15k 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
319 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
7k 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
1k 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
241 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
894 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
2answers
5k 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 ...
5
votes
3answers
29k 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
1k 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
5k views

How to see today compiled object in Oracle?

Is there a way to find objects that were compiled today?
7
votes
4answers
17k 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
676 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 ...
10
votes
5answers
17k views

Cannot drop nonexistent constraint and cannot create it either

While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands: ...
4
votes
1answer
587 views

Can Oracle Text query return the terms found?

I am querying an Oracle 10g database. The database contains a table with documents that has an oracle text index. I query the database using a query like SELECT SCORE(1), title from news WHERE ...
2
votes
2answers
2k views

Materialized views use older dbms_job instead of scheduler?

Very new to this topic - using 11g. When I create a materialized view, a matching job (not a scheduler job) is created. But it's my understanding that dbms_job is being replaced by dbms_scheduler. ...
1
vote
2answers
176 views

Functioning of Oracle's Server result Cache

I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] : PL/SQL function code can include a request to cache its results. Upon invocation of this function, the ...
1
vote
1answer
6k views

Cleaning Oracle In-Active Sessions

There a are huge number of in-active sessions persisting in our database.How can we clean these in-active sessions? Database - Oracle 10g R2 with ASM DB Size - 5TB SGA - 78GB PGA - 10GB Maximum ...
2
votes
2answers
971 views

Get timing on individual queries in Anonymous code block

I have a set of queries that I need to run in succession, carrying the result from the previous query into the next. My only need is the execution time on the last query in the list, but SET TIMING ON ...
3
votes
3answers
5k views

What are the mandatory/required oracle 11g tablespaces?

Ok, what are the mandatory tablespaces in oracle 11g. My research has lead me to believe there are three of them: System, Sysaux, and Undo. However since I am taking the OCA and all the ...
9
votes
2answers
5k views

Updating 700 million rows to same value

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows. I don't have admin access, or access to an admin, so this needs to be accomplished with ...
4
votes
3answers
1k views

How and what do you backup for a simple Oracle DB?

Background: I am in the following situation: We have a JIRA instance running on a dedicated server machine (Windows Server). It uses Oracle XE to store all the data. The JIRA instance and a Oracle ...
4
votes
3answers
221 views

Are there any tables that I can get all Oracle supported system privileges and object privileges?

Are there any tables that I can get all Oracle supported system privileges and object privileges? I know there is a view called all_sys_privs in Oracle. But I can't find more information about it. ...
1
vote
1answer
1k views

OEM Grid Control Template

I create an monitoring template for my database on OEM GC, apply it on somes database and now i want to know what database is using this template. Can someone help me please?
2
votes
1answer
99 views

How to make a duplicate field cheap?

There are two tables associated with each one to many in a field. In Table 2, there are fields that duplicate the value in Table 1. Is there a cheaper way to ensure (guarantee) the correspondence ...
0
votes
1answer
3k views

Unattended/silent install of Oracle XE Express fails

I am experimenting with silent installs of Oracle 10g XE on a fresh XP VM. I have an iss file, copied from the standard one from oracle, called "foo.iss" Here is my command: >OracleXEUniv.exe /s ...
3
votes
2answers
525 views

Join condition on complex views

If i join two/more complex views (views that are made up by joining two/more tables); would it result in multiple iteration of reads on the base tables? Would it not be more efficient just to have ...
7
votes
1answer
293 views

What is “SAP on Oracle”

OK, total newb question. What is "SAP on Oracle", exactly. This link is talking about how SAP can run on Oracle (I think). I'm not understanding how this is working. My understanding: SAP is a ...
2
votes
2answers
559 views

Full Name search query

We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please. We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and ...
0
votes
2answers
584 views

FBI on function with concatenated string

I have an index in place (from days of old) that I'm curious about. It's on a basic person table and the function is upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME") When trying ...
2
votes
1answer
464 views

Oracle Database Block Size

One of our Production database has a rather peculiar block size of 16KB. This database was created about 2 years ago and at that time the size of the db was approx 1TB only.However over the past 2 ...
2
votes
1answer
281 views

Shortest Query to Combine Count and Unique (Oracle)

I have the following query which works for me: select count(*) from ( select UNIQUE col1, col2, col3 from my_table where cond1 = 'A' and cond2 = 'B' and cond3 = 'C' ) Is it possible ...
3
votes
2answers
771 views

Oracle 10g Storage Migration

We need to migrate the storage of our production database. What is the appropriate method of doing so and what specific/generic precautions do we need to take?? Database Configuration: Volume Manager ...
3
votes
2answers
2k views

Oracle 10g SGA sizing Help

Help in sizing oracle 10g database sga We've an Oracle 10g r2 database, size = 4.5TBASM is used to manage the storage. It uses "External Redundancy" option on "Raw Device" (i.e. no os file system ...
2
votes
1answer
557 views

Can you trigger an action when a view is created?

Is it possible to create a trigger that fires when a view is created on a user schema? I want to create a meta data table which will hold all the views and a listing containing what they are supposed ...
2
votes
1answer
13k views

How to display a number with fixed number of digits before the decimal?

I am looking for result 0123.56 and the following query should have worked. SQL> select to_number(123.56,'0999.99') from dual; select to_number(123.56,'0999.99') from dual * ERROR ...
3
votes
1answer
1k 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 ...
4
votes
1answer
12k views

oracle format specifiers: to_number vs to_char

SQL> select TO_NUMBER(123.56,'999.9') from dual; select TO_NUMBER(123.56,'999.9') from dual * ERROR at line 1: ORA-01722: invalid number SQL> select TO_CHAR(123.56,'999.9') ...
4
votes
2answers
15k views

Which picture does the ORA-01830 error message refer to?

ORA-01830: date format picture ends before converting entire input string About the error message. I am confused. Which picture is the error message talking about? All I provided was a date.
2
votes
1answer
307 views

Tablespace consumed by views

I was in an SQL class where the instructor said "DBAs don't like it when users create views, because they will clobber tablespace." Obviously, I understand that any schema object will consume some ...
2
votes
4answers
157 views

Options for dumping Oracle queries to text files with no database server filesystem access

I've an application currently producing reports using UTL_FILE and DIRECTORY. We are migrating this application to an environment in which there will be no filesystem access on the database server ...
3
votes
4answers
167 views

How implement a cancel attribute in a table?

Which is the best way to implement a canceled attribute in a database table, provided that less than say 3 % of the rows are canceled. For canceled data I want to store the additional information ...