Oracle 11g is the July 11th, 2007 release of Oracle’s primary database product. New features added in 11.0.1 include Virtual Columns, Automatic Memory Management and Interval Partitioning.

learn more… | top users | synonyms

1
vote
2answers
7k views

Adding subpartitions to existing partitioned table

In Oracle 11g, is there a way to introduce subpartitions into an existing partitioned table? I cannot seem to find a combination of EXCHANGE and SPLIT partition that does the right thing. SPLIT will ...
13
votes
6answers
29k views

Best way to delete very large recordset in Oracle

I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no ...
0
votes
0answers
46 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 ...
4
votes
2answers
12k views

How and when to use sys_refcursor in oracle

Can some one give me a small explanation about how and when should someone use sys_refcursor ?
1
vote
1answer
34 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 ...
0
votes
1answer
48 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
611 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
votes
1answer
26 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 ...
0
votes
0answers
142 views

Update error: cannot modify a column which maps to a non key-preserved table

I am having a problem in Oracle update statement. UPDATE ( SELECT cc.string21, c.boolean5, cc.string22, cc.string23, cc.string24, cc.string25 FROM ...
0
votes
0answers
32 views

Materialized View

I am new to oracle I have problem can any one help me please. I have one base table, and my requirement query like below. SELECT a.asset_id, a.asset_customattributes_id, cc.string21 AS string46, ...
0
votes
0answers
16 views

update multiple column from multiple tables [duplicate]

I am trying to update multiple columns from multiple tables. My query like below but this is not working , how can write in correct way to update this. UPDATE Z_test_agl_asset a Z_test_cust_att c ON ...
0
votes
0answers
181 views

update multiple columns from multiple join tables with selected data from multiple tables in where condition

please help me. UPDATE Z_test_agl_asset a, Z_test_cust_att c ON (c.customattributes_id=a.asset_customattributes_id) , Z_test_cust_att cc ON (cc.customattributes_id=c.extendedcustomattributes_id) ...
1
vote
1answer
35 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
2answers
677 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 ...
2
votes
2answers
260 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 ...
0
votes
1answer
104 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 ...
1
vote
0answers
77 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 last ...
0
votes
1answer
73 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 ...
2
votes
1answer
104 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 ...
1
vote
2answers
1k views

Oracle's UTL_FILE when connected locally or through listener has different behaviour

We have a procedure which creates a CSV file with UTL_FILE. (env is Oracle 11.2.0.1 and RHEL 6.4) Until now, we were storing that CSV file into system oracle (the oracle owner) user home ...
0
votes
1answer
119 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
132 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
1answer
72 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 ...
4
votes
1answer
8k views

Connection to an oracle db from another computer

I have installed Oracle 11g to an ubuntu, I can connect to DB using sqlplus from the localhost, But I cannot connect from another computer, I have configured my tnsnames.ora as followed; ELIF = ...
0
votes
1answer
41 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 ...
0
votes
0answers
167 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 ...
1
vote
1answer
2k 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
0answers
199 views

DBMS_CRYPTO - ORA-06521 PL/SQL: Error mapping function

PACKAGE: user/password -- SYSTEM/SYSTEM CREATE OR REPLACE PACKAGE enc_dec AS FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC; FUNCTION decrypt (p_encryptedText RAW) RETURN ...
0
votes
1answer
47 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 ...
4
votes
2answers
432 views

Oracle 11g XMLType Experience

We are currently evaluating possibilities to store generic data structures. We found that at least from a functional point of view Oracle XMLType is a good alternative to the good old BLOB. Because ...
1
vote
1answer
1k views

How can I change default backup location for oracle11g using RMAN?

I want to change to another drive due to lack of disk space. How can I change the default location of backup files used by oracle to another drive using RMAN? By default it's in the folder ...
0
votes
0answers
230 views

Renaming the system generated partitions during interval partitioning

I am trying to rename the system-generated partition names (generated during interval partitioning). I try to do this while my table is live(data read and write is happening). I need two scripts. I ...
0
votes
0answers
182 views

merge in cursor is slow ,any alternative for that in oracle 11g

we have a requirement like below. we need to update one table A from another table B based on joining criteria. both tables are having porper joining columns. Table A has 2 columns missing_order_cw ...
0
votes
0answers
203 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
2answers
189 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
3answers
147 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 ...
2
votes
1answer
3k views

Oracle.exe (SHAD) process keeps increasing

I have an Oracle Server 11g that we use for integration tests. For some reason, the processes count [select count(*) from v$process;] keeps increasing up to the limit, causing ORA-12514: TNS:listener ...
0
votes
0answers
64 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 ...
0
votes
4answers
18k views

Convert a Unix timestamp to a DATETIME in a View

I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out. ...
0
votes
2answers
277 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 ...
3
votes
2answers
4k views

How to move the ownership/copy a table from one user to another?

I've been tasked with doing some tidying of our database. I'm not a DBA expert so here I am. (We don't have a DBA expert either). We have a user Old_User and another user New_User. Old_User owns a ...
0
votes
1answer
86 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
652 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 ...
4
votes
1answer
449 views

Negative number of rows inserted

I just ran a rather simple INSERT statement (that I think processed a lot of rows), and I got the message: -1,311,750,183 rows inserted Should I be concerned about the fact that it's negative?
1
vote
0answers
59 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 ...
1
vote
1answer
2k 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
0answers
455 views

oracle streams apply: how to get a reason why LCR message was not applied

I've set up bidirectional oracle streams replication (11gR1) using identical scripts on both machines (DB1 and DB2). Although changes from DB1 are being applied to DB2, changes from DB2 to DB1 aren't. ...
0
votes
1answer
61 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
319 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
311 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 ...