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

learn more… | top users | synonyms

1
vote
1answer
48 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
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
54 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
54 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
168 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
82 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
204 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 (...
2
votes
1answer
105 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 ...
3
votes
2answers
105 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 ...
0
votes
0answers
99 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
154 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 ...
1
vote
1answer
44 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 ...
2
votes
1answer
96 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 ...
1
vote
1answer
78 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
33 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 - ...
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'...
0
votes
0answers
193 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.) ...
0
votes
1answer
29 views

Creating report based on the sql statement

I would like my report to display something image like as below so can i know is it how my sql for table has to be when creating it create table attendance ( Student_ID VARCHAR2 (15) ...
1
vote
3answers
350 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" *...
3
votes
1answer
101 views

Global command to destroy all disconnected data files in Oracle 11g

Is there a global command to delete/purge all disconnected .bdf files in an Oracle 11g R2 database? Here is the scenario: Every user-defined user and user-defined tablespace was deleted from an ...
0
votes
1answer
99 views

How can I launch SQL Developer?

I recently installed Oracle 11g R2 on a Windows 8.1 machine, but for some reason SQL Developer does not seem to launch easily. What specific steps do I need to take in order to launch a working SQL ...
2
votes
1answer
71 views

Can I use an RMAN Backup of DB1 to replace DB2

I have a backup of an Oracle 11g database on Linux, that is a version that is 'prescrub' needed by other users. This is for an ACTIVE database that is now scrubbed, so I do not want to use it as the '...
1
vote
1answer
311 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.)...
1
vote
0answers
126 views

Sync oracle DBs using archive log when not connected to each other

I'm trying to synchronize 2 dbs which are not connected to each other, and located in a separated networks. The 'copy db' (aka: standby) can be read only, but still should be active. My intuition (...
0
votes
1answer
48 views

Using exception with raise_application_error

I'm developing with Oracle 11g. How can I use a named exception in RAISE_APPLICATION_ERROR like this: DECLARE my_fault EXCEPTION; PRAGMA EXCEPTION_INIT (my_fault, -20000); BEGIN ...
2
votes
1answer
93 views

aggregate column of nvarchar2 and obtain result in nvarchar2 too

I need to create a view with an aggregate column of nvarchar2(4000) in Oracle 11g. What I have: Table1 with every column of type nvarchar2(4000): Col_Name txt_value X a X ...
1
vote
1answer
40 views

Strategy To Remove Old Records

We are running Oracle 11.2.0.4.0. It is a transactional database that has been online since 2011. We have been tasked to clean up old records. The table structure is (parent->child): A->B-C->D->E ...
0
votes
1answer
118 views

ORA-00904 error while querying the Oracle database table

I am newbie with oracle DB and I need to List the Number of departments per region in the region table. This is my SQL statement: select r.region_id, count(d.department_id) "Number of ...
2
votes
1answer
79 views

Oracle - Connect Via SqlPlus

I have a local installation of Oracle. I want to add the XMLDB package to it. As step 1, I need to connect to it via sqlplus. And I am not able to. The installation has a database called Pm. Here ...
1
vote
1answer
57 views

Nested, inline pivot table (oracle)

I'm trying to go from a table like this: ID | NAME | METADATA ------------------------------- 1 | test | '{"a":"x","b":"y"}' where the metadata column is a JSON string. What I'd like to end up ...
0
votes
0answers
72 views

Cannot create user using administrator credentials in Oracle

I am trying to get some hands-on experience with ORACLE and I installed Oracle Database 11g Express Edition on my PC at home. I logged into the 'Application Express' by entering TEST in the Workspace ...
0
votes
1answer
60 views

Pointing DB to new Oracle home in primary and standby oracle database

I needed to apply a security patch to production database. To minimize the outage time, I cloned the oracle home and applied the patch to this newly created oracle home. With production DB there is a ...
1
vote
2answers
56 views

How do I change all Oracle profiles PASSWORD_LIFE_TIME to UNLIMITED?

This query: select distinct profile from dba_profiles; Returns 3 rows: default default1 default2. I want to change the PASSWORD_LIFE_TIME for all these profiles to UNLIMITED with a single query ...
1
vote
2answers
86 views

Updating duplicate records with different data

I have a migration specific issue. Data from a large table has been migrated into another table, but data for one destination column is missing due to certain issues (that are not specific to this ...
0
votes
1answer
133 views

Can Oracle 11g Express Edition Work on Parallels

I am using a Mac and need to use Oracle 11g. I have Parallels installed with Windows 8.1 on it. Do I need to download anything else or just download Oracle 11g Express Edition and run it?
1
vote
1answer
155 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
0answers
39 views

When the query was last executed?

Is it possible to find which queries were run a fortnight ago, and the time they were executed in Oracle 11g? (answers for MySQL also welcome) I found this when I searched: How to know when a query ...
-2
votes
1answer
59 views

plsql block not executing

I am unable to execute this block. it just compiles. what statement I have togive to see output. where I am doing error ? I clicked on output button also
0
votes
1answer
116 views

UTL_TCP how to monitor number of connections?

I have a leak somewhere in the number of UTL_SMTP connections being made and closed. There is an underlying limit of 16 from its reliance on UTL_TCP. I'd like to be able to monitor the connection ...
1
vote
2answers
63 views

Checkpoint during Backup

Can database process make automatic checkpoint when I'm in process of copying files during ALTER DATABASE BEGIN BACKUP; and ALTER DATABASE END BACKUP; You can assume that process of copying ...
1
vote
1answer
70 views

Making data fetching faster on VIEW

I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called . Below is the query to fetch data from VIEW ,this ...
0
votes
1answer
123 views

How can sqlplus knows where ORACLE_SID point?

As I understand, SID is unique value to identify an Oracle database instance. And I can set default database instance sqlplus use by changing ORACLE_SID environment variable. But how can sqlplus ...
0
votes
0answers
60 views

How does Toad detect installed client?

I installed Oracle client and Toad and have worked fine. But When I install Oracle server also in the same system. The server and the client seem to conflict on the path something. I decided to ...
0
votes
2answers
63 views

How to transpose rows to column in datatabse [duplicate]

I have one table which contain 3 column,Col_ID, Col_Group, Col_Price for this my column is like below Col_ID Col_Group Col_Price 1 AA 0 1 BB 2000 1 ...
4
votes
1answer
94 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 ...
0
votes
0answers
80 views

Linked Server to Oracle seems to be slow

we have a linked server between a SQL Server 2008 R2 instance and an Oracle 11.1.0.7 instance using OraOLEDB.Oracle Provider. The enabled options for provider are: Dynamic parameter Nested queries ...
0
votes
0answers
61 views

How to concatenate using cast(collect()) in Oracle DB?

My current query: select distinct mb.BP_TYPE,mb.ABS,mb.BLD_SEQ_CODE, mb.BPCODE||'~'||(select LISTAGG(mbp.PROCEDURE,' ') within group(order by mbp.LINE,mbp.SLN1,mbp.SLN2) from MIS_BS_BB_PROC mbp ...
6
votes
2answers
202 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 ...
3
votes
1answer
42 views

How to convert following mysql query to oracle?

I have a mysql query like following, Column time contains date in long format. SELECT FROM_UNIXTIME(CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i') AS Time, SUM(CreatedInstanceCount) AS ...