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
341 views

Unable to connect oracle as sysdba tables have been dropped

I have a script which lists all tables belonging to the user and executes DROP for all of them. By mistake, I logged in oracle with 'sys as sysdba' and ran above script. Due to which all sysdba ...
4
votes
1answer
4k views

How can I view what NLS settings are active for other session?

I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2. What can I query to show the NLS settings for a different active session - all of the ...
4
votes
2answers
9k 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 ...
4
votes
3answers
3k views

Is (and if how) it possible in Oracle to “insert into <table> values <rowtype-variable>” if <table> has virtual columns

Here's a table create table tq84_virtual_test_without ( col_1 number, col_2 number, col_3 number, col_4 number, col_5 number ); with the rule that col_5's value is the sum of the other ...
4
votes
1answer
884 views

What is Sql Server Clustered Index in Oracle terminology

A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on. If you look up with a name Ozgur you start O then z etc. I would like a simple explaination for this ...
4
votes
1answer
5k 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
1answer
1k views

How do I horizontally partition an oracle database table, and should I?

We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment. Create ...
4
votes
1answer
811 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
462 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
475 views

DB2 (LUW) equivalent for Oracle “drop user cascade”

This question is related to creating and initialising test and development databases for an application that has the database running on LUW (linux-unix-windows) On oracle, we have an os user (on ...
3
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' ...
3
votes
2answers
9k 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
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
6k 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
450 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
457 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
211 views

Design of an application log database

We are generating logs like following example(this is a table no actual pipes): 2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms 2014-06-10 09:00:08.245 | Channel2 | Operation5 | ...
2
votes
1answer
15k views

“ORA-03113: end-of-file on communication channel” on startup

I have been reading posts here, on Oracle support, and anywhere else I can find for the last three days and I've given up on this problem... An Oracle database hung. Shutdown of the database sat for ...
2
votes
2answers
6k 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
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
1answer
708 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 ...
2
votes
2answers
8k 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
209 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
2answers
99 views

sqlplus: ORA-01017 logon denied from one host but OK from another

I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain: 1) I have an Oracle 11.2 server running on a virtual host, say host1 2) I set up another VM, say host2 (CentOS ...
1
vote
0answers
42 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
53 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
200 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
0answers
183 views

Change Oracle banner without restarting database

I am removing the banner for Oracle by commenting out this line in SQLNET.ORA: SEC_USER_AUDIT_ACTION_BANNER=C:\banner.txt Is there any way to make the change take affect without restarting the ...
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
146 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
245 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
5k 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
842 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
335 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
198 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
294 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
662 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
2answers
339 views

What is the difference between Schema and Database in Oracle?

In oracle, Datafile are system files where actual data is stored.The collection to datafile make Tablespace and atlast database is collection of tablespaces Correct me if I am wrong on the concepts ...
0
votes
1answer
50 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
104 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
199 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
7k 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
918 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 ...