All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

1
vote
2answers
72 views

Single sql query to get the transpose of a matrix

I am facing a problem in writing a query for this question.Consider two random 3x3 (n=3) matrices A and B,having the following schema: A(i,j,val) and B(i,j,val) where i int:Row Index j int:Coloumn ...
1
vote
2answers
347 views

missing files, installing Oracle DB 12c on Oracle Linux 7.2

when I try to Install the Database I get this error message: Source location is incomplete. Make sure you have downloaded and extracted all the relevant archives. I did try this twice with the same ...
1
vote
1answer
43 views

Dataguard parameter changed after DB bounce

I have a production environment of Oracle 11g R2 patchset 4 primary database configured with 2 standby machines. Primary database unique_name is DBIDI 1st standby database unique_name is DBIFI 2nd ...
2
votes
5answers
128 views

MIN(column) with subquery

How do I query 3 tables (STUDENTS, MODULES, MARKS) such that I return the module name and the lowest mark score for student John Doe? SQL> DESC STUDENTS; Name Null? Type ...
1
vote
1answer
26 views

Oracle XE/SQLPlus query display more items

Is there a setting to allow Oracle XE/SQLPlus to display more items before beginning a new header? For example: First Name SURNAME Subject MARK ------------...
0
votes
0answers
51 views

Sql developer how to copy to another tablespace [duplicate]

When copying database using SQL Developer it complaints that the source tablespace does not exist on destination database. How can I copy schema and data from Database A tablespace t1 into Database B ...
0
votes
1answer
38 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 ------------------------ -------- -------------...
1
vote
0answers
65 views

Search for Arabic text ignoring diacritics, alef hamza differences, and kashida in SQL and oracle

Well, I have a table with an Arabic column which may contains variant forms of the same word in multiple rows, for example the word "أسمى" might be in the following forms: 1- with diacritics: "...
1
vote
0answers
61 views

Will I have to restore controlfile in RMAN level 1 backupset before recover database?

For example, I have a level 0 and level 1 RMAN backupset files like inr00_0413_* inr01_0413_1_* Is it necessary to do this when recover from RMAN level 1 backup: restore controlfile from ...
0
votes
2answers
53 views

Massive deviation of tablespace max_size compared to bytes used by segments on that tablespace

I just checked the size of our tablespaces, because the admins said that they've made them bigger after we've had a full tablespace yesterday and I've discovered something really strange. I hope ...
-1
votes
1answer
67 views

Need help with this policy function: Shows compilation errors [closed]

Create or Replace FUNCTION Hello_Fun ( P_SCHEMA IN varchar2, P_OBJECT IN varchar2) Return varchar2 IS V_Hello Varchar2(400); BEGIN If User = 'Admin' then V_Hello := ''; Else V_Hello := 'cus_id in(...
2
votes
2answers
108 views

Oracle: Why is DBW terminating my Instance?

I was performing routine administrative tasks on an Oracle instance and I was not able to connect to it. I proceeded to do as follows to reach a diagnosis: I checked for Oracle processes in memory ...
0
votes
1answer
145 views

Connect to Oracle RAC from SQL Developer?

We have set up RAC database on server. We can connect to database from sqlplus on the server using SCAN address. However, if I try to connect to RAC using SQL developer from client machine using SCAN ...
0
votes
1answer
48 views

How to recover auto committed transaction in Oracle with normal privilage

I have updated the table & need to rollback that changes. I have tried Rollback work command but data still not rollback. I have also tried Flashback command but it is not working because "row ...
0
votes
1answer
55 views

Oracle: Can a tablespace stay in backup mode forever?

Recently I discovered in a legacy Oracle 8.1.7.3.0 database that the tablespace holding de AUD$ table has been in backup mode for six months. Dunno who issued the begin backup command on it six moths ...
-3
votes
1answer
46 views

Why different connections in database.? [closed]

I know we can use a single connection for different databases but why an option of multiple connection is provided, in other words, when is the correct situation to use multiple connections when we ...
0
votes
1answer
119 views

Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

So lets say 100, 200, 400, 600 are company_ids and there are 6 administrators who each have their company_ids. The owner of the table User_Info is Admin who is also the owner of other tables and can ...
0
votes
1answer
50 views

PUBLIC privilege potential security threat - Oracle database

Recently, audit findings at my site found that PUBLIC being by default assigned to all the schemas that are created is a big risk since when a new user is created and even if we were to assign only ...
0
votes
1answer
43 views

INSERT and UPDATE in Trigger

