Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.

learn more… | top users | synonyms

-2
votes
2answers
35 views

creating tablespace failed!

I've restored a db and its failed with many errors. When I look into the log file, I found that the tablespace does not exist. So when I try to create a tablespace in plsql am getting the following ...
0
votes
0answers
56 views

How to store values from a loop and compare them?

I have to select child items from a parent item and then compare text of each child and if the text is same I should not be displaying the heading. I have written a procedure for this, which is like ...
1
vote
1answer
61 views

Rollback in Oracle

I am having a problem in Oracle rollback. In my stored procedure I have multiple insert and update statements. If every thing is fine I am sending success message and also I am tracing success status ...
2
votes
2answers
523 views

Oracle connection suddenly refused on windows 8

I am having all kinds of connection issues with sql developer all of a sudden. It was working till yesterday and stopped working on windows 8 machine. i am still able to connect via command prompt. ...
0
votes
1answer
222 views

Create a new table oracle

Hello I recently changed companies and the databases as well. Having worked with postgres so far, oracle is still fuzzy to me, so here I am stuck with creating a table. This is my SQL attempt : ...
-1
votes
1answer
41 views

sequence id in oracle

I am using Oracle 11g. I have a senario like: I need to increase auto increment id like below 00000001 00000002 00000003 00000004 For example, my table looks like below id empid name ...
-1
votes
1answer
8k views

Commit and rollback in Oracle stored procedure

In my Oracle stored procedure, I have multiple insert and update statements like below: create or replace PROCEDURE SPTest AS BEGIN insert into emptest(empid,empname,deptno) (1,'ravi',10); ...
1
vote
1answer
147 views

What is the privilege my user requires to be able to grant CONNECT, RESOURCE and CREATE SESSION to other users in Oracle 11g?

What is the privilege my user requires to be able to grant the following privileges CONNECT, RESOURCE and CREATE SESSION to OTHER users in Oracle 11g?
0
votes
1answer
473 views

parsing and purpose of oracle audit (.aud) logs

I am looking at the oracle audit logs and am trying to understand and parse. The format appears to be consistent: Date Length: Action: ...etc My questions are: 1) what generates these? Is it every ...
2
votes
2answers
1k views

How to convert html entities in Oracle?

I'm trying to decode html containing html entities. I've tried dbms_xmlgen.convert and utl_i18n.unescape_reference but the results are less than satisfactory. SET ESCAPE ON; SELECT 'dbms_xmlgen....
1
vote
0answers
133 views

Oracle 11g SSIS SQL Server 2014 Truncation

We have a nightly SSIS job which downloads data from a Vendor's Oracle system into our SQL Server 2014 system using Oracle's Wallet. On some tables, we've noticed that some VarChar columns have the ...
0
votes
2answers
1k views

Slow import for users .DMP files in Oracle 11G

After upgrading my DBMS Oracle 10g to 11g, when it will import user files, they are slower than the old version. A .DMP file in Oracle 10g with the size of 20GB takes almost 1 hour to import, but ...
0
votes
1answer
268 views

Keeping messages in a backup table after dequeue is completed

I would like to transfer the enqueued messages to a backup table after they have been dequeued. Currently we are using DBMS_AQ.DEQUEUE with mode dequeue_mode := DBMS_AQ.REMOVE. So after the message ...
0
votes
1answer
370 views

How to install oracle 11g XE and use the data files of another instance (like Postgres)?

I installed Oracle 11g XE in Windows 7 x64, then Windows 8.1 (install new OS, not perform an upgrade of the previously Windows 7 x64 installation). Can be possible to install Oracle 11g XE over the ...
0
votes
1answer
950 views

Oracle Hierarchical query: how to follow max on each level

I'm trying to get a query to "follow" the max values in each level excluding the max values in other branches. Here some examples to illustrate what I'm trying to achieve: Given this hierarchical ...
0
votes
0answers
382 views

Please anybody help me with oracle db importing?

I try to import .DMP file with impd comand. root@HP:~# impdp SYSTEM/xxxxxxxxx log=4.log file=as.DMP and i get this ;;; Import: Release 11.2.0.2.0 - Production on Thu Oct 2 12:21:14 2014 ...
0
votes
1answer
68 views

