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

4
votes
1answer
778 views

What is the difference between these two create table statements?

a) SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE); Table created. SQL> desc xml_tab1; Name Null? Type ----------------------------------------- -------- ...
4
votes
2answers
1k views

What's a free client for Oracle 8 on Linux / Ubuntu?

I need to connect to a legacy Oracle 8 database. Oracle SQL Developer would be my tool of choice, but the current version doesn't support Oracle 8 anymore. I tried Squirrel too, but the connection ...
3
votes
1answer
387 views

How to separate out individual sql statements from Oracle SQL script for execution from java code

I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code. I am reading this sql script from a .sql file and the script contains statements such as create table, ...
3
votes
2answers
7k views

Using Same CASE WHEN Conditions For Multiple Query Columns

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once? See the example below. SELECT CASE ...
3
votes
1answer
8k views

Is commit necessary after DML operation in Function/Procedure?

I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure? Example: create or replace function test_fun return number is begin delete from a; return ...
3
votes
4answers
5k views

Synchronizing data between MySQL and Oracle databases?

I want to sync data between Oracle & MySQL databases. What is the best possible way to achieve this? Are there any tools/software available? This is not a one-time but an ongoing operation. The ...
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 ...
3
votes
1answer
429 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 ...
3
votes
2answers
7k views

Oracle 10g dump file push into Oracle 11g database

Currently when executing the imp command with the following parameters: file=my.dmp log=my.log fromuser=myuser touser=myuser The following errors occur: IMP-00058: ORACLE error 12560 ...
3
votes
2answers
426 views

Resources for SQL Server developer migrating to Oracle

What resources can you recommend for a SQL Server developer wanting to learn Oracle basics? I am looking for a comprehensive whitepaper or blog post describing differences between these systems and ...
2
votes
2answers
5k views

How to set Password for sqlplus /as sysdba

I am new to Oracle. I would like to know how to set login password for sqlplus / as sysdba in oracle 11g. I want to set the password for my next login, i dont want to login as sqlplus / as sysdba ...
2
votes
1answer
1k views

How To dump data from Oracle to Vertica

I want to transfer data from Oracle to Vertica and I want to know what is best way to do it? I s there a tool for it? I tried to dump the Oracle data and load it to Vertica using 'Copy Direct' ...
2
votes
1answer
5k views

Reduce Size of SYSAUX

I am trying to reduce the size of our SYSAUX Table. Currently the largest four occupants are below. As you can see it is mostly the Server Manageability items. select occupant_desc, ...
2
votes
2answers
7k views

Improve BLOB Writing Performance in Oracle 11g

I have an application that writes a lot of rows into a table that contains a blob column. The average size of the blob is 3k bytes. Is there any buffer that i can resize to improve inserts into this ...
2
votes
1answer
199 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
2answers
2k views

How can I catch the output of DBMS_OUTPUT.PUT_LINE when executing a block using [Oracle.DataAccess.Client]?

In my oracle Database if have the following table: Create table Test_call_count ( count number(10) ); insert into Test_call_count values (0); Now I can run the following PowerShell script if ...
1
vote
0answers
27 views

Oracle: separate column histograms for partitions

I have a partitioned table like MY_TABLE(PART_KEY, DATE_KEY, ...) with a local partitioned index on (PART_KEY, DATE_KEY). When I collect stats on the table, I end up with a HEIGHT BALANCED histogram ...
1
vote
2answers
52 views

Trunc sytax problem

If any one could help me with the syntax issues which I am facing here. sqlshipped := ' and a.ORDS_COMPLETED_DATE between ' ||shippedfrom ||' and trunc(' ...
1
vote
1answer
186 views

Pl/SQL web application

I have a PL/SQL web application,got the entire DB dump from remote DB. Its very old application, running on oracle 9i. I basically want to get the application up and running on my laptop,but i am not ...
1
vote
1answer
53 views

Open a Oracle 7.3.x database in Oracle 11

Is there any way to open or convert an old Oracle 7.x database into Oracle 11g? Thanks in advance!
1
vote
1answer
129 views

Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome. In short we have left-join query like: select from a left join b on a.key1=b.key1 or a.key1=b.key2 This works VERY slowly, while ...
1
vote
1answer
228 views

Find who and when changed a specific value in the database

We recent found some record in a table of an oracle database has been updated to another value.and it has been cause very important mistake. we want to find out who and when has done this? could ...
1
vote
1answer
4k views

