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

1
vote
1answer
7k 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
1k 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
6k 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 questions(...
9
votes
2answers
6k 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 ...
3
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
229 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
2k 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
4k 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
553 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
301 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
621 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
683 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
471 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
293 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
813 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
3k 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
589 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
17k 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 ...
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 ...
5
votes
1answer
14k 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
16k 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.
3
votes
1answer
344 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 ...
3
votes
4answers
162 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 (...
4
votes
4answers
173 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 ...
5
votes
5answers
733 views

EXISTS clause in SQL

I have following requirement: main_set table has all the ids, user_input table has all user entered ids. I want result of ids that exist in user_input table. If nothing in user_input table then I ...
11
votes
4answers
8k views

Is Oracle more demanding environment for a DBA than SQL Server? [closed]

I don't know Oracle at all but I have heard Oracle DBA's saying that working with Oracle (as a DBA) is "harder" and more difficult and more demanding than working with SQL Server. Does this statement ...
8
votes
1answer
18k views

What makes an Oracle Applications DBA different from an Oracle DBA?

All Oracle databases support some type of application that the Oracle DBA would need to have some knowledge of. What makes an Oracle Applications DBA different than this? Does the term mean that ...
2
votes
1answer
251 views

Where to put glogon.sql in 32-bit ODAC (11.2.0.2.1)?

I installed the 32-bit ODAC (11.2.0.2.1) from here. The Oracle Universal Installer version contains Oracle SQL*Plus 11.2.0.2.0. But I'm missing the sqlplus/admin/ folder in the installation, where I ...
6
votes
2answers
2k views

Oracle clean up

I have an Oracle database on a VMWare image that seems to be slowly eating all my available disk space. I would like to know if there is a command or script that would perform some kind of clean up of ...
4
votes
5answers
22k views

How check that Oracle Database is Up?

For our application we have configuration file for connection to the Oracle Databese (url, user, pass). Application start for 2-5 minutes. If some wrong with databese i see this in logs, but i must ...
4
votes
3answers
950 views

What are the 'free to use' workload generators available for Oracle? [closed]

A couple that I know about are http://www.dominicgiles.com/swingbench.html and http://hammerora.sourceforge.net/ Are there any others? I am looking for tools what can generate random workload (...
4
votes
3answers
767 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
3
votes
2answers
3k views

How do I group results in batches of consecutive rows with the same 'module'

I'm looking for a seemingly very simple SELECT. I have a logging table LOGGING of which three columns are of interest to me: MODULE (text), PDATE (date), PTEXT (text). Actually, I care only about ...
14
votes
3answers
2k views

Is there any point in undersizing VARCHAR columns?

Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not. I would like to give the question of VARCHAR size a little twist and hope ...
0
votes
3answers
288 views

What all are the recoverable points of failure in an Oracle database?

I am testing a product against an Oracle database for failure scenarios. The product talks to Oracle through a session and maintains a few tables. I want to make sure that whenever Oracle recovers ...
1
vote
1answer
99 views

Can I install an additional oracle database on one node of an oracle cluster (RAC)?

Let's say I have a two-node Oracle RAC environment. Can I install an additional Oracle database on one of the node? Thanks.
1
vote
3answers
421 views

When installing Oracle, what's the quickest way to check rpm packages and install what is missing?

When installing Oracle, there is a step which is to install all necessary packages. I use the following command to test whether a package is installed and install them if they are missing: rpm -qa | ...
2
votes
3answers
2k views

when selecting from multiple tables I get a multiplication of the records

When executing this query below it takes too long. And the result is too much records, more than possibly in the database. The query should return around 117.000 records. That is the amount of ...
3
votes
2answers
18k views

What is the best way of checking the current user privileges on a particular table from another schema?

In my software, at runtime, I need to check if the connected user has the privileges on some tables, across 2 different schemas. From what I found doing research, I came across these 2 views ...
5
votes
2answers
2k views

What storage engines work with Oracle?

http://en.wikipedia.org/wiki/Database_engine mentions about database engines aka storage engines. What all storage engines can be used with the Oracle database?
5
votes
4answers
12k views

Why does = NULL not work in Oracle?

I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL. Why does = NULL not work here?
1
vote
2answers
7k views

Hierarchical query from two tables

Help me create a hierarchical query. I have two table: Table 1(dic_kgs) KGS_ID KGS_PARENT_ID KGS_CODE 1 A 2 1 A0 3 1 ...
4
votes
3answers
15k views

When creating views, why do users need direct object permissions if they already have the same permissions via a role?

I'm having a problem with permissions in Oracle 10g. I'm hoping someone can help me make sense of this. I have a schema with a table in it. I have granted select on that table to a role. grant ...
0
votes
2answers
3k views

Possible career paths for Oracle Certified Associate (OCA) [closed]

In order to be confident in databases as a entry level job candidate, I completed the OCA certification. Now I want to know what career paths follow this. Can one go into data-mining, BI, or R&...
2
votes
2answers
1k views

Piplelined Result Set over Database Link Alternatives

Since a pipelined result set cannot be returned over a database link (see MOS note 560743.1), what workarounds/alternatives are there. I can think of a few, but I'd rather not lead the question. I ...
2
votes
2answers
2k views

Error updating a master table of a materialized view

I've created several materialized views, and all but one are working correctly. The specific error I'm getting (via a Java job) is this: error loading from table 'STG_INTERACTION_RESOURCE_FACT' to ...
2
votes
2answers
2k views

Oracle Restore and recover Database on the same host?

I have DB1 database on Server1 . I have taken backup. After that I have created table TEST. Inserted some values in the TEST table. Truncated table. And want to return my TEST table to its previous ...
2
votes
1answer
2k views

How to find out CPU utilization per Oracle Database on Linux?

For capacity planning on existing RedHat servers hosting Oracle Databases, I was asked how to find out the CPU utilization per database. Thought it would be as simple as ps -eo pcpu,comm |grep -i ...
6
votes
1answer
2k views

What's the difference between a v$ view and its dba_ equivalent?

What's the general difference between a v$ view and its dba_ equivalent? Take, for example, v$tablespace and dba_tablespaces.