Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.

learn more… | top users | synonyms

4
votes
1answer
210 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, ...
2
votes
1answer
661 views

Oracle 11g bug ? not returning the record until I triggered index to invisible and then to visible

We are using Oracle 11g, 11.2.0.3. We know a record exists in a table but a select is not returning it for some odd reason. The execution plans the general query, not forcing any index, shows that ...
0
votes
2answers
80 views

oracle rman connecting to a database in not mount state

When connected to oracle database through rman directly from the terminal, everything works fine, but when the same connection string is executed through a shell script rman connects to the database ...
4
votes
3answers
6k 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 ...
0
votes
1answer
75 views

how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle

I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up. Is it possible to: create a stored ...
0
votes
1answer
52 views

Having primary key in both fields but repeated in either fields allowed repeating

Is it possible to have both fields to be primary key but allows repeating For either 1 field in Oracle SQL. Student_ID Date 1001 23/10/2016 // allowed 1002 23/10/2016 // ...
2
votes
2answers
12k 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; /...
0
votes
0answers
18 views

Error with displaying ranges of dates when using pivot table

can i know is there any problem with this codes as when i use datagridview and then te pivot table it sometimes the dates runs whereby sometikmes the dates does not follow in sequence. I not sure ...
0
votes
1answer
56 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 | ...
0
votes
1answer
143 views

How can I partitioning oracle table when the table still working?

How can I partitioning oracle table when the table still working? Explain: - I need to partitioning table of Oracle but the table didn't partitioned before. - I have many scheduler I can't close it ...
0
votes
1answer
37 views

Get dates between two months

Can I know why am I getting weird output in my SQL statement whereby my table has two dates of the month May but it only appears for one month, the same things I test for dates range it is working. ...
1
vote
1answer
22 views

Display dates not in ascending for To_Char

can i know why and how i can solve the problem whereby if i use To_Char it does not sort the date in ascending. Below is my code select distinct To_Char (attendance_Date, 'dd/mm/yyyy') from ...
0
votes
0answers
25 views

network Adapter could not establish the connection in netbeans with oracle11g in linux

I am using fedora 23 64 bit. My system is up to date. I have installed oracle 11g xe. I have downloaded the jdbc driver 'ojdbc6" , now i am trying to connect netbeans with the oracle database but it ...
2
votes
2answers
47 views

Select rows where smaller row does not exist

Consider the following table data: ID NAME 1 Kshitiz 2 Aman 5 Aakash 6 Manjeet Here there are records missing between 2 and 5. What query can I use to select row 5 in this table?
0
votes
1answer
53 views

Very fast individual queries but when joined super slow

I've got query 1: select department_id from USER_ACCESS where USER_ACCESS.USER_ID = 13170 AND USER_ACCESS.MANAGEMENT = 1 Takes about 0.475 seconds, which is not a lot for the data set I m ...
0
votes
1answer
13 views

(SQL) User tables are separated into two sections

Using SQL Plus, I create 13 tables via an .sql file, each containing primary and foreign keys. When I execute "select table_name from user_tables;" they are displayed in this partitioned fashion. The ...
14
votes
3answers
38k 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: ╔══════════╦══════════╗ ║ ...
0
votes
2answers
49 views

Name already been used by an existing constraint after renaming the table and creating a new one

I would like to know why, if I alter a table name to different name and then create a table with the same name, I am getting the error "name already been used by existing constraint". This is the ...
0
votes
1answer
50 views

ORA-24033 : No recipient for message has been detected

I am using Oracle E-business Suite R12.1, 11G database version. Error I encountered when trying to create or modify a user or responsibility: ORA-24033 : no recipient for message has been ...
0
votes
1answer
26 views

Is it possible to perform skip N rows and fetch M rows without full scan

Let's say I've got this query: SELECT EMP_ID,LAST_NAME FROM EMPLOYEES WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME; Now if I want to show only the first ten rows between 50 and 60, ...
0
votes
0answers
22 views

Copying values from one table to another table which the column intended to be copied delared as default

Can i know, the below column late_allowed when creating attendance table i declared as default and with a numbers (such as 3). The problem is that i am not sure how to put and where can i put default ...
1
vote
0answers
133 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 the ...
0
votes
1answer
39 views

Help on joining 3 tables Oracle XE 11g

I am trying to return the first name, surname, subject and mark from the following tables: SQL> desc students; Name Null? Type ------------------------ -------- -------------...
0
votes
1answer
425 views

How do I create an Oracle 11g user with read access to all tables/schemas?

I need to create a user that has read access to all the schemas. Do they just need Select priviliges or is there more that needs to be done?
0
votes
0answers
17 views

Prerequisite for learning oracle DBA [duplicate]

i need an advice on the needed skills to start an oracle DBA. Thanks
0
votes
2answers
318 views

Database link from Local Oracle 11g to Oracle Database Cloud Service 11g

We are using Oracle database from Oracle Database Cloud Service, now we are not able to connect Cloud db from Local oracle db. Somehow we created link using below commnd, CREATE DATABASE LINK link_1 ...
0
votes
1answer
42 views

Oracle RAC 11g creation

I would like to play with RAC configuration. Using virtual area (VMware or VirtualBox): RAC1 (Windows 2003 Server) RAC2 (Windows 2003 Server) Shared storage (Openfiler) DNS server (for using SCAN ...
2
votes
1answer
99 views

Why Does Oracle kill a session at the specific time? [closed]

Oracle kills sessions remotely open at the specific time. It means if I open a session at local machine, it is ok. In my case, at every 58 minute. For example, at 13:58, 14:58, 15:58 and so on. It ...
2
votes
1answer
77 views

Problem using pivot to display data

I currently have a table called sample, the structure of my attendance table is: create table sample ( Student_ID VARCHAR2 (10), Late_Attendance number (10) Default 3, Attendance_Date Date, ...
0
votes
1answer
45 views

Multiple counts in a SQL statement

Is it possible to have more than one count in a SQL statement such as: SELECT student_id, ( COUNT (ATTENDANCE_ID) from attendance where attendance_id IN ('ABSENT') ...
1
vote
1answer
59 views

Is there a way to find the partition or subpartition name where my ID belongs

I know if I need to select from any partition I need to provide partition name SELECT * FROM TABLE PARTITION(P1); What if I need to know which partition a particular value belongs using a sql query ...
0
votes
1answer
57 views

Updating data from subpartition when the subpartition name is unknown

Thanks to JSapkota who helped me pull the subpartition name as per below query select subobject_name from user_objects where data_object_id IN (select dbms_rowid.rowid_object(rowid) from TABLE ...
0
votes
1answer
55 views

Help interpreting Oracle RMAN results

I am extremely new to Oracle and had to take a backup on an 11g database that is in NOARCHIVELOG mode. Below is the output. It claims that the backup failed but I have a file of appropriate size where ...
-3
votes
1answer
40 views

Converting SQL Syntax: Oracle to MySQL [closed]

I have the following SQL code written in Oracle SQL developer: CREATE TABLE students ( student_id INT NOT NULL, username VARCHAR2(30), email VARCHAR2(80), password VARCHAR2(30), f_name VARCHAR2(30), ...
0
votes
1answer
62 views

Not exists in sql query

I'm using oracle database I need to display customer id and customer name of those customers who have not purchased at all from any retail outlet using not exists My query: select customerid,...
0
votes
1answer
201 views

Can we expdp using JOINS in QUERY clause

I have a task to export data from database using data pump. I have successfully exported database using subquery in QUERY clause. But I want to know can we expdp using joins in QUERY clause?
1
vote
1answer
85 views

How to add Subpartition to all partitions

I believe adding sub-partition in Oracle 11g is easy ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS'); I have close to 250 partitions..namely jan07 feb07 ... ...
0
votes
1answer
242 views

Can we export Selected Row with Subquery using data pump

I am trying to export selected rowS from my database using SUBQUERY but I got error. expdp directory=exp_data dumpfile=selected_row.dmp logfile=rows.log tables=sample,super query="where name IN (...
3
votes
2answers
107 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 ...
2
votes
1answer
108 views

permissions for yet another “ORA-06512: table or view does not exist” scenario

See the code below. I'm running the code with user x in schema y. The exact same code works if I run it by user x in schema x, but not with user x in schema y. I get the error ORA-06512: table or ...
0
votes
0answers
114 views

TNS Time out Error ORA-12170

I am trying to connect to a remote Oracle 12c database from a client machine that has 11g oracle drivers for ODBC. I can reach to remote machine through ping but can not Telnet the remote machine on ...
2
votes
1answer
156 views

LGWR is switching frequently in every 3,4,7 minutes. what should i do

I am running Oracle 11g R2 single instance on Linux 6.7. Data guard is also configured. In a database I have 4 groups of redologs, each group has 2 members and each logfile size is 200mb. The ...
5
votes
3answers
5k 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 ...
1
vote
1answer
46 views

special setup to create views in Oracle in default schema

When creating views in Oracle, do you need special setup for a given user if the view and table in which the view is reading from are in two different schemas? The user creating the view has dba ...
1
vote
1answer
89 views

Oracle 11.2.0.3 Service Name Mismatch issue

My Environment During Installation SID lab3 Service_Name lab3.domain.com Changes Some time ago I changed my service_name to just lab3 to match my SID Select instance_name from v$instance; ...
0
votes
1answer
36 views

SQL command not properly ended

Do you see why is appearing this error "SQL command not properly ended" in the select statement below? Select command in oracle: select l_orderkey, sum(l_extendedprice * (1 - ...
1
vote
1answer
172 views

ORA-01031: insufficient privileges connecting to standby

I configured a standby database and it worked for a while, but now I noticed that the redo logs thread on standby is far behind the primary. After researching alert log and trace files, I figured out ...
0
votes
1answer
45 views

Changing own user permissions with Oracle SQL not being the DBA

I have been making changes to a couple of schemas of different environments (they belong to the same application). Sometimes I'll want to copy data from one environment to the other, and sometimes I'...
1
vote
3answers
439 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" *...
0
votes
0answers
264 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.) ...