Questions tagged [oracle-11g-r2]

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

Filter by
Sorted by
Tagged with
15
votes
2answers
95k views

“ORA-01950: no privileges on tablespace 'SYSTEM' ” : What error is it?

Earlier I created a user : SQL>create user suhail identified by password SQL>User created. SQL>Grant create session,create table to suhail; SQL>Grant Succeeded. but when I login as ...
7
votes
4answers
1k views

Does an IOT guarantee order in a select?

We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1). The priority does not matter ...
32
votes
9answers
34k views

How do you version your Oracle database changes?

I am interested to know what methods other people are using to keep track of changes made to the database including table definition changes, new objects, packages changes, etc. Do you use flat files ...
5
votes
2answers
25k views

What is the difference between Schema and Database in Oracle?

In Oracle, datafile are system files where actual data is stored. The collection of datafiles make tablespace and, at last, database is collection of tablespaces. Correct me if I am wrong on the ...
6
votes
4answers
18k 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) ...
39
votes
4answers
73k views

What is the difference between sys and system accounts in Oracle databases?

There are two ways to connect to Oracle as a administrator using sqlplus: sqlplus sys as sysdba sqlplus system/manager These accounts should be uses for different purposes, I suppose. Which tasks ...
8
votes
2answers
12k views

Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team. They ...
13
votes
2answers
62k views

Importing schema to new or different tablespace

Is there a convenient way to import a schema into Oracle 11gR2 using a single new or different tablespace than where the data originated? As an example, I have exported BLOG_DATA from OLDDB, where ...
7
votes
7answers
22k views

Oracle 11g installation stuck at installation

I'm trying to install oracle 11g on fedora by following this tutorial but it stuck after I click Next in step 8. The screen does not go away even after several hours. There's no log file in ...
7
votes
2answers
19k 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
2answers
25k views

Installing scott schema for oracle 11g express

I want to install scott schema, a sample database in oracle. But in express edition, I can't find out it. Please help me install it on Oracle 11g express. I'm a newbie, pls help me
1
vote
2answers
979 views

Is there any other way that we can take auto backup in oracle 11g apart from RMAN?

I have tried with RMAN. But is there any other possible way that we can backup database and tables. backup using queries or stored procedures is possible?
44
votes
14answers
155k 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 ...
22
votes
6answers
449k views

How to determine if an Oracle table is locked or not?

We've been using BI software and a repository database that are installed on Oracle Enterprise 11gR2. Some of these batch reports will try to access a database table which may still be locked. How ...
14
votes
2answers
76k views

How to free up disk space? which logs/directories to clean?

I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size /u01/app/11.2.0/grid/cv/log /u01/app/11.2.0/grid/log/...
29
votes
4answers
16k views

Why is OS authentication considered poor security for Oracle databases?

Oracle is deprecating OS authentication according to the Oracle Database Security Guide, which says Be aware that the REMOTE_OS_AUTHENT parameter was deprecated in Oracle Database 11g Release 1 ...
11
votes
2answers
21k views

Why in Oracle 11gR2 I can't drop the materialized view with the same user that created it?

I created a materialized view with the DI_TEST_AL user, let's name it MY_MVIEW. It appears in the USER_OBJECTS table as MATERIALIZED VIEW, I try to drop it, I get a success message, but the object is ...
10
votes
3answers
6k views

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method ...
5
votes
6answers
14k views

Lack of space in SYSTEM and SYSAUX tablespace in oracle 11g R2

SYSTEM and SYSAUX Tablespace size of my DB is in the following status now: SYSTEM - PCT_USED= 99 SYSAUX - PCT_USED= 95 I believe the db is in critical situation now. What can I do to bring back it ...
4
votes
1answer
2k views

How do I import data into oracle 11g r2 using a `.dmp` file and a `.par` file?

How do I import data into a new installation of oracle 11g r2 using a .dmp file and a .par file? Here is what I have done so far, on a Windows 8.1 devbox: 1.) Open command line as administrator 2.)...
2
votes
1answer
88 views

How do I get multiple rows output in one line?

The data is like Jane has items pen pencil eraser. If I write the statement 'select * from item' the output is: Jane pen Jane pencil Jane eraser Jack box Jack wiper I want the output to be like: ...
2
votes
1answer
1k views

Oracle 10gR2 to 11gR2 upgrade - performance hit [closed]

We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to 11gR2 (11.2.0.3) and we are noticing a significant hit in performance although execution plans are the same for the offended queries before and ...
1
vote
1answer
8k views

Oracle Database Client 11g R2 Installation Types, which components?

I need to know which component is under each installation type! Instant Client Administrator Runtime Custom (No need as it shows all the components) ALSO? does it includes ODP.NET? is it the ...
9
votes
2answers
4k views

Oracle Shutdown Method

Shutting down a database before doing an upgrade or a patch can be done several ways. shutdown immediate; or shutdown abort; startup restrict; shutdown immediate; or shutdown abort; startup ...
6
votes
4answers
22k views

Why does = NULL not work in Oracle?

I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL. Why does = NULL not work here?
5
votes
2answers
37k views

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Couple of days back, I execute shutdown on my DB but it took very long time so I shutdown my m/c. After restarting my machine, I get this exception when I try to connect to my database using TOAD. I ...
5
votes
2answers
2k views

