Oracle Database 11g Release 2. Please also tag oracle for search purposes.

learn more… | top users | synonyms

1
vote
1answer
1k views

Audit select queries against specified tables by specified users

I have a requirement to audit all SELECT queries (by access) against specified tables by specified users. As far as I can tell, this can't be done with normal auditing; it only supports auditing all ...
0
votes
0answers
36 views

Simultaneous table read in single query in Oracle

Is it possible to set Oracle to read tables simultaneously and don't wait until the first table scan is finished? Example: I'm joining a remote table to a local table. Oracle is reading the remote ...
0
votes
0answers
113 views

ORACLE_HOME is not available

I install Oracle 11GR2 on Cenots 5.9 . i edited bash_profile to correct values for ORACLE_HOME and ORACLE_BASE and etc . But when i want to run SqlPlus , " command not found " is shown . but when i ...
0
votes
1answer
803 views

Install Oracle 11gR2 driver for Node.js 0.10.20

I want to connect Oracle 11gR2 and Node.js 0.10.20. I use this package but I don't understand this part of installation process. Can you explain it to me? # Replace /opt/instantclient_11_2/ with ...
0
votes
3answers
1k views

Trigger needs dbms_alert declared

A trigger has given me the following: ORA-04098: trigger 'adminuser.DB_ERROR_CHANGE_TRIG' is invalid and failed re-validation and this is when working on altering schemas/triggers/etc from an ...
3
votes
1answer
692 views

Oracle Datapump: modify fully qualified objects when cloning a schema

I am confronted with a problem for which I have not found an elegant solution: I need to clone a schema within the same database, so I use datapump's remap_schema import parameter. The problem is ...
-3
votes
1answer
444 views

I am trying to import a file but it gives me exception : ORA-20001: Invalid Operation. while calling a stored procedure, how can i solve this [closed]

I am trying to import a file , but while calling the procedure i get an exception ORA-20001: Invalid Operation. Stored Procedure: begin -- Call the procedure imp_pkg.isinmstimp(cpath => :...
0
votes
1answer
58 views

Is DBCA in Oracle for a single database or the entire system?

I ask this because I was going to create a simple phone list database for learning. But when I used dbca, I was asked did I want to install the sample databases, HR, etc. It also had the default ...
0
votes
1answer
274 views

imp paused mid import

OK, I'm new to Oracle administration but I'm trying to import a previous dump (no pun intended) which was split into two files. The import is happening from an Oracle 9i to 11gR2 on Windows I used ...
2
votes
2answers
289 views

Does RMAN in Oracle only copy the archive logs? What happens to the active redo log in case of failure?

If RMAN copy copies the archived redo logs when they are full, what happens to the current redo log in the event of a failure (hardware failure, etc.)? Isn't there always an active redo log that is ...
3
votes
4answers
18k views

Environment Varible PATH exceeding recommended length solution?

I am attempting to install Oracle Database 11g on a Windows 7 64x laptop. I get all the way to step 5 of 8 and I get a fail on Environment variable path "exceeds recommended length"... I have ...
0
votes
1answer
459 views

Trigger and temp table or materialized view

I'm looking for the better/best solution to compare data in a table before update and after. This can be done with trigger and temp/global temp table or with read only m-view as far as I know. Would ...
1
vote
2answers
283 views

Audit table insert restriction

So I'm trying to create my own audit table for some user management analysis and stumbled across a little problem. I use this trigger CREATE OR REPLACE TRIGGER usr_t_audit_user_alnd AFTER LOGON ...
1
vote
1answer
82 views

recover oracle db from datafile

We have oracle db which unfortunately we did'n make backup and all control files and other files except all data-files are damaged. Is there any solution to recover db from all of these intact data-...
2
votes
1answer
283 views

Backing up Oracle home directory while instances are running

Can I take a backup of Oracle home directory (with cp -r) before upgrade while the instances using the home are running or is it required to shutdown instances first? Trace files/alert logs are in ...
1
vote
3answers
3k views

Oracle query is slower the second time

I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried ...
0
votes
1answer
3k views

Installing Oracle 11gR2 on Oracle Linux 6 - error possibly related to database (unique) name

Installing 11gR2 on Oracle Linux 6.4. System is a desktop used by one developer. It's on DHCP. Hostname is similar to ip-t3400-1547.mydomain.local Please note I have no issues installing this ...
1
vote
1answer
176 views

Enforce a constraint problem

This business model is for a case management database. This is closely modeled on the idea of a file folder representing the phase and a sequential checklist representing the stages. A case consists ...
0
votes
1answer
741 views

SQL output formatting

I want this: "ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID" "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" But I get ...
0
votes
1answer
77 views

Is it possible to dynamically retrieve the number of columns in a view in Oracle?

As the title says, is it possible to retrieve the number of columns in a view dynamically? I'm learning Oracle 11gR2 and I'm checking out the V$ views. One of the things I want to do is output them ...
1
vote
1answer
552 views

Migrating a tablespace to a different database on the same ASM

How might one go about copying or moving tablespaces between databases that uses the same ASM instances? Might it be possible to cut some corners in RMAN or have ASM remap files belonging to a ...
6
votes
1answer
10k views

Cannot change service name for Oracle

I'm trying to change the service name of an Oracle 11.2.0.3 installation on a Windows 2003 server. During installation the service name was defined with the default domain, but we'd like to get rid ...
1
vote
0answers
119 views

Oracle specifying join order hint

Oracle 2 node RAC 11G R2 I have a query (this is a simplified version, but still exhibits the same issue as the larger query) that looks like this: SELECT t1.trip_id, t1.origin, t2....
0
votes
2answers
3k views

Register XML schema using dbms_xmlschema in Oracle

The requirement is to register XML schema from XSD files in Oracle 11gR2. I am using the following code: DECLARE BEGIN DBMS_XMLSCHEMA.registerSchema( SCHEMAURL => 'http://[...]/spin-catalog-...
0
votes
1answer
597 views

Oracle 11gr2 instant client installation

I have an application that uses Oracle 11gr2. For the Oracle client installation, I first install InstantClient and then run the setup again, choose Custom, and choose "Oracle Database Utilities" ...
1
vote
1answer
4k views

ORA-21780: Maximum number of object durations exceeded

I am getting this error when records are streamed over to another database Oracle DB version:11.2.0.3.0 Transaction Flow: DML on Schema1@DB1 Streamed to Schema2@DB2 then trigger on this table will ...
1
vote
0answers
14k views

Failure Of Backup Due To Limit Exceeded For Recovery Files Since Cannot Reclaim Disk Space [RMAN-03009,ORA-19809,ORA-19804]

While I try to take incremental backup level 0 by using the command RMAN> backup incremental level 0 database plus archivelog; , encountered this error. RMAN-00571: ============================...
0
votes
2answers
488 views

CASE Statement - 1st case is being ignored

I have a table (usr_t_user_reg) in which I register all the users of the database. Now I want to create a view (usr_v_user_not_reg) which shows me all the users that I haven't registered yet or users ...
0
votes
2answers
6k views

Update year alone in date (Oracle 11g)

When I checked one of my client's database, I found some mistakes which will make problems. The problem is that the date is showing as 01-01-01 when I try to display it in the dd-MM-yyyy format the ...
1
vote
1answer
68 views

Redundant transpose. Case is not enough to solve my problem

I have db problem in transposing rows to columns. I am half way through the result but getting redundant data. My table: EMP_ID EMP_NAME SAL_PAID 01 ABC JAN 01 ABC FEB 01 ...
1
vote
1answer
3k views

startup mount issues with Oracle

I am trying to simply shutdown and restart an Oracle db instance on a Windows Server 2008 machine (Oracle 11.2.0). Logged in via sqlplus, I issue the command shutdown immediate;. I get the Database ...
6
votes
1answer
98 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
2answers
529 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
2k 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
4k 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 pre-...
1
vote
2answers
544 views

Table redirect / filter / trigger on select

Is there any way to redirect queries to different tables / views based on the package that's referencing the table? I.e. packages A and B both have "select grade from schema1.grd_tbl", but I want ...
0
votes
2answers
12k 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
2k views

How to Drop tables being a SYS user

I am not being able to drop my tables using : drop table <table_name>; command. It says "the table is referencing primary keys of some other tables as foreign keys." , and when I tried to ...
2
votes
1answer
8k 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
1
vote
0answers
137 views

Import from incremental backups to a new host in Oracle 11g

I am using Oracle 11g. I would like to know that whether it is possible to import from incremental level 0 & 1 backups to a new host using RMAN. If yes, how can I do that? For level 1 I am using ...
0
votes
2answers
616 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 ...
1
vote
2answers
906 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 ...
1
vote
1answer
35 views

most effective architecture to apply mulitple functions

I have a table that is the only source for a second table. Currently the second table is created using a CTAS (create table as select) statement that pulls in all the columns from the first table and ...
1
vote
2answers
2k 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 ...
8
votes
1answer
48k 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 ...
2
votes
1answer
2k 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 ...
3
votes
2answers
1k views

FileWatcher Troubleshooting Options

I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working. I am trying to figure out what I can check to troubleshoot why this isn't ...
4
votes
2answers
1k views

Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 roles....
0
votes
2answers
6k views

Problem compiling view when it is referencing a table in an other view: insufficient privileges

Oracle 11g R2 Logged on: SYS / AS SYSDBA When I try to compile or create a view that is referencing local schema tables. It works fine. Problem does occur when I try to compile the same view ...
1
vote
2answers
91 views

CONNECT BY using 3 tables(2 DIM and 1 FACT to get the Level and dependencies)

Below is my scenario and any help in this regard would be highly appreciated. I need to get the list of Managers and reporting persons to that manager with LEVEL at ID level. But the Fact table ...