Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...

learn more… | top users | synonyms

3
votes
5answers
5k views

Copying Oracle 11g express Database from one server to another

Basically, I am a MS-SQL developer,and relatively new to Oracle. Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another. In ...
4
votes
2answers
439 views

Tracking SQL queries and rows operated on for documentation/audit

Is there a way to record the SQL queries (and rows that were acted upon) in a session for documentation/audit purposes? For example, in deleting duplicate rows from a table I use the following SQL ...
4
votes
1answer
1k views

How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats

Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings: FOR COLUMNS SIZE 5 some_column and FOR COLUMNS some_column SIZE 5 both will create a ...
1
vote
2answers
762 views

Oracle 11. Updating BLOB field. Db file sequential read inappropriately slow?

I've and Oracle (11 Enterprise) schema with a table CREATE TABLE USER.WSP_BUNDLE ( NODE_ID RAW(16) NOT NULL, BUNDLE_DATA BLOB NOT NULL ); CREATE UNIQUE INDEX USER.WSP_BUNDLE_IDX ...
2
votes
2answers
2k views

Oracle Query by Quarter

I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should: select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ... from ... where ...
1
vote
1answer
188 views

Is there an Oracle rdbms monitoring API?

We have about a thousand customers with our application installed out of which 500 have an oracle database. Monitoring the database is only one of the requirements. Installing Oracle Enterprise ...
2
votes
3answers
6k views

ORA-09925: Unable to create audit trail file

I'm using Oracle 11.1.0.6.0 db, on Linux 64 bit server on Amazon cloud. I'm getting following error when I run the connect command SQL> connect / as sysdba ERROR: ORA-09925: Unable to create ...
2
votes
2answers
738 views

Question on Oracle rowid column in every table

I want to know how to use the object number that extract from rowid column? It's seems that this oracle number is not the same as object_id in dba_objects view. Anyone can help me with it please? ...
4
votes
2answers
1k views

Where can I find autoextend events?

In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?
2
votes
2answers
654 views

Logging/profiling an unpredictably slow stored procedure

I have a stored procedure that sometimes runs significantly slower than others. I have two different timers measuring the execution time at different scopes. One is in the client code, measuring only ...
18
votes
2answers
2k views

How to make a transition from SQL Server DBA to Oracle?

As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do? I would assume the concepts are the same and the ...
1
vote
2answers
808 views

Which oracle should I download for windows 7 x64?

My os is: windows 7 x64 I tried to download oracle and install it in my computer, but I have downloaded 3 different oracle releases(including oracle 11G), they can't be installed. They reported ...
1
vote
1answer
380 views

Oracle change initialization parameter with “deferred” keyword

The description of deferred and scope keyword is listed below (extracted from Oracle documentation). The meaning of them gives me an indication that deferred can just use the memory scope. Is my ...
5
votes
1answer
441 views

If an 'after' DDL trigger causes an error, is the DDL rolled back?

It has been suggested that DDL is logically performed something like this: begin COMMIT; perform any appropriate pre-DDL trigger code; do the ddl; perform any appropriate post-DDL ...
3
votes
1answer
2k views

Installing 10g client next to 11g database on same computer

I have Oracle 11.2.0.1.0 installed on Windows 7 64 bit I want to import data from warehouse which has Oracle 10.2.0.4.0 installed. When I try to import impdp usr/pass@WAREHOUSE TABLES=TABLE ...
1
vote
2answers
71 views

How can I return the number of values in each row which match a criterion?

