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
1answer
3k views

Delete Archive log file

I am using oracle database 11g. Some day's back we have deleted our Archivelog Manually, and now it's getting full and due to that we are not able to connect to our database. So how should i delete ...
0
votes
1answer
4k views

check listener status other than 1521

I have two listner: one is running on port 1521 another on 1531. lsnrctl stat show's status of services running on port 1521 only. How should i check whether the services running on port 1531 is ...
6
votes
3answers
6k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
2
votes
1answer
4k views

After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail

I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
2
votes
2answers
209 views

Support ticket system - when/how to move old tickets?

I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting ...
2
votes
1answer
57 views

Transaction stays in capture queue

I set up simple bidirectional db replication using Oracle Streams. I used enterprise manager for this. Streams are active and their status is ok. The problem is that the replication is very slow. What ...
9
votes
1answer
1k views

Why am I NOT getting a mutating table error in trigger?

It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation: A mutating table is a table that is being modified ...
2
votes
1answer
2k views

Oracle error “too many values”, must be solved with nested SELECTs

I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs. A simplified description of my problem, on a toy problem describing the hierarchy in a company. ...
4
votes
2answers
2k views

Hibernate Query Running Slow

I am currently facing a problem with a slow running Hibernate query that is being kicked off from a Java program. It is making a call to Oracle 11g at the back end. This query is taking anything from ...
3
votes
2answers
6k views

sqlnet.ora or tnsnames.ora changes require reboot?

Anytime you change Oracle's sqlnet.ora or tnsnames.ora files, does the system require a reboot? In my instance, I only have the Oracle client installed on the machine I'm referring to, but out of ...
0
votes
2answers
631 views

Oracle dump import

After a system failure I bought a new server and now I'm trying to recreate the Oracle database I had. I have a dump file which was made with the command exp and full=y. Can I restore the full ...
10
votes
1answer
1k views

Commit vs Fast Commit vs Commit Cleanout in Oracle Database

I was wondering whether someone could verify my understanding regarding the differences between those 3 terms as pertaining to Oracle Databases. Many sources confuse these terms and do not explain ...
1
vote
1answer
54 views

Supporting Multiple Oracle Clients

I have an application where the data repository is moving to Oracle 11g. This means we need the new Oracle 11g client on the application server to support the retrieval of their data. Is it ...
2
votes
1answer
811 views

How do I export data from Oracle 11.1 to 10.1?

I have a 11.1 oracle server and I need to export a dump file for a client who has a 10.1 server. While reading the documentation, I figured out I needed the 10.1 Export Utility. I searched the ...
1
vote
3answers
116 views

Query missing one date; only happened today

This should be pretty reproducible I hope. I'm on Oracle 11g. This query, which I use as part of another query, is acting strangely: SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM ...
4
votes
1answer
431 views

How to I interleave logs from two different Oracle tables?

I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's ...
2
votes
2answers
87 views

Regular Expression Difficulty

I'm trying to extract 456 from the string :123:456: as follows: select regexp_substr(':123:456:', ':(\d+):', 1, 2, 'i', 1) from dual However, this query returns null. What am I doing wrong?
1
vote
1answer
199 views

Size of an empty database

Continuing with my school-project, now I have to estimate the size of the database from here to 5 years. They give me a formula, so, in that formula, one of the values is the size of the empty ...
1
vote
1answer
2k 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 ...
6
votes
3answers
2k views

ALTER SYSTEM DISCONNECT SESSION doesn't work

Oracle 11g. The devs leave sessions connected overnight, sometimes certain cron jobs fail because of that. I am trying to kill all user sessions, but I can't. I login as: sqlplus system/[password] ...
1
vote
1answer
2k views

ORA-12541: TNS:no listener using EXPDP

I need to export an Oracle 11g database to an Oracle 10g instance. I have the command, which I believe is correct: expdp user/pass@192.168.10.8 full=Y VERSION=10.2 directory=m_dump ...
2
votes
2answers
4k views

Troubleshooting Oracle ORA-08103 - object no longer exists

I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against it. Since I'm truncating a different ...
2
votes
1answer
287 views

ETA: translate Oracle SQL queries to MySQL

task: replace Oracle 11g with MySQL current setup: Oracle 11g SE One about 80 tables stored procedures, PL/SQL are in place but out of scope for this question client applications do not use a ...
1
vote
0answers
132 views

Port no in Oracle Forms 11G

Which port number to be used in forms 11G Preference-->Runtime--> Application ServerURL Section? I have: 1521-Given at the time of database creation 7003 and 7004-Given at the time of WLS Domain ...
1
vote
1answer
372 views

Help with getters & setters in PL/SQL

I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question: I have 2 tables one file level & another batch level. In a day, only 1 file_id is ...
1
vote
1answer
472 views

Oracle out-of-place upgrade on same host: impdp issues

I am testing an out-of-place Oracle upgrade from 11.2.0.2 to 11.2.0.3 on the same host. From what I've read, expdp and impdp is the way to go. My problem is that since I am importing to another ...
1
vote
3answers
2k views

