Questions tagged [oracle-11g]
Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.
37
questions
22
votes
3
answers
83k
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
18
votes
4
answers
63k
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
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
13
votes
2
answers
145k
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.3k
5
votes
2
answers
24k
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 ...
- 163
4
votes
2
answers
25k
views
How to execute this procedure in PL/SQL?
I have this table in the below format:
Persnbr | Userfieldcd | Value
01 | Port | Funds
01 | Vip1 | Systems
02 | Port | Bank
02 | Vip1 | Authority
This is how I want ...
- 65
2
votes
1
answer
523
views
Oracle isn't restoring correctly a backup
First of all I'll say I'm using:
Oracle Database 11g Express Edition
Oracle SQL developer
What I'm trying to achieve
Create a backup and restore it, so for example I can delete a table after a ...
- 127
0
votes
1
answer
539
views
Oracle 11g: increasing buffer cache with AMM
On a 2-node RAC we have enabled Automatic Memory Management. The advisor views indicate that increasing the buffer cache by 4 GB will half the physical reads, and we have 4 GB to give.
How must we ...
- 53
-1
votes
1
answer
2k
views
How To Delete Record From dba_audit_trail in Oracle 11gR2
I want to delete records older than one month from AUD$ table, but I have to do this on dba_audit_trail, because I have to do this job according dba_audit_trail's timestamp. I can't find any clear ...
- 55
19
votes
6
answers
85k
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
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
11
votes
4
answers
105k
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.3k
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
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.3k
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 ...
- 977
8
votes
1
answer
47k
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 ...
6
votes
2
answers
826
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
2
answers
11k
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
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 ...
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
2
answers
16k
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 ...
- 454
4
votes
1
answer
4k
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.)...
- 487
4
votes
1
answer
8k
views
Select multiple columns from subquery - Connect by
I have a table which manages folder structure. Hence it has a parent-child relationship. We trigger a query to sum all documents in the complete heirarchy:
SELECT folder_name name,
...
- 93
3
votes
1
answer
9k
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.) ...
- 67
2
votes
1
answer
5k
views
Indexing issues while partitioning with dbms_redefinition
I get an error while doing the redefinition. Following are the steps which I have taken so far:
Step 1. Start redefinition
DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'T_FDC_TOOLCONTEXT',
...
- 21
2
votes
1
answer
841
views
Oracle 11.2: Backup and recovery strategy based on full cold backup plus expdp
Are there any issues with the following strategy for backup/recovery in Oracle 11.2.0.4?
Perform a full database cold backup once a month, moving the file off the server for safekeeping.
Write a cron ...
- 149
2
votes
0
answers
505
views
How to turn on the expanded mode in Oracle SQL*Plus?
As in the switch \x of PostgreSQL.
For more information on the PostgreSQL expanded mode, fetch https://serverfault.com/questions/618642/psql-expanded-mode-equivalency-for-mysql.
Both PostgreSQL and ...
- 86
2
votes
1
answer
9k
views
ORA-48108 & ORA-01034
I'm trying to set up a database on a Solaris 10 platform that had its database corrupted before. Due to the lack of backups, I was forced to reinstall Oracle server. Currently I'm facing an issue ...
2
votes
1
answer
14k
views
Rename interval partitioning system generated partition names
I have an existing table which I partitioned (interval partitioning on a daily basis) using dbms_redefnition.
PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "p1_1" ...
- 99
2
votes
2
answers
15k
views
Oracle 11gR2 Installation on Oracle linux 7 - Prerequisite condition failed for OS kernel parameter "semmni"
while Installing oracle 11gr2 on oracle Linux 7. The prerequisite condition to test whether the OS kernel parameter "semmni" is failed with below error.
Please find the screenshot of it.
Below is ...
- 55
1
vote
1
answer
12k
views
Difference in database dump file size
Actually I am bit confused with my database export, while i exporting it shows "Total estimation using BLOCKS method: 20.83 GB" But my DB export dump is only 6 GB. Few days back we deleted some ...
- 152
1
vote
1
answer
184
views
Oracle: How can I create a where clause programmatically?
Suppose I have a table like:
TBL_CONDITIONS
| ID | WHERE_CLAUSE |
|----|---------------------------------------|
| 1 | fruit = 'apple' |
| 2 | ...
- 135
1
vote
1
answer
76
views
Open a Oracle 7.3.x database in Oracle 11
Is there any way to open or convert an old Oracle 7.x database into Oracle 11g?
Thanks in advance!
- 11
1
vote
1
answer
5k
views
How do I include zeros in a count(column)?
I am attempting to build a report for a client. I need a display of all the values in Tab1, Col1 (a validation table), a count of each time that occurred, and a percentage of the total. Here's what I'...
- 113
1
vote
1
answer
6k
views
How to Restrict Schema Access to Specific IP Address & user?
I want to lock down access to specific schemas. Restrict the access to specific users when the connection is made from the specific IP addresses.
Eg Only Access to USER1 and USER2
1
vote
1
answer
12k
views
Oracle 11g XE to 18c XE - What is the difference? Is 18c can be used like 11g XE?
I have worked on Oracle 11g XE about two months ago. After that I de installed them about a week ago. Now, I am curious to learn a little bit more about databases and I tried to installed Oracle 11g ...
- 107
0
votes
1
answer
2k
views
creating stored procedure to populate empty table using pl/sql
I have created a table and inserted a values one row at a time. However, I am trying to populate the table using a "stored procedure" or "trigger". The values can be a random string or number. I have ...
- 5