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

1
vote
1answer
1k views

Why does Oracle SQL*Plus consider two queries executed together on the command line as a syntax error?

Consider: SQL> select sysdate from dual; SYSDATE ----------- 22-Aug-13 0 SQL> select sysdate from dual; select sysdate from dual; select sysdate from dual; select sysdate from dual ...
2
votes
1answer
267 views

Recover encrypted Oracle database

A client fired their DBA. He used OWM to add a wallet to their Oracle database and secure a few necessary columns of tables that they need. I can see the walled using: orapki wallet display -wallet ...
0
votes
2answers
603 views

Oracle intermittent performance problem

I have some complex queries that are usually fast (< 1 sec), but occasionally slow (> 10 sec, even minutes). How do I track this down or identify what condition is causing it? One theory was ...
1
vote
1answer
267 views

Help with Oracle Pipelined function

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works): SELECT oks_c.contract_number, ...
0
votes
1answer
56 views

Connecting to Oracle with encrypted pwd

I am reading my userid , pwd from a file with 400 permissions to connect to the external oracle application. Now my requirement is that i should not use the plain text pwd present in the file . I ...
0
votes
1answer
385 views

Oracle 11G Startup failed after altering the number of system processes

When I alter the number of system processes to 100000 and restart the database, I get the error below: ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semids_per_proc ...
1
vote
1answer
1k views

Creating a USER using the imp command [duplicate]

I was told I can use exp to export a certain USER, then DROP the user and with the imp command I can recreate that USER with all of its tables. Is it true? How do I do it? I already have an ADMIN ...
6
votes
1answer
89 views

Why are some aggregates treated differently in the RHS of a rule in the model clause?

With my limited understanding of the model clause, I'd expected the following three queries to return the same results because there are no nulls in the data, and the functions are equivalent: select ...
0
votes
0answers
249 views

Oracle prevent CPU usage per process/session

I have an Oracle instance to which several applications from different machines connect (http servers). Normally Oracle CPU usage is ~5-10% even on high server loads. But from time to time, one of ...
1
vote
1answer
315 views

Any advantage of creating table in temporary tablespace - Oracle

My on-site DBA told me to create the particular table on temporary tablespace.Is there any advantage of such action?
0
votes
2answers
258 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
2
votes
1answer
716 views

Is there a way to explicitely have a not-null column in a view

If I create a view in Oracle, the column definition's not null characteristic is inherited from the base table: create table tq84_t ( col_1 number, col_2 varchar2(15) not null ); create view ...
0
votes
1answer
1k views

Oracle: list user-created tables in the SYS schema

I need to delete all tables (hundreds) in the SYS schema that someone accidentally created with sqlplus. Looking in dba_tables and dba_objects, I don't see a way to distinguish these tables from ...
2
votes
1answer
85 views

Is SQLT safe to run in production? [closed]

I am debugging poor query performance for my application and recently came across the SQLT tool. The XTRXEC method combines XTRACT and XECUTE and it seems like this provides more info then XTRACT ...
1
vote
1answer
2k views

Why isn't oracle using an index for distinct query ?

This is the scenario SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true) PL/SQL procedure successfully completed. SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / ...
1
vote
2answers
3k views

Oracle impdp - Importing file from remote server

Am I missing something or is it not possible to import a dump (.dmp) file using impdp to a database on another server other than where it was created? All of my investigations lead me to believe that ...
0
votes
2answers
6k views

ORA-01031 while creating a view as DBA

I'm trying to create a view under my DBA schema on Oracle XE. I already did that on the productive database and it worked with a user with a lot lesser privileges than the DBA role. But now I always ...
0
votes
1answer
174 views

Oracle Patch Update

We have an Oracle RAC production environment with primary and secondary DB. Our DBA has asked to update oracle version from 11.2.0.1.0(64 bit) to 11.2.0.3(64 bit) with patch 6880880,10404530,16803769 ...
1
vote
1answer
571 views