I have a table "trial_volts" like this in an oracle database (I think it's 10g): trial_index NUMBER voltage_1 NUMBER voltage_2 NUMBER voltage_3 NUMBER voltage_4 NUMBER voltage_5 NUMBER And I want to ...
4
votes
1answer
8k views

Create dump file using Oracle SQL Developer

I think I'm missing a simple point, but I couldn't find a way to use Oracle export (exp/expdp) and Oracle import (ipm/impdp) with Oracle SQL Developer (version 1.5.5)? Is there a way for it? If ...
1
vote
2answers
96 views

How does an alter work?

I have a table with a lot of records (more than 15M) and i am executing an alter query to add 2 columns to the table. the query is taking around 1hr to finish executing. So my question is: How does an ...
2
votes
1answer
4k views

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

I have a problem with a SPFILE as shown below.How can I get rid of that problem?Any suggestions? SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE ...
2
votes
2answers
938 views

Compare two oracle .dmp files

Ok calling an Oracle expert, DBA or general good guy The problem is simple but trawling Mr Google has not come up with an easy to use solution. I am testing data input. I want to compare master.dmp ...
4
votes
2answers
5k views

How to catch and handle only specific Oracle exceptions?

From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955. How can I rewrite the following to catch only the error ORA-00955? begin EXECUTE IMMEDIATE ...
3
votes
1answer
142 views

Grant to one; then revoke from all: does it work in Oracle SQL?

Consider the following: grant select on books to frank. revoke select on books from public. At the end of executing the above SQL script, will frank have 'select' access to the 'books' table?
2
votes
2answers
583 views

Is there any prevalent logging packages in PL/SQL similar to java and .Net counterparts

Normally in java or C# , I would use a following construct when try and catch errors. Bad Exception Handling in C# and Java try { // do something } catch(Exception ex) { } Good Exception ...
1
vote
2answers
640 views

Is Using Execute Immediate Bad Practice in Oracle?

So, I've been writing a script which creates a sequence (if it doesn't exist) then copies some data including a value from that sequence before deleting the original source data. As the sequence ...
8
votes
5answers
2k views

Oracle sort varchar2 column with special characters last

How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
3
votes
3answers
526 views

What does “thread” mean in “thread checkpoints” context?

The following excerpt is from Oracle concepts. Maybe this is a stupid question, but I really need your help to understand it. My question is: What's does the thread mean in the context thread ...
2
votes
1answer
102 views

Do IOTs generate significantly more undo per block for deletes?

IOTs are an index-like structure. When deleting large contiguous chunks of data, how much undo is generated as compared to a similar contiguous delete from a heap?
1
vote
1answer
106 views

Oracle SecureFile Compression

Where can I read database compression properties after the creation of an oracle database / tables? Is there a special view where the info is stored and can be retieved?
1
vote
1answer
345 views

Using a sequence created in the same script in Oracle

SQL Server developer working on Oracle so apologies in advance if I'm fundamentally coming at this incorrectly from an Oracle perspective. I've got a script I've written which: Checks ALL_SEQUENCES ...
2
votes
2answers
2k views

Does Oracle cache queries when using a database link

Some vendor told us that we should not use database link between our database and theirs as the queries executed over a database link would not be cached, nor optimized. I can't find any information ...
4
votes
3answers
4k views

PostgreSQL vs. Oracle High Availability solutions?

PostgreSQL has a matrix of different high availability options which represent many different ways of building replication into an RDBMS. Here is the PostgreSQL High Availability, Load Balancing, ...
3
votes
1answer
199 views

Accessing ORA_ROWSCN in the triggers body

Is it possible to access ORA_ROWSCN pseudo-column in the body of BEFORE triggers? I'm thinking about storing this value in the table itself to emulate SQLServer rowversion behaviour.
2
votes
2answers
208 views

Execution plan doesn't use clustered index!

I'm using "SQL*Plus: Release 10.2.0.1.0 - Production" and have a table foo with a field fooID. I have created a clustered B+tree index on the field foodID of my table foo. And I do the following: ...
0
votes
2answers
949 views

how to verify that next sequence value can be used as a new primary key

I have many tables with a trigger attached like this BEFORE INSERT ON BRULES ...snip... if inserting and :new.ID is NULL then SELECT BRULES_SEQ.nextval into :new.ID FROM DUAL; end if; Due to ...
9
votes
3answers
766 views