Oracle dataguard - Redo apply

In a low bandwidth environment when a transaction commits that generates a large amount of redo data will cause other independent transactions to slow down. This is because the redo data of small ...
2
votes
2answers
9k views

Composite primary key from multiple tables / multiple foreign keys

I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this... A great answer was given to building a composite key from multiple foreign ...
0
votes
1answer
160 views

Select * from (every table in an subselect)

I was trying to get an select * from (for column table result) every table_name with the result bellow, how can i achieve this? (SELECT a.table_name FROM all_cons_columns a JOIN all_constraints c ...
0
votes
1answer
208 views

Combining data of multiple rows in view

I have a table that holds timetracking data of users and looks somewhat like this: projectid activityid worklocationid year week day hours 1 22 21 2014 37 09/...
1
vote
1answer
1k views

How to know when a query was executed?

How to find out when a query was executed? I ran a query couple of days ago, I'm not sure about the exact time. I want to know at what date and time the query was executed. Is it possible to get that ...
0
votes
0answers
579 views

How to put more than 1000 values into an Oracle

I have a comma delimited list of many IDs(Database size is very huge here). Sometimes this list can exceed 1000 items, at which point Oracle throws an error. The query is similar to this... select * ...
0
votes
3answers
4k views

Delete all the data from all tables

I have a little problem. I have to delete all the data from our database. I don't want to drop tables, only delete the data sets. These are 47 tables in total. I tried the following: Delete FROM(...
0
votes
1answer
84 views

Update table from different table [closed]

UPDATE RBI.RBI_OV_F_DETAIL A SET (BRANCH_ID, BRANCH_NUMBER) = ( SELECT PRICE_BRANCH_ID, PRICE_BRANCH FROM RSE.RSE_SO_F_ORDERS B WHERE A.SOURCE_ID = B....
2
votes
1answer
118 views

Is it possible to copy mounted database without RMAN?

I need to make a copy of production database for development. For some reason I can't use RMAN. My question is: can I copy standby database while its in mounted state and recovery is canceled? Of ...
2
votes
5answers
48k views

Oracle Export using EXPDP gives ORA-06512 SYS.UTL_FILE error on Windows 7

I am using Oracle 11g and want to export a table using below statement C:\>expdp SYS/sys@MYDATABASE DIRECTORY = Mydir DUMPFILE = Customer.dmp LOGFILE = Customer.log TABLES = PERSON.CUSTOMER ...
0
votes
2answers
1k views

Dynamic permissions

I have a application that creates tables in one user's schema and another user needs to be able to use the dynamically created tables. Example: Application creates User1.RandomTable User2 needs ...
0
votes
1answer
125 views

Table statistics on database

Is there any way that I could find how many tables has statistics on them,and how many tables which doesn't have statistics on them.
0
votes
1answer
2k views

How to add a column in table via stored procedure? [closed]

create or replace procedure SP_PARAMETER_TEST -- this is my table ( NAME_KEY in VARCHAR2 -- this contains a value assume NAME_KEY contains "Gypsum" ) is begin ALTER TABLE PARAMETER_TEST ADD ...
1
vote
0answers
119 views

Sync/Replication of 2 Oracle Databases with NetApp storage

I am working on a high availability setup of Oracle Enterprise Manager. I need the back-end repository replicated (preferably in real time, or something close) to another repository, in which I could ...
5
votes
1answer
10k views

Using Oracle12C Client with Oracle11.g database

I have been using Oracle12c client to connect to an Oracle11g database in a remote server for about one month, it wasn't giving me any problems until this week, when I started getting the error: ...
2
votes
1answer
258 views

Running a query over the network multiple times seems to choke performance, how can I pinpoint the problem?

Background: Oracle 11g running on Windows 2003 x64 SP2 on a 3.06GHz Xeon X5675 with 16GB RAM Over the past 3 weeks, a query I used to run approximately every 5 minutes has started to respond very ...
0
votes
1answer
85 views

General question on dbms_stats

General question on dbms_stats and auto sample size? what is the impact of having IOTs with overflows and you switch to auto_sample_size and enable approximate NDV . How does it impact?
2
votes
3answers
758 views

External authentication using Oracle Standard

I want to authenticate my client connections externally, using a custom external application. Is this possible using Oracle Standard 11g? If so, how? (our custom external application will not have ...
0
votes
1answer
625 views

Mapping the oracle database directory object to a network path

I'm an accidental DBA. We have oracle database running on windows server. I created an oracle directory object named 'Result' and mapped it one of the local drives (C:\appResult) on the database ...
5
votes
3answers
5k views

Can't drop UNDO TABLESPACE

We have Oracle 11gR1 RAC two instances on two different servers, new undo tablespace UNDOTBS20140508 created 30G, I want to remove the old one UNDOTBSX02, I did the following: CREATE UNDO TABLESPACE ...
1
vote
0answers
29 views

Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ) [duplicate]

I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue. issue is: when I wanted to integrate OWASP ...
1
vote
1answer
38 views

Locating a schema that owns a role

Let's say we've logged on under the A schema. I am able to execute the following statement grant select on wph_pden to LANDING_R; However, the LANDING_R object can not be located in the schema A. ...
1
vote
1answer
146 views

Does hard-coding schema names in procedure calls for Oracle jobs have any performance impact?

A database application I'm working with uses Oracle scheduled jobs to perform certain tasks. The DBA insists that hard-coding the schema name in the procedures that organise these jobs improves ...
0
votes
0answers
164 views

Oracle AQ Message Replication

We are upgrading our infrastructure and for that we are migrating from Oracle 10g to 11g. We are using Oracle GoldenGate for data replication and it does not support replication of AQ messages as far ...
0
votes
1answer
39 views

Enterpise Manager 12c to Database 11g

Does Oracle Enterpise Manager 12c can be install with Oracle Database 11g? Because the Oracle Enterprise Manager 11g is no longer available.
0
votes
1answer
113 views

EMStagePatches_$ORACLE_SID is using up space

We have this directory: $ORACLE_HOME/EMStagePatches_$ORACLE_SID We are running out of space and this directory is using up a lot of space. Is it safe to delete it? I think it is something to do ...
0
votes
1answer
71 views

Using CURSOR in Oracle 11g database to locate all matches for specifed string in DB

As far as I know in Microsoft SQL Server it is possible to use CURSOR to "scan" database and get all tables/entries where specified string is mentioned. Can I do this in Oracle 11g DB? Any caveats/...
0
votes
2answers
334 views

Restoring Oracle DB using RMAN

I am a application developer doing DBA work. I have to restore an oracle database on our test server. The databases are backed up using RMAN and the last db restore that was done on this server was in ...
0
votes
1answer
210 views

Oracle 11g accessing data in Oracle 7

I am working on a project that involves converting production data from an Oracle 7 DB to our system, which use Oracle 11g. In our other projects, we use DB link to access the other system DB, which ...
0
votes
1answer
496 views

What is the Difference between IN and Not IN performance wise?

I am new to database.I am having doubt that what is the difference between IN and NOT IN performance wise. When I am using IN, takes less time for the same query(Logically same query) when I use NOT ...
0
votes
1answer
66 views

Orace tablespace extension operation

Could somebody clarify whether tablespace expansion procedure in Oracle 11g database requires any downtime or could impact availability of the database? My understanding that this can be done "on the ...
0
votes
2answers
7k views

oracle active sessions waiting for scheduler

Load on my 11g RAC looks like this: and I don't understand how to get insight into what this "scheduler" wait class is. It seems like "scheduler" that it refers to is the process that runs ...
4
votes
1answer
71k views

How to run procedure in Oracle SQL developer

Hello I have a table named CITY in oracle SQL developer, my problem is how to run the procedure -- start the script SET SERVEROUTPUT ON SET LINESIZE 400 SET TIMING ON CREATE OR REPLACE PACKAGE BODY ...
0
votes
2answers
994 views

Changes in Oracle Database implicit conversion rules for select statement

Due to hardware failure after a power outage we had to recreate the database for one of our 3rd party applications from an dumpfile (created via exp). The old hardware had Oracle 10G R2 running while ...