Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
715
questions
22
votes
3
answers
82k
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:
╔══════════╦══════════╗
║ ...
- 887
20
votes
4
answers
91k
views
Can I change the SID of an Oracle database?
The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
Is there an easy & quick way to change the SIDs of the test databases on the server?
Dropping & recreating of ...
- 12.2k
19
votes
6
answers
83k
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 ...
- 335
18
votes
4
answers
62k
views
How can I unload Oracle database tables into a csv files?
I need to retrieve certain data from an Oracle 11g release 1 database and provide it as one or more CSV files. The data resides in multiple tables and/or views. All this should work via the command ...
- 183
17
votes
1
answer
87k
views
Oracle 11g listener fails with ORA-12514 and ORA-12505 errors
I run an instance of Oracle 11g locally on my development machine and can connect to the local instance directly via SqlPlus:
c:\>sqlplus ace
SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar ...
- 421
15
votes
3
answers
38k
views
Is there any difference between Rem and -- comments for SQL*Plus?
On SQL*Plus prompt, both Rem and -- qualify as comment indicators:
Rem this is a comment
-- this is also a comment
create table emp (
id number primary key,
name cvarchar2(40));
Is there any ...
- 3,351
13
votes
5
answers
50k
views
In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?
I'm writing a script to populate some tables with data for testing.
I would like to write something like the following but I don't know how to do it (I'm Oracle 11g)
SET ENABLED_USER_ID = SEQ....
- 397
13
votes
2
answers
144k
views
How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora?
I have access to a ORACLE 11g server using the following listener.ora
# listener.ora Network Configuration File: D:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by ...
- 12.2k
12
votes
2
answers
2k
views
LNNVL Justification
LNNVL is an oracle built in function that returns TRUE for conditions evaluating to FALSE or UNKNOWN, and returns FALSE for conditions evaluating to TRUE. My question is what would be the benefit of ...
- 23.8k
12
votes
1
answer
5k
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 ...
- 261
11
votes
2
answers
130k
views
How to see profile assigned to a user
Let's say i have created a user as 'TEST_USER', and then i assigned a profile to it.
Now how can i see for TEST_USER which profile has been assigned?
I want some query that will check what profile a ...
- 223
11
votes
2
answers
75k
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 ...
- 587
11
votes
1
answer
7k
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 by ...
- 895
10
votes
4
answers
91k
views
ORA-01502: index or partition of such index is in usable state problem
I have a table in my Oracle database, where
select pkcol, count(*) from myTable group by pkcol having count(*) > 1;
yields
PKCOL COUNT(*)
------- ----------
1 2
2 ...
- 12.2k
10
votes
2
answers
74k
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 ?
10
votes
2
answers
6k
views
Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?
I want to access data in an Oracle 11g database from SQL-Server 2008
I set up a linked server and when I execute
select * from [Link_server_name]..Oracle_schema.Oracle_table
and Oracle_table ...
- 12.2k
9
votes
1
answer
885
views
Designing Simple Schema for Disaggregation of Demand Forecast
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw ...
- 201
9
votes
1
answer
1k
views
Oracle Ref Partitioning: Deadlock due to child table row migration
My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc.
ORDERS table is LIST partitioned on STATUS column and all ...
- 265
8
votes
4
answers
101k
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.
...
- 451
8
votes
2
answers
7k
views
How Long Would Take to Upgrade an 1T Oracle DB from 10g to 11g?
How long would take to upgrade an Oracle DB which has 1T data from 10g to 11g usually/roughly? I need to estimate the down time for it since it is a prod db. Thanks much!
- 283
8
votes
5
answers
41k
views
Simplified/automated datafile free disk space reclaim
On Oracle version 11g:
After Googling, I can't find a simple way to reclaim free space after deleting a table.
I've found a lot of explanations, telling how the datafile becomes fragmented, the big ...
- 937
8
votes
3
answers
21k
views
Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)
I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job;
alter system set memory_max_target = 512M scope = spfile;
Which I guess ...
- 213
8
votes
1
answer
32k
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:
...
8
votes
1
answer
46k
views
temporary table inside procedure oracle
I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g.
Mysql procedures allow me to:
1. Create temporary tables
2. Insert data into temporary tables / Query these ...
8
votes
1
answer
466
views
How to get hierarchical data from two tables in oracle?
I have two tables table1 and table2, I need hierarchical output by joining of both of the tables.
Table1 contains three categories CAT1, CAT2 and CAT3 with one Foreign key i.e. F_ID which is a ...
- 183
7
votes
2
answers
83k
views
Sql*Plus ERROR: ORA-12154: TNS:could not resolve the connect identifier specified
I installed Sql*PLUS on a cloud Oracle Linux Environment in order to access an remote database. I used the installation instructions provided as an answer here: https://stackoverflow.com/questions/...
- 201
7
votes
3
answers
37k
views
Passing parameters to a procedure executed by DBMS_SCHEDULER
I have a scheduler program that runs a stored procedure that requires input parameters.
BEGIN
DBMS_SCHEDULER.DROP_PROGRAM
(program_name => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/...
7
votes
5
answers
18k
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 ...
- 171
6
votes
3
answers
45k
views
ORA-02270: no matching unique or primary key for this column-list
I getting an error report of:
Error report -
SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*...
6
votes
2
answers
10k
views
How to find foreign keys that don't have corresponding indexes?
I have installed Oracle database 11g R2 with a customized application. In my environment we are experiencing locking on tables. I want to check if there are foreign keys in my database without the ...
- 167
6
votes
1
answer
181k
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 ...
- 163
6
votes
6
answers
30k
views
Oracle 11g setup closes without errors on pre-requisite check
I'm installing Oracle 11g and the setup wizard just closes automatically after the pre-requisite check.
I have administrative access
UAC is disabled
IPv6 is diabled
There is more than enough free ...
6
votes
2
answers
40k
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 ...
- 434
6
votes
1
answer
21k
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 =
(...
- 163
6
votes
2
answers
781
views
Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command
I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below:
create table test_table1
(col1 nvarchar2(100));
table TEST_TABLE1 ...
- 195
6
votes
3
answers
15k
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] ...
- 163
6
votes
3
answers
7k
views
Add Oracle Label Security to an existing Oracle installation
We are using Amazon EC2 to host some Oracle database instances while we are evaluating and prototyping some software. We are using the AMIs provided by Oracle - specifically, Oracle Database 11g ...
- 161
5
votes
2
answers
5k
views
PL/SQL function to receive a number and return its binary format
I'm trying to write a function to receive a number and return its binary format. This is what I've developed so far and it's ok for inputs : 4,8,16 but it does not return correct binary format for ...
- 1,412
5
votes
2
answers
9k
views
Oracle 10g dump file push into Oracle 11g database
Currently when executing the imp command with the following parameters: file=my.dmp log=my.log fromuser=myuser touser=myuser
The following errors occur:
IMP-00058: ORACLE error 12560 ...
- 201
5
votes
6
answers
96k
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
After ...
- 433
5
votes
2
answers
663
views
Learning Oracle, does it matter if I install 10g or 11g?
I'm taking a class in Databases, they're using Oracle 10g in class, but I'm having some trouble installing the database here in Windows 8 (auth problems), so I thought I'd attempt 11g.
Is 11g ...
- 391
5
votes
2
answers
13k
views
Is index performance on a TIMESTAMP(6) column impacted when converting it to a number
If I have an index on a TIMESTAMP(6) column, am I forced to use this column as a TIMESTAMP(6) to get the benefits of the index? Can I convert this column to a number with second precision (...
- 153
5
votes
1
answer
473
views
Result on execution of any SQL is number 2
After successful execution of any sql in oracle sql command line it shows 2
like i want to see the table or description of any table it show 2 instead of showing the table.
SQL> select * from dual
...
- 61
5
votes
1
answer
23k
views
How to create an Oracle Database Link when the password has special characters
I'm trying to create a DBLink. SQL Developer is the client I'm using. The password to my remote user has *'s in it. I'm having trouble figuring this out. This syntax isn't working:
CREATE DATABASE ...
- 361
5
votes
2
answers
51k
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);
...
- 504
5
votes
3
answers
26k
views
ORA-03113 when starting up an Oracle 11g database
I have read "ORA-03113: end-of-file on communication channel" on startup and others from this site to solve my problem but I still have not solved it. All I get is the following:
Microsoft ...
5
votes
1
answer
20k
views
ADD Column ON UPDATE TIMESTAMP
I want to alter a table to include modified and created columns. However, I am having trouble with adding the modified column. I get the following error:
SQL Error: ORA-00907: missing right ...
- 207
5
votes
1
answer
19k
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 ...
- 283
5
votes
2
answers
21k
views
Oracle Wallet Setup ORA-12578
I am having trouble setting up the Oracle Wallet on a new box connecting to a new database. I have added the following lines to my SQLNET.ORA:
SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION = (SOURCE=...
- 23.8k
5
votes
1
answer
14k
views
Update oracle sql database from CSV
I tried google at first but no luck.
Is it possible to update tables from csv file?
I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the ...
- 51