Repartition Primary Key Index online in Oracle

I have an 24x7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention. We use sequences for the ...
2
votes
1answer
9k views

Is it possible to query value of SQLNET.EXPIRE_TIME parameter?

Can somebody advice whether it is possible to query for current value of SQLNET.EXPIRE_TIME parameter in Oracle 11 DB? Should select * from v$parameter return value of SQLNET.EXPIRE_TIME (if it ...
2
votes
2answers
19k views

Oracle SYS Auditing with Audit Trail to DB

I have an Oracle Database 11g R2 on Oracle Enterprise Linux. I have enabled SYS auditing as: SQL> ALTER SYSTEM SET audit_sys_operations=true SCOPE=spfile; SQL> ALTER SYSTEM SET audit_trail=OS ...
1
vote
1answer
19k views

Encountering “Snapshot too old” error during execution of expdp command

As am facing an issue while performing expdp command in my production db.(Oracle 11g in windows enviornment) cmd> expdp 'sys/123@PROD as sysdba' DUMPFILE=BACKUP_02082017_BACKUP.dmp LOGFILE=...
0
votes
1answer
2k views

Removing editioning from an Oracle user

I maintain an Oracle Enterprise 11.2.0.3 database where one user has editioning enabled. I understand that this was an irreversible choice according to the documentation. If I wish to revert that ...
13
votes
2answers
4k views

Security for Application Developers doing PL/SQL work in Oracle

How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...
7
votes
1answer
176 views

Cardinality is so far off from reality

I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2. select count(*) from table1 where col1 = ...
6
votes
3answers
1k views

Oracle Advanced Queuing LIFO

With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
6
votes
1answer
1k views

Enforce data integrity with a recursive table

I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
6
votes
1answer
19k views

oracle format specifiers: to_number vs to_char

SQL> select TO_NUMBER(123.56,'999.9') from dual; select TO_NUMBER(123.56,'999.9') from dual * ERROR at line 1: ORA-01722: invalid number SQL> select TO_CHAR(123.56,'999.9') ...
5
votes
2answers
2k views

What is the difference between these two create table statements?

a) SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE); Table created. SQL> desc xml_tab1; Name Null? Type ----------------------------------------- -------- --...
4
votes
3answers
5k views

TNS-12541 Error

I have grid installed in my pc but the problem is LISTENER does not start automatically. I have to start listener manually and then only LISTENER get started. But before starting listener if you check ...
4
votes
2answers
354 views

Why does SAMPLE BLOCK often return zero?

In a comment on another question, someone asked: I used SAMPLE BLOCK in many tests but found that it would inexplicably return '0' much of the time. Any idea why? I speculated that this might be ...
4
votes
4answers
11k views

Is (and if how) it possible in Oracle to “insert into <table> values <rowtype-variable>” if <table> has virtual columns

Here's a table create table tq84_virtual_test_without ( col_1 number, col_2 number, col_3 number, col_4 number, col_5 number ); with the rule that col_5's value is the sum of the other ...
4
votes
2answers
38k views

Using Oracle client to connect to remote database

Although I can do a telnet to remote oracle port I am unable to connect. I have a following tnsname.ora connecting to oracle database using sql plus: remoteOracle = (DESCRIPTION = (ADDRESS_LIST ...
3
votes
1answer
12k views

2 Different Oracle DB Version on same server

Is it possible to install and run 2 different oracle version(11g and 12c)software and db on same server? is it possible then how we can? and if not then why? Thanks Rahul Joshi
3
votes
1answer
7k views

How do I open Data Pump Import Wizard in SQL Developer 4.1?

How do I find and open the Data Pump Import Wizard in SQL Developer 4.1? I opened the Help search tool in SQL Developer 4.1 and searched for Data Pump, and found directions that include: 1.) ...
2
votes
1answer
1k views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in Store1, ...
1
vote
1answer
41k views

ORA-01034: ORACLE not available in oracle 11g

I installed oracle 11g on ubuntu 14.04 and i started lister and the message is as below. LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 10-OCT-2014 14:20:02 Copyright (c) 1991, 2011, Oracle. ...
1
vote
2answers
100 views

Reading data from oracle 8 in 2019

I'm an intern stuck dealing with a database system that is as old as I am. The Issue: I have a perl script pulling data from a server running Oracle 8 (8.1.7) and placing the data into a .csv. The ...
1
vote
1answer
2k views

Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index

One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index. Its values are just a sequence ...
1
vote
2answers
669 views

Not able to select the disk during Oracle 11g Installation

Hi All, During the 11g installation on Win 2012, I am not able to select the available disks which are already stamped using ASM. Any idea what could be the reason? I have tried to select using ...
1
vote
1answer
444 views

How to refresh Oracle data dictionary to remove deleted tablespace and datafiles?

Long story short we have a server with two Oracle instances running on it: ora1 and ora2. Somebody defined an undo tablespace for ora2 and provided the same datafile of the undo tablespace datafile of ...
1
vote
2answers
157 views

Does any kind of constraint in a database have a unique constraint name?

I know that it is possible and even encouraged to add primary key constraints and foreign key constraints after the creation of the table so that the programmer has control over the names of the ...