How to partition an Oracle table with 65 million rows?

The table that needs partitioning contains a list of Components and their Values. So one table is tblComponents. Another is tblFields. Then this table, tblValues, contains the values of the ...
0
votes
0answers
22 views

isql*plus crashes in 9i when multiple user execute query simultaneously in oracle 9i [duplicate]

In my workplace Multiple users are using isql*plus(oracle 9i) to access single database through server url .But after some time isql*plus crashes and its services needs to started. Its causes a lot ...
1
vote
1answer
82 views

DB Certificates [duplicate]

I hope this is the right place to as this question. I just earned a Bachelor's degree in Management Information Systems and I'm interested in becoming a Database Admin. However, I'm a little confused ...
0
votes
3answers
428 views

Oracle won't start

While it was working fine, I had to stop the server once. When trying to start Oracle using a script we have, I got the following error: SQL> Connected to an idle instance. SQL> ORA-01081: ...
0
votes
1answer
316 views

Creating a job scheduled based on a specific timezone

I have an Oracle database hosted in Europe. I need to create a scheduled job in the database to send email. I need to send email daily at 17:30:00 in India standard time (IST). I know how to send ...
1
vote
1answer
140 views

How do I get value from parent row

I have two tables. One contains a hierarchical structure of topics and another table that contains optional configuration of that topic, that controls it's visibility in several contexts (one column ...
2
votes
1answer
3k views

SP2-0750: You may need to set ORACLE_HOME

I am getting following error message when i write sqlplus on command prompt SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory Here are the path details
0
votes
2answers
359 views

Fatal NI connect errors

I'm wondering if there are any auditing processes I can enable to see what is causing these errors, like sql statements being run, or any other metadata that I could use to track down the source of ...
2
votes
0answers
180 views

Oracle Text - Changing SYNC to MANUAL not working always

Oracle version is 11g. When the Oracle Text index is created, SYNC option is given as "ON COMMIT". Before inserting large number of records, SYNC is changed to "Manual" for performance reason. Below ...
1
vote
2answers
204 views

fighting filter (nested loops) execution plan from Oracle

My query is: SELECT category, count(*) FROM large_data_table t WHERE (t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 1)) OR (t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 2)) GROUP BY GROUPING ...
0
votes
1answer
2k views

Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server

i am having a bit of a linked server problem . I created a sql server linked server and it's been working fine for last 2 years and suddenly not working . SQL Server Microsoft SQL Server 2005 - ...
1
vote
1answer
2k views

Removing foreign key when it is not named

I've created a table named 'student' as : create table student( stud_id number primary key, stud_name varchar2(30) not null, branch_id varchar2(4) references branch(branch_id), course_id ...
0
votes
5answers
8k views

Message file not found when starting SQLPlus

I've installed Oracle XE 11.2 on Fedora 18 but have trouble starting SQLPlus. It quits with the following error: Error 6 initializing SQL*Plus SP2-0667: Message file sp1<lang>.msb not found ...
0
votes
3answers
221 views

Oracle DB Recovery

Before I start, I just want to mention that I am not a DBA, I am a programmer/developer who also happens to look after the database. So if I say something wrong, my apologies in advance. We recently ...
0
votes
1answer
108 views

Trigger in trigger or trigger before table creation

I need to create trigger inside trigger as follows: SQL> CREATE OR REPLACE TRIGGER table_account_modify AFTER CREATE ON SCHEMA 2 DECLARE 3 NAZWA_TABELI varchar(250); 4 sq0 VARCHAR2 ...
0
votes
0answers
75 views

How oracle syncs data across nodes in a cluster

If I have a oracle db server clustered across 2 nodes, it means both nodes have a copy of the whole data, the data is not lying half and half on each node. Now when I make an update using my ...
1
vote
2answers
6k views

Index for date column for range query in Oracle?

We have a lot: select count(*) from TBL where XDATE between ? and ? and FLD = ?; We expect to boost performance by tuning index on XDATE column (which have DATE type). What type of indexes more ...
0
votes
3answers
6k views

How to solve ORA-011033: ORACLE initialization or shutdown in progress

I have followed link to solve the problem. When it is giving ORA-00600: internal error code, arguments: [kcratr1_lostwrt] then I am using: SQL> startup mount the above line should return ...
1
vote
2answers
698 views

oracle tablespace extending

While under massive write (insert and create table as) load, when Oracle RAC needs to extend tablespace, all write activity gets blocked for however long it takes it to finish extending (tens of ...
0
votes
1answer
372 views

Oracle Exchange partition from non partitioned table

We have table name Summary with 86 GB data. This table do not have partition/sub-partition .We want to move the data from the summary table to a composite partitioned table (range partition on ...
2
votes
3answers
511 views

best practice either create multiple databases or multiple users

I have several application which currently running on my local. I have installed oracle 11g and create a global database name as oracle11. Now should i create again different database for each ...
0
votes
0answers
488 views

ORA-03113: end-of-file on communication channel Can not connect [closed]

This is the log file: Errors in file E:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_arc0_5024.trc: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: ...
2
votes
1answer
2k views

Migrate / Copy Huge data from Oracle to SQL Server

I have huge tables in oracle database, appx 1 crore+ (10 million+) rows and want to migrate / copy those table and data in sql server. Currently I am using Import functionality of SQL Server for this ...
0
votes
1answer
364 views

USER_TAB_PARTITIONS Query Runs Incredibly Slow

I seem to be seeing incredibly slow (5-10 seconds) to run a query against USER_TAB_PARTITIONS (Oracle) intermittently. When I do an explain plan on : SELECT * FROM USER_TAB_PARTITIONS WHERE ...
5
votes
1answer
13k views

Getting “ORA-00942: table or view does not exist” while table does exist

I'm fairly new to Oracle database. I have installed Oracle Database 11g R2 on Oracle Linux 6. I have successfully created a new database with dbca and connected to the database using: $ sqlplus "/ as ...
1
vote
1answer
585 views

Two Intel E5-2643 (4 cores each) or single E5-2690 (8 cores) for Oracle 11.2.0.3 on Oracle Linux

I have quite specific question about Oracle database (11.2.0.3) and Intel processors. We just upgraded our main database server to big Linux box with 192GB of RAM (150 GB dedicated for Oracle). After ...
0
votes
2answers
52 views

Workaround for using functions in restricted system

I have access to an environment where I can run SELECT statements but cannot call functions. I know what all the code is inside the functions. The functions are merely lookups (SELECTs and not ...
2
votes
2answers
657 views

How to disable flashback query logging for a specific table (Oracle)?

We have a specific table that has a lot of activity and it creates a lot of change records. The consequence is that the flashback data only goes back a couple of days. That is OK for many cases but it ...
0
votes
1answer
95 views

Different time in different sessions

I am connecting to same db from 2 different machines using SQL Developer and querying a table which stores timestamp. But to my surpise the time value returned by query has a gap of one hour on ...
2
votes
1answer
1k views

oracle blocking sessions

I am trying to investigate some concurrency issues on Oracle 11 RAC. So far the most helpful tool I have is "blocking sessions" view of the Enterprise Manager, which tells me which query is blocking ...
0
votes
2answers
126 views

Recommendation for RDBMS for large table [closed]

I have been asked for recommendation on the optimal RDBMS for a large amount of data. The largest table would contain 2TB of data. My own largest table is only 400GB (mysql, performs very well). The ...
2
votes
1answer
826 views

Example of loading into RDS from S3 using SQL*Loader

Does anyone have a working sample of loading data into an Amazon AWS RDS Oracle database using SQL*Loader where the source file is on S3? In principle it's clearly possible. The Amazon RDS Data ...