Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...

learn more… | top users | synonyms

2
votes
0answers
25 views

what is causing the reads in this configuration?

i have a setup here where we use a Solaris 10 x86, zpool for oracle12c-redo-log using mirrored disks and mirrored ZIL. logbias="latency", recordsize=128: pool: xxxxxx01_redo00 state: ONLINE scan: ...
2
votes
1answer
2k views

Oracle 10g: Optimizing an update query that uses a subquery

I have the following query within a stored procedure, and I've been trying to improve its performance to no avail. UPDATE TBL_REPORTING REP SET (REP.ISSUER_ID, REP.ISSUER_NAME ) = ( ...
0
votes
1answer
2k views

How to create Oracle RAC database using dbca silent mode with different db_name and db_unique_name

I have a requirement to use dbca (version 11.2.0.3) silent mode to create a RAC database on a 2-node RAC and the db_name and db_unique_name must be different, I tried following dbca command ...
0
votes
1answer
30 views

Replace nested “select with count” with outer join? is this possible?

SELECT c.classid , c.classname , (select count(s.id) from students s where s.classid = c.classid and status = 'failed') as noOfFailed, (select count(s.id) from students s where ...
1
vote
1answer
337 views

Help with Oracle Pipelined function

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works): SELECT oks_c.contract_number, ...
1
vote
0answers
20 views

SQL - Optimize table population with dynamic queries

I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative ...
3
votes
0answers
43 views

Can an active session in oracle be dead?

I have a time consuming query which populates a materialized view. It has been running for 18 hours - which was not expected. SELECT STATUS FROM V$SESSION WHERE SID=194; The above query shows the ...
0
votes
2answers
41 views

Failed to connect to oracle SQL Plus [closed]

I tryed to connect to SQL Plus as the admin "System", I'm sure that the password I wrote was correct but the connection failed. I also tried to connect with Scott/tiger but it failed as well. What can ...
8
votes
1answer
7k views

Duplicate an Oracle database user

We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the ...
0
votes
1answer
55 views

What is the best query to get the latest row version based on time?

I'm wondering what can be the most efficient query to get the latest rows from the following table. CREATE TABLE AGE_DATA ( INSERTED_DAY TIMESTAMP(6), HOSTNAME VARCHAR2(255 CHAR), CONCEPT ...
0
votes
1answer
26 views

Oracle grant select on a table

I want to grant select permission on a specific table to a remote ip address I had done it before in Mysql with this: grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd'; ...
0
votes
1answer
156 views

ORA-12541: TNS: No listener after adding new IPs to machine

We are receiving the ORA-12541 error since we added 2 new IP addresses to the machine. This may or may not be related to the problem at hand, but this is the latest severe change that was made, before ...
1
vote
1answer
103 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
0
votes
3answers
37 views

Oracle RMAN Back-up files in red hat can be read in windows?

We have performed a cold back-up of a database using Oracle RMAN. The back-up ran on a Red Hat Server and then the files were copied to a mounted external hard-drive. Can this back-up files then be ...
0
votes
0answers
16 views

Difference between @multitenant(SINGLE_TABLE) and @multitenant(VPD) in EclipseLink

Despite the fact I searched a lot in the internet and read many articles, I still be unable to get the difference between the Single_Table strategy and the VPD strategy of EclipseLink. At first I ...
0
votes
1answer
118 views

Select * from (every table in an subselect)

I was trying to get an select * from (for column table result) every table_name with the result bellow, how can i achieve this? (SELECT a.table_name FROM all_cons_columns a JOIN all_constraints c ...
2
votes
1answer
47 views

How do I retrieve the loop record in the connect by clause?

I have a employee-->Manager hierarchy and using the following query: SELECT EMP_ID, FIRST_NAME CONNECT_BY_ISCYCLE "CYCLE" FROM MYTABLE CONNECT by NOCYCLE PRIOR MANAGER_EMP_ID = EMP_ID START WITH ...
2
votes
1answer
63 views

With Clause vs View of the same name

In my database, there is a view named VAL_DATES_V which is used in many other views in the same schema. For example, the SQL code in the view SECTOR_V uses VAL_DATES_V. CREATE OR REPLACE VIEW ...
0
votes
1answer
29 views

Using oracle hints

I am trying the following query to list the first 10 rows from fisgen table: SELECT /*+FIRST_ROWS(10)*/ salute, gender, fid FROM fisgen; The hint is not executed. All the rows from the table are ...
0
votes
1answer
27 views

Oracle Text: query scoring with CTXCAT index?

If a text column is indexed with a CTXSYS.CONTEXT index it can return a score showing how well the text matches the query. create index ix on t(text) indextype is ctxsys.context parameters (''); ...
0
votes
2answers
23 views

Single select from template record oracle

Due to lack of terminology in my head, let's say I've got 1 record from users table with these attributes : Name | Surname | Height | Weight Person X 6,2 190 How do I find other ...
1
vote
1answer
176 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
0
votes
1answer
42 views

Displaying output in SQLPlus [closed]

I will open my SQLPlus with CMD in windows. When I write a query it shows 157998 rows as result. I want see the all result in sqlpus window, but it is not showing all the result.
-2
votes
1answer
31 views

How to record sqlplus session activity like “/var/messages” in Unix..? [closed]

something like $ sqlplus '/as sysdba' @spool log1.txt SQL> --Is there anything like the above..?
-1
votes
1answer
166 views

Can't open/select a table created with another user

I created a table with one user and cannot read or select the table after I log in with another user. Database: Oracle Name: TestDatabase User name: sysadmin Another user "user1" was created in ...
0
votes
1answer
58 views

Right way to restart Oracle 12c Server with pluggable databases

I am new to oracle. I installed Oracle 12c on windows with a pluggable database on a test VM. Everything was working fine. After the installation I was able to connect via EM Express on port 5500, via ...
1
vote
1answer
1k views

Fixing wildcard expansion resulting in too many terms error (DRG-51030)

How can I resolve the wildcard_maxterm problem in Oracle 10g? My index creation syntax is: begin ctx_ddl.drop_preference('SUBSTRING_PREF'); end; / begin ...
7
votes
4answers
14k views

Oracle returns an error ORA-01031: insufficient privileges

Oracle returns an error "ORA-01031: insufficient privileges" When I run sqlplus / as sysdba but sqlplus sys/oracle123 as sysdba doesn't, why? Oracle version: Oracle Database 10g Enterprise Edition ...
1
vote
1answer
8k views

Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )

I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue. issue is: when I wanted to integrate OWASP ...
0
votes
1answer
29 views

Removing editioning from an Oracle user

I maintain an Oracle Enterprise 11.2.0.3 database where one user has editioning enabled. I understand that this was an irreversible choice according to the documentation. If I wish to revert that ...
1
vote
1answer
89 views

Type with nested table in oracle

I want to create a type with a nested table. I have a table called assignment_tbl which I want to put a type assignment_status. This assignment_status contains primitive types but also contains a ...
1
vote
1answer
169 views

Connecting to an 11g Standard Database (Local)

I've installed 11g Standard on a VM, it's going to be connected to from other Computers/Users. It installs successfully but I'm not really sure how to create my first database on it (using SQL ...
0
votes
2answers
4k views

Dividing comma separated strings into columns

I have a problem with dividing comma separated values. I have 2 columns with comma separated values, for ex: ID Name 1,2,3 Ab,cd,ef I want the columns divided as: Id Name 1 ab 2 ...
18
votes
11answers
47k views

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
0
votes
1answer
92 views

Oracle 11g XE 64bits installation problem on Windows 7

I had to install Oracle 11g XE on my laptop under Windows 7. So I follow this Tutorial. The installation didn't even fail once, and seem to be OK. But every time i tried to connect to it with SQLPLUS, ...
0
votes
1answer
32 views

Connecting to Oracle Pluggable database using service name

I installed Oracle 12c on windows using all the defaults but choosing to use a pluggable database and to install the sample data. The container database and SID name is ORCL and the pluggable database ...
0
votes
1answer
32 views

Regarding table access

How to revoke access for particular table from an user who have 'select any table' privilege? I want to block access to system tables that have password details such as user$ etc.. DB : Oracle ...
0
votes
1answer
52 views

Continuous replication between Oracle Database and Microsoft Azure SQL [closed]

Background info: Our core ERP has it's data on Oracle. Our own applications are Microsoft SQL based. Almost every application reads data from this Oracle database and usually there is some sort of ...
8
votes
2answers
7k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
0
votes
0answers
36 views

Is this MERGE statement correct?

Question: Is this MERGE statement correct ? emphasis on the ON and AND .. are they rightly placed? MERGE INTO VR_ORDER_LINE USING (SELECT * FROM E14) ON ( VR_LINE.RETAIL_NUMBER = ...
-2
votes
1answer
42 views

Single-row subquery returns more than one row

I try to run the follwoing query but it gives me 'Single-row subquery returns more than one row' error. BEGIN FOR C2 IN (SELECT S.bom_header, S.sap_cage_cd, ...
2
votes
2answers
3k views

Oracle - abort within a “before insert” trigger without throwing an exception

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these ...
0
votes
0answers
24 views

I/O contention on RAID disk [duplicate]

I found all of the Oracle data files including redo, control, system, index and everything are in just one directory. That directory is on Raid5 disks. In this case do I need to do something for ...
2
votes
1answer
251 views

Imposing column level security on a view for different user [duplicate]

Possible Duplicate: Column level security I have a view which can be viewed by multiple users, and I would like to restrict some columns to specific users. e.g I have a view with fields A, ...
2
votes
3answers
1k views

Can Oracle row-level security work with user session credentials?

Can row-level security in Oracle be controlled by a user's session credentials or user context? Here's an example to illustrate what I'm looking for: Our database contains a bunch of sensitive ...
8
votes
1answer
675 views

Column level security

I am in need of a solution to hide specific columns in a table. We have people who need to build reports against this database and specifically some of these tables with confidential information but ...
4
votes
2answers
3k views

Does Oracle support table-level & column-level permissions?

If there is a customer table, and some extra-private/secure information like SSN is there, should I create another table to store such information and lock that table so only certain people can view ...
2
votes
2answers
1k views

Is to possible to forbid access to tables in own schema - Oracle?

Lets assume that we have the following situation: We have an Oracle schema/user: USER_TEST (with the "create session" privilege). SYS as SYSDBA creates couple Tables on schema USER_TEST. Is it ...
0
votes
2answers
551 views

Oracle 10XE: Stop schemas seeing the all_users table?

I've got an Oracle 10g XE Database that I want clients to be able to view remotely via SQL*PLUS or SQL Developer. Each schema has a name which includes the name of the client, so anyone looking at the ...
0
votes
0answers
30 views

IO contention on raid5 happens?

I found all of the Oracle data files including redo, control, system, index and everything are in just one directory. And that directory is on Raid 5 disks. In this case do I need to do something ...