I have created a trigger so I could insert every row in a backup table CREATE OR REPLACE TRIGGER xyz AFTER INSERT ON table_1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO table_2 (...
0
votes
0answers
24 views

Why does Oracle text catsearch function need to follow with > 0?

Why does Oracle Text catsearch function need to follow with "> 0" ? Even I use > 10000, > 100 nothing different. Example: CATSEARCH(PRODUCTNAME, 'NOTEBOOK A4','') = 10000000000000000000000 CATSEARCH(...
2
votes
1answer
82 views

Oracle Min - ORA-00937 not a single-group group function

I try to find companies whose fare has minimum fare of destination which more than one companies use that destination. My command; SQL> select tickets.bus_id bus, tickets.fare fare, route....
1
vote
1answer
65 views

Oracle: Query gives different answer when running from procedure

I guess my problem is somehow related with grant permissions. I have an "ADMIN" user, I grant it "dba" permissions like this: grant dba to admin Now in the "ADMIN" schema I have 2 tables: The ...
1
vote
1answer
66 views

Oracle 11gR2 permit NOLOGGING but ensure non-corrupt backups

I'd like to stand up a database which offers a single tablespace for NOLOGGING operations (all other perm tablespaces set with FORCE_LOGGING=YES). A conventional weekend full-backup + daily ...
2
votes
2answers
44 views

Is an Oracle 10g instance dependent on the windows account authentication?

I have created an Oracle 10g (10.2.0.4) instance on a Windows 2003 Server with a domain user xxx having domain administrator level privileges. Now I wish to change xxx's password. Will it impact the ...
1
vote
1answer
63 views

SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints

I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is: SQL Error: ORA-12991: column is reference ...
1
vote
2answers
70 views

Read from SQL Server into Oracle

Is there a way for Oracle to read from SQL Server? We are basically trying to read from a SQL Server table and update the information in Oracle. We used to have a database link in Oracle that takes ...
1
vote
2answers
83 views

Troubleshooting / traceroute within Oracle Instant Client?

I am troubleshooting a freshly reimaged backup server Win2000 / Oracle 10.1.0.5.0. Here's what I did: Installed Oracle 10g by copying Basic Instant Client package and ODBC package into same ...
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 ...
0
votes
1answer
85 views

How to convert decimal number to IP address?

I need a function or query to convert a decimal number to ann IP address. Presently my ip_address column looks like this: select ip_address from test_ip; ip_address 173015430 ...
1
vote
1answer
29 views

finding out the metadata and joins

Suppose I have a database with a large number of tables. Is there any command that I can type in PL/SQL which display only the field names of a given table? Given any table is there any command ...
1
vote
2answers
68 views

Delete rows from a table with inner join conditions

I have a query that selects data from a table based on some inner joins: select * from table1 p inner join table2 e on e.col1='YU' and e.username=p.username inner join table3 d on p.col2=d.col3 and d....
0
votes
1answer
64 views

How to grant privileges to use DBMS_OUTPUT in Oracle?

SET SERVEROUTPUT ON, DBMS_OUTPUT work perfect in my user, System or DBA. But when i create new user, it's not work! How to grant privileges to use this?
0
votes
1answer
43 views

SQL: Pivot + Group by in one go

I wish to pivot INFO2 into 2 columns. INFO3 data should adjusted accordingly after the PIVOT. The challenge is I also have a date column, which I want to take the MAX of. The MAX(Date) should also be ...
0
votes
2answers
70 views

Can I install Oracle 12c and NOT use pluggable databases?

I've just installed Oracle 12c. I did not select any of the fancy new options, I just wanted an Oracle instance. I'm fairly new to Oracle but I was used to 11.2.0.4. Every command I try now fails ...
0
votes
2answers
68 views

List expiration dates of Oracle backups

Is it too much to ask Oracle to provide expiration dates with a list of backups? I am not looking for expired backups. I am looking for a list of expiration dates for each backup.
0
votes
1answer
111 views

ORA-00942: table or view does not exist- Can it be solved by granting privileges to user?

I am creating a Java application that connects to an Oracle Database. In the application I connect to the DB as User A, which works as intended. I then try to access tables in Schema 1 and Schema 2....
2
votes
1answer
75 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
74 views

Trigger to keep account unlock history

How can create trigger that will insert username and date to a table,when oracle user account unlocked to keep account lock history
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') ...
0
votes
2answers
71 views

How to get past account lock information in oracle database?

Is it possible to query the history of previously locked/unlocked Oracle database user accounts? I've tried dba_user but there is no option to get history.
0
votes
1answer
58 views

How to select sub query in pl/sql? [closed]

I want to select subquery in pl/sql by count where UPI debit card and VISA debit cards as screenshot below: Please helps to check script code as below : for r in (select BRANCHCODE,BRANCHNAME,cntupi ...
0
votes
1answer
31 views

How to download or place a copy of original tables with locked-permissions via remote connection in Oracle SQL Developer?

N00b question here: I'm still learning Oracle SQL Developer. Although I understand how to query databases/tables remotely, I'm unable to manipulate the data due to permissions-basically I would like ...
1
vote
1answer
123 views

Gap resolution of Logical Standby Database via Archive Log Backups

Due to reasons outside the scope of this question, we had to flashback our Logical Standby Database (at the VM level) to the previous day. So, now i'm trying to resolve gaps in missing archivelogs ...
0
votes
1answer
53 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 ...
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 ...
1
vote
1answer
74 views

Why can't I SPOOL an EXPLAIN PLAN? [closed]

Why can't I spool the explain plan to a txt file? SPOOL EXPLAIN_PLAN_1.txt set heading off set echo off set pages 999 set long 90000 select plan_table_output from table(dbms_xplan.display('...
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 ...
0
votes
1answer
49 views

Why is installing ORACLE ODBC client a complex task? [closed]

I'm following the ODBC driver instructions in the link below. I think it would help me deal with issues if I understood why this has to be so complex. Cannot be that ORACLE would not make it easier if ...
-5
votes
1answer
35 views

how to data dictionary? [closed]

How to data dictionary owner and table name. Additionally, the output should add the comments on the rows. Input: owner and table name. Output: columns for Name, Null?, Type, Comments focusing on a ...
0
votes
1answer
30 views

Oracle : Is my DISTINCT function working properly?

I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. ...