How does Oracle know who I am?

Slightly unimportant but it's piqued my curiousity. I've just logged into an Oracle 10g database for the first time using the Oracle SQL Developer Tools. I used a generic user login which isn't my ...
5
votes
2answers
173 views

How the indexes are managed?

I am trying to understand how the indexes are managed for below common data types: 1- Numeric (Integer, Decimals) 2- String (Varchar, Char) 3- DateTime I have few questions: 1- How the indexes are ...
1
vote
0answers
87 views

Question on Oracle database table compress option?

How come a compress enable table (name as ABC) when created (Oracle 11g R1), but after merging with a partitioned table's partition with data which is not compressed, then the table (ABC) auto ...
2
votes
1answer
157 views

May I Convert Non RAC db to an RAC DB and add on a Node?

Currently my work env as 1 node none RAC database 11gR2, we are getting another machine (Linux), so I need to add on this node to our currently env and becoming a RAC db. What is the best way/route to ...
16
votes
2answers
2k views

Difference between GROUP BY B,A and GROUP BY COALESCE(B,A)

I have been getting myself very confused. Could somebody kindly explain under what circumstances I would want to use a GROUP BY COALESCE? My guess is that I would use it if I wanted to conditionally ...
11
votes
1answer
230 views

Term for a one-row table?

Is there a specific term to describe a relation with a cardinality of one? I'm thinking particularly of RDBMS "special" one-row tables, for example, DUAL (Oracle), SYSIBM.SYSDUMMY1 (DB2, Derby) and ...
1
vote
0answers
746 views

Fatal NI connect error 12170 [closed]

Getting TNS timeout error when trying to run a jdbc application against an oracle 11.2.0.1.0 database. Driver used : ojdbc6. JDK : 1.6_06 I've tried increasing the connect_timeout sqlnet parameter, ...
6
votes
1answer
1k views

MySQL or Oracle Associate Certification?

I have 15+ years in software development and 7+ in web development, and I want to get a certification in another area, like DBA. I have 7+ years of experience like MySQL developer. Which is better for ...
1
vote
2answers
163 views

How many DBMS's can be installed on a single server?

The software in our organization uses various databases, many on different database platforms. Can we put all the various database platforms on a single machine? What are the limitations to this? ...
1
vote
2answers
1k views

Materialized View - Oracle

I have materialized view for which i want to alter its refresh time: REFRESH FORCE START WITH SYSDATE NEXT SYSDATE +1 DISABLE QUERY REWRITE I want to know few things in this. What does NEXT ...
2
votes
1answer
1k views

dbvisualizer: number of rows limited by maxrows

I'm running a simple query on an Oracle database table like select * from tableA and DBVisualizer cuts the query short at 1000 rows. How do I remove this limit? Message is "number of rows limited ...
4
votes
1answer
141 views

Is Oracle Configuration Manager HIPAA compliant?

Does Oracle's Configuration Manager which according to Oracle provides a "40% faster issue resolution", violate HIPAA or require specific configuration in order to comply? This question concerns ...
2
votes
1answer
802 views

Use sqlldr control file to automatically create several tables in Oracle

I have a client who has a requirement that I load data from csv files into an Oracle database. Part of the requirement is that I use sqlldr and provide a control file to facilitate loading this data. ...
1
vote
2answers
2k views

Can anyone explain to me the concept “incarnation” in Oracle database in an easy-to-understand way?

The Oracle documentation describe incarnation as: incarnation A separate version of a database. The incarnation of the database changes when you open it with the RESETLOGS option, but you ...
2
votes
1answer
538 views

Oracle Active Session Pool Questions

what is oracle active session pool? is it activated by default? what is the default maximum active session that there is per user?
1
vote
1answer
209 views

What information from v$ views should be included when asking questions about Oracle Problems?

I was asking more information about Operating System. Then I wondered if an generic sql can give this information? Following things come to my mind. SELECT banner from v$version; SELECT LOG_MODE ...