Unable to mount oracle instance

I am trying to shutdown the system, then I want to mount it. I have tried this: SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ...
1
vote
1answer
3k views

Oracle server connection reset by peer socket write error

Full disclosure, I'm not a database admin, but if anyone is able to help me, it would be you guys. Our Oracle 11g database server stopped working, I'm not aware of anything that could have caused it, ...
2
votes
1answer
401 views

How do I connect to Oracle Server without installing Oracle Client on client machines?

I am using Microsoft VB.Net to create a Windows application, and would like some suggestions on how to connect to an Oracle database.
-4
votes
1answer
3k views

primary key, foreign key relationship between the department and employee tables How do I install? (sql_developer) [closed]

EMPLOYEES TABLE COLUMN NAMES: EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_LASTNAME JOB EMPLOYEE_BUSINESS_PHONE EMPLOYEE_GSM SALARY HIRE_DATE E_MAIL DEPARTMAN_NO NOTE:FOR THIS TABLE: EMPLOYEE_ID IS PRIMARY ...
2
votes
2answers
2k views

Encoding problems with sqlplus, Linux

I use sqlplus and all non-Latin characters appear as '?' when I run select queries. When I run insert or update queries with non-Latin characters I get question marks on a web page. select * from ...
0
votes
2answers
2k views

Oracle - capture row commit order in a table

Is there a way to capture the order in which rows get committed in a table ? Our application has clients that query the table based on a sequence_id column of the table, that is supposed to reflect ...
0
votes
1answer
277 views

How to troubleshoot/debug Oracle permission problems

An application is connecting to Oracle to do stuff. It's not working but I don't get any ORA errors in the application itself. I'm thinking its a permission problem. So my question is what is the best ...
0
votes
1answer
81 views

Can you search using an index while indexes are being updated in oracle11g?

I am using oracle 11g. I noticed some performance problems that causes hanging on some search operations. Can I still search based on an index while that index is being updated in the database?
1
vote
0answers
168 views

Oracle ASM failover strategies

I inherited a production system with the following setup: Disk A, Failgroup AB Disk B, Failgroup AB Disk C, Failgroup CD Disk D, Failgroup CD Diskgroups AB and CD are configured with normal ...
1
vote
1answer
658 views

Oracle Client vs Oracle Instant Client on Linux!

to have the following setup on a Linux box: SQLPlus client library (oci.dll)!!!! ORACLE_HOME is set for users is Oracle Instant Client enough or I need to have the Oracle Client installed on the ...
0
votes
4answers
10k 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. ...
2
votes
1answer
609 views

Alter in PL SQL Loop

I just started learning PL/SQL an hour ago... I'm trying to set profile settings dynamically... I select distinct profile names from dba_users and I'm trying to run an alter profile command for each ...
1
vote
1answer
958 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
1answer
3k views

Oracle SQL for left outer join to rownum = 1 of another query?

Background I have a query joining many tables together, the chief of which is a workorder table. I also have a table called WOStatusHistory, which stores every status for every workorder. A diagram ...
4
votes
1answer
5k 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 = ...
1
vote
1answer
896 views

How do you monitor specific SQL statement of a specific user ?

We have a few standalone users here which cause high server loads for several 11.1.0.7 Oracle's. I spoke with them a few times but it seems that their queries are causing high loads and probably ...
3
votes
2answers
6k views

Oracle 11g SYSDBA login

I installed Oracle 11g on my win xp machine. Now I want to connect to the database. I was told that to connect to the database from command prompt I should use SQLPLUS "/ AS SYSDBA". But I want to ...
3
votes
1answer
2k views

Oracle11g password expiry issue

I have a user which has a default profile in oracle db. I want to reproduce the following warning ERROR: ORA-28002: the password will expire within X days In dba_profile I set following values. ...
1
vote
1answer
811 views

In Oracle, what grants do I need as a non-schema user to view rows in all_objects?

I have two instances of an oracle 11g database, both have the same object definitions in the schema: tables with indexes and constraints, a sequence, and a stored procedure. The facts of the case: ...
4
votes
1answer
1k views

How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats

Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings: FOR COLUMNS SIZE 5 some_column and FOR COLUMNS some_column SIZE 5 both will create a ...
4
votes
2answers
2k views

Where can I find autoextend events?

In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?
2
votes
0answers
2k views

Oracle 11g displaying ORA-01034, ORA-27101, and Linux “error 2” messages.

When I try to connect to Oracle 11g using: sqlplus noah/noah@orcl I get these errors: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux Error: 2: No such file or ...
5
votes
1answer
519 views

Execution plan using B+ tree index, but also sorts

I'm using Oracle sqlplus. And I have the following query: SELECT fooID from foo MINUS SELECT fooID from bar; I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo ...
2
votes
1answer
5k views

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

I have a problem with a SPFILE as shown below.How can I get rid of that problem?Any suggestions? SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE ...