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

learn more… | top users | synonyms

1
vote
0answers
31 views

Diagnosing Oracle query problems by a non-DBA [closed]

I have a query (insert) run on Oracle 11gR2 (inside a stored procedure). It goes something like this: execute immediate 'truncate table global_temp_table'; insert into global_temp_table select /*+ ...
0
votes
2answers
87 views

Can Oracle databases start automatically when Windows machine is booted/rebooted?

In one of my client machines every time Windows starts we have to start the Oracle database manually. Is there a solution/ command to start the database automatically when the machine is started/...
1
vote
1answer
434 views

How to create DBA user from oracle APEX 11gR2 XE

Please anyone tell me how to create DBA user from ORACLE Application express(http://127.0.0.1:8080/apex/apex_admin)? I installed Oracle 11gR2 XE on windows 8.1 PC and I am able to login to Oracle ...
0
votes
0answers
56 views

Oracle - compressed subpartitions in uncompressed table

I am working with Oracle 11.2. I have table which on subpartition level has in DBA_Tab_subPartitions column COMPRESSION set to ENABLED for all subpartitions. But on partition level in ...
3
votes
0answers
367 views

Fast restore of dropped partitions

I want to test the restore of erroneously dropped partitions of a subpartitioned table. This restore would consist of three steps: restore the database (or at least the necessary tablespaces) to the ...
0
votes
0answers
65 views

How to concatenate using cast(collect()) in Oracle DB?

My current query: select distinct mb.BP_TYPE,mb.ABS,mb.BLD_SEQ_CODE, mb.BPCODE||'~'||(select LISTAGG(mbp.PROCEDURE,' ') within group(order by mbp.LINE,mbp.SLN1,mbp.SLN2) from MIS_BS_BB_PROC mbp ...
3
votes
3answers
4k views

Permissions needed to call DBMS_DATAPUMP from within package

I am attempting to write a procedure (within a package) that calls DBMS_DATAPUMP to copy an existing schema into a different schema. The code works when executed from an anonymous block, but from a ...
4
votes
2answers
8k 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 NLS_*...
1
vote
1answer
81 views

sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows

While i am trying to connect with with database by the command sqlplus /nolog than the error message is coming "sp2-0667 message file sp2 lang .msb not found and sp2-0750 error is coming on windows" ...
0
votes
1answer
47 views

Oracle connection through visual studio

I installed oracle client 11.2.0 version in my machine and also installed Oracle data access providers for visual studio.I also installed Oracle Data Access 11.2.0.3.20. I am trying to connect to the ...
2
votes
1answer
16k views

Connecting to SQL Server using Oracle SQL Developer

I am using oracle sql developer to manage my oracle database. I recently added third party database jdbc drivers to be able to connect to my new MySQL and SQL Server installations. I need help with ...
1
vote
0answers
81 views

Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS

I have the following scenario: Oracle 11.2.0.3 A 3rd party application, that requires cursor_sharing = TRUE A table (T2310) with a indexed field (C1000000161) that has no duplicated values Result ...
1
vote
3answers
86 views

What procedure can be used to change system statistics in a production environment

System statistics aren't required but highly recommended by Oracle. From my own experience, it produced a huge improvement on the total user IO waits of our production environment when first ...
0
votes
1answer
68 views

How do I iterate over 1000 records in a particular order

I am writing a procedure to migrate two tables into different tables. This procedure will have two cursors. One to fetch the parent records and then second cursor to fetch the details for each parent ...
0
votes
0answers
89 views

Possible cause of ORA-00600 on dbms_xplan.display?

I'm trying to see the execution plan information for queries. I can see some statistics after I set autotrace on. Also, I can check from plan_table. However, most tutorials or blogposts suggest ...
0
votes
0answers
59 views

Does RECO always resolve in-doubt transactions ? (Oracle)

I am working on an application which uses a Oracle 11gR2 database in order to fetch and store data. The application is written in C/C++ and some Pro*C, which takes care of everything related to the ...
0
votes
2answers
1k views

In Oracle, how to get a list with the names of schemas that a user has read permission?

In Oracle, i need list which schemas a particular database user (or the current database user) has read permission. Based on this information, also need to get all the tables and views are in each ...
4
votes
1answer
609 views

Isssue with Database Links converting OEM Job to DBMS_Scheduler Job

We had an OEM job that would run a PL/SQL block using a particular database users credentials. The credentials are the same for two different databases, so there was no problem for the block to use a ...
3
votes
2answers
672 views

Database not able to startup showing control file is inconsistent with another

Our windows server 2008 R2 is shutdown due to power failure. When I am trying to startup Oracle 11g database its shows an error. SQL> startup ORA-01081: cannot start already-running ORACLE - shut ...
0
votes
1answer
168 views

Top Activity in OEM reports that dbms_scheduler jobs run as the job_creator user?

The top activity graph in Oracle Enterprise Manager was reporting that certain activity was being performed by certain users at odd hours of the night. Upon researching, this activity was because the ...
1
vote
1answer
358 views

My queries are slow under load

I am performing a load test for my application. All my SQL queries which are fired from my application, individually look better and perform fast. The same SQL are becoming bottleneck when they are ...
0
votes
0answers
40 views

One Listener for two Oracle Versions

I've got an dev machine with Oracle 10 and 11 running (on separate users) So I want to use only one Listener (the new from 11). I deleted the Listener of Oracle10 and created one on Oracle11. After ...
0
votes
0answers
40 views

Oracle Enterprise Manager 11g - restricting tablespace access

This may be a complete newbie question but I need help nonetheless. I have an administrator assigned to a default tablespace with his quota set to zero on all other tablespaces yet find that when ...
0
votes
1answer
221 views

Migrate/Export DB from Oracle 11g Enterprise to Oracle 11g Express

So I'm a DBA newbie and I've got an Oracle Enterprise server running in production with the following information under All Reports -> Data Dictionary Reports -> Version Banner, under "...
3
votes
1answer
62 views

Running / Accumulative Sum with Reset - Oracle

I am trying to accumulate a sum, but the sum accumulated cannot be positive. So far i got the following: Select t.caccserno,t.Reportingmonthyear,t.Amount,t.cycleage from temp_UnpaidInterest t ...
3
votes
3answers
5k views

How can I select rows from a hierarchical query with the lowest level?

I have a hierarchical query in Oracle 11gR2 that returns something like this: Parent (Level 1) Child (Level 2) Grandchild (Level 3) Child (Level 2) Grandchild (Level 3) Grandchild (Level 3) ...
30
votes
13answers
82k views

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
0
votes
1answer
237 views

Problem using sample schemas by Oracle in Oracle APEX

I am new to Oracle and Oracle APEX. I have been practicing to make Oracle APEX database application using "hr" schema of the database. Before doing so, I unlocked the schema, along with schemas "scott"...
1
vote
2answers
106 views

Testing on Logical Standby Database

Primary DB: 11.2.0.3 Enterprise Standby DB: 12.1.0.2 Enterprise I've found several articles on making changes on a Physical Standby DB, but I've been having difficulty locating how to enable user ...
0
votes
1answer
44 views

Is it possible to generate workload for hammerora (hammerdb for oracle) from AWR tables?

I'm trying to generate workload from our production databases but rather than running a trace file in production, I would prefer using the AWR tables since we have a license for it. Is it possible? ...
0
votes
1answer
17k views

How to fix “TNS:listener does not currently know of SID given in connect descriptor” with DBVisualizer on Linux?

I am trying to connect with Oracle database from a server having Oracle client installed (RHEL Linux Environment). I installed DB Visualizer on client machine. But getting listner error on hitting ...
3
votes
1answer
999 views

Where can I find a syntax reference for Oracle SQL Developer's “Generate DB Doc” function?

Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, ...
0
votes
1answer
163 views

Oracle migration unable to create indexes

I want to move the contents of a database instance to another using the expdp and impdp utilities. The source instance is version 12.1.0.1.0 and the destination is 11.2.0.1.0 so I modified the expdp ...
0
votes
1answer
94 views

ORA-12154 when PLSQL Developer 11 is trying to connect to database

Hi i have troubles when i'm trying to connect my PLSQL Developer to Oracle 11g R2 Express Edition. I created a new folder in c:\Oraclex86, in that folder I installed the oracle 11g R2 Client for x86 ...
0
votes
2answers
617 views

Oracle database patch update doubts

Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1) says the following 4 patches are recommended to be applied to 11.2.0.4 database. Note:21744343.8 Combo of 11.2.0.4.5 OJVM PSU and 11.2....
1
vote
2answers
62 views

Fetching recent/historical queries run on a database from an application

I'm getting some database errors from an application related to unique constraints however none of the records have any duplicate values so I want to check what query was run that produced the error. ...
1
vote
1answer
29 views

Find out where length semantics gets changed

I've code to create tables that doesn't specify length semantics: CREATE TABLE CHAR_TEST ( CHAR_TEST_ID NUMBER(*,0) NOT NULL, FOO VARCHAR2(6) NOT NULL, BAR VARCHAR2(6) NOT NULL, ...
2
votes
1answer
266 views

Unable to turn off automatic memory management (AMM)

i am running oracle 11.2.0.4 SE RAC on Red Hat linux 6, the database is using ASM for datafile management i have been experimenting with turning on AMM and set the following parameters alter system ...
0
votes
1answer
603 views

Updating BLOB Columns

I have table blobtable which has a BLOB column blobcol. A user updated the table using a command similar to this: UPDATE BLOBTABLE SET BLOBCOL=BFILENAME('directory/subdirectory', 'filename.txt') ...
0
votes
0answers
45 views

Login as SYSTEM in web console

I've installed Oracle XE 11g on Windows Server. I've set a password for SYS/SYSTEM when prompted, I've opened port 8080 to receive incoming connections and http://example.com:8080/apex loads fine. In ...
1
vote
1answer
202 views

Difference between ASYNC & SYNC real time apply

What is the difference between a data guard (Oracle 11g) operating in max performance ASYNC mode and enabling real time apply and a data guard operating in maximum availability SYNC and enabling real ...
-1
votes
1answer
164 views

ORA-00904: : invalid identifier [closed]

Anyone help me with this error. I'm trying to create a table named "accounts" with 2 columns. CREATE TABLE accounts ( accountno number(10) NULL, accountname varchar2(50) NOT NULL, ); > ORA-00904: ...
1
vote
2answers
208 views

Permissions needed for dbms_metadata.get_ddl in a function

Oracle Enterprise 11.2.0.3 I'm receiving: ORA-31603: object "string of type "string" not found in schema "string" when executing a function that calls dbms_metadata.get_ddl. I know that ...
1
vote
2answers
62 views

Add standby database on different OS version?

Primary database is installed on RHEL 6.1. Standby database software is installed on RHEL 7.0. I am trying to add new standby database by using Grid Control. Database versions are same 11.0.2.0.3. I ...
1
vote
2answers
9k views

ORA-39165 Schema was not found in export

I'm getting the following error on a data pump export: ORA-39165: Schema OFDAT was not found. First I verified that I had full export permission: GRANT EXPORT FULL DATABASE TO OFDAT; Then I ...
0
votes
1answer
60 views

Oracle 11g: SQLDeveloper

How would I use the pre-installed SQLDeveloper that comes packaged within Oracle 11g if I am invoking it with a user that has no oracle or admin rights on linux? Would it be enough just to change ...
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 ...
0
votes
1answer
85 views

Fill a GTT with external data each session

I have to import an external csv file into a table in database each day. The file is encoded in 'UCS2-LE BOM'. I have found a solution using external tables and bulk insertion, but the problem is to ...
2
votes
2answers
22k views

Where all can the /*+ PARALLEL */ hint be used?

It works with SELECT and UPDATE statements. Are there some more statements with which it works? I was able to use it with create, but am not sure if the hint was actually considered. SQL> create ...
0
votes
2answers
1k views

RMAN restore from tag

oracle 11.2.0.4 on redhat 6 SERV1 is prod server running database MAZ, SERV2 is developemt server with no databases on it. i have noted the dbid and last SCN, and then taken a backup on SERV1 using ...