What is the best way to insert a XML input to an oracle table in stored procedure?

I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this: <rowset> <row> ...
1
vote
2answers
760 views

Factoring out subselect (with clause) ends in ORA-00904

I am trying fo factor a repetitive subquery with with but getting ORA-00904 over and over again. This is my base query which works quite well: select tka.id, tka.name, txt.text as langname, ...
1
vote
1answer
311 views

Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index

One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index. Its values are just a sequence ...
1
vote
1answer
1k views

How to open Oracle listener for three database instances?

I have 3 database instances: orcl JIP02T JIP04T When I start the database, does it start all 3 instances? When I start the Oracle listener, does it start a listener for all 3 instances? When I ...
1
vote
1answer
2k views

unknown SET option beginning “oracle_sid…”

SQL> set oracle_sid=sid SP2-0735: unknown SET option beginning "oracle_sid..." SQL> emctl start dbconsole; SP2-0734: unknown command beginning "emctl star..." - rest of line ignored. SQL> set ...
1
vote
1answer
671 views

Repartition Primary Key Index online in Oracle

I have an 24x7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention. We use sequences for the ...
1
vote
1answer
197 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 ...
1
vote
3answers
291 views

Compare content of databases in Oracle

we have a request to build an ASP.Net application that will be used as interface to launch commands on Oracle databases. We need to find a way to compare the content of 2 databases. We prefer not to ...
1
vote
2answers
3k 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 ...
1
vote
2answers
645 views

Can I implement a 'gapless' identity column in Oracle?

In SQL Server it is so easy to write: create table #tmp ( id integer identity(1,1) primary key, message varchar(256) ); When I try to migrate this to Oracle, I end up with: CREATE ...
0
votes
0answers
40 views

Why does second instance of database not open?

SQL> startup ORACLE instance started. Total System Global Area 3841982464 bytes Fixed Size 2025392 bytes Variable Size 788531280 bytes Database Buffers ...
0
votes
1answer
38 views

Migration of oracle database from rhel5 to rhel6

I currently have oracle 11G running on RHEL5 on one server. (This Server has reached END of Life and no hardware support for it) This server is connected with a Storage and ORACLE_HOME is on storage. ...
0
votes
1answer
94 views

Tablespaces in oracle

I understand that tablespace is a logical collection of tables and database objects. It could be made up of multiple data files (which is the physical collection of objects). Apart from this, what is ...
0
votes
2answers
179 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
0
votes
2answers
6k views

“ORA-01017: invalid username/password” : Why is that?

What is the difference between the two : SQLPLUS / AS SYSDBA and SQLPLUS Enter user-name: SYSDBA Enter password: If I use the first command I get connected and see the prompt SQL> but upon ...
0
votes
0answers
1k views

Achieve log transfer to standby (Data Guard)

I am not able to transfer my archive logs from primary to standby. I executed the following query: archive log list Database log mode Archive ...
0
votes
3answers
824 views

ORA-00905: missing keyword when trying to create a synonym

I have a problem when I try to create a synonym like this: CREATE SYNONYM B2H.BILLING_USER_ACTIVITY@PACOMNET FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM; Why does this happen and how can ...
0
votes
1answer
4k 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 ...
0
votes
2answers
3k views

Copy complete structure of a table in Oracle without using Backup

When you create do a CTAS (create table as select) of a table you only get the structure, but lose the index, PK, FK, etc. Example: create table t1 select * from table2; How can a copy of the ...
0
votes
1answer
400 views

Preloading a Java library in DB2, similar to ORACLE's loadjava

Edit to add details to clarify, based on Chris' comment: A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when ...
0
votes
1answer
2k views

Listener failed to start [closed]

I have installed Oracle 11g on my Window7 computer; whenever I try to create a connection in Oracle SQL developer I get this error TNS:listener does not currently know of SID given in connect ...
-1
votes
0answers
34 views

How to run oracle PL/SQL in C++?

I want to run SQL script from a C++ program. My code goes like this: int main() { //..... sql_stmt = "Insert into t1 values ('qwerty');\nInsert into t1 values ('dothar');"; sql_stmt = ...
-1
votes
1answer
110 views

the environment variables configuration for ORACLE EXPRESS 11g / windows7 32Bit

I can't connect in Oracle Express 11g (windows7 32-bit) and I think the problem comes from the environment variable ORACLE_SID & ORACLE_HOME , how can I get the database instance to do the right ...