Tagged Questions

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

1
vote
1answer
196 views

One Materialized Views in Two Refresh Groups

I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to ...
4
votes
1answer
25k views

“ORA-03113: end-of-file on communication channel” on startup

I have been reading posts here, on Oracle support, and anywhere else I can find for the last three days and I've given up on this problem... An Oracle database hung. Shutdown of the database sat for ...
1
vote
1answer
98 views

A query executed in a backup of a database is performing better than if executed in the original database

We have two databases with the same data, let's call it Database A which is the original and Database B which is the backup from Database A. A query is performing much better in Database B for example ...
0
votes
2answers
2k views

Delete rows in parent table based on criteria in child table - Oracle

I have a table B with a foreign key to table A. I want to DELETE some rows in table B, and I also want to DELETE their parent rows in table A. However, the delete criteria is based on table B. The ...
1
vote
2answers
3k views

How can I execute a PL/SQL procedure for Oracle via JDBC?

I have a Java webapp with an Oracle DB, and I try to execute this statement at the end of a data migration script: EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'MY_SCHEMA', tabname => ...
1
vote
1answer
197 views

Migrating a tablespace to a different database on the same ASM

How might one go about copying or moving tablespaces between databases that uses the same ASM instances? Might it be possible to cut some corners in RMAN or have ASM remap files belonging to a ...
0
votes
1answer
286 views

Oracle Materialized View Refresh Group - Montly Interval

I have a few Materialized Views that I need to update weekly and monthly. So for the weekly I'm using: execute DBMS_REFRESH.MAKE( name=>'LPMVRefresh_Weekly_1', ...
0
votes
1answer
1k views

Getting multiplication of column values on a joined table based on condition

The title was best I could find to explain my question, I don't think it helps though. Anyways, I have two tables: CREATE TABLE YEARLY_RATES ( RATE_YEAR NUMBER(4,0), RATE NUMBER(6,4) ); ...
0
votes
2answers
4k views

Oracle no privileges on tablespace USERS

I have a brand new Oracle database that is giving the error: ORA-01950: no privileges on tablespace 'USERS' I have done: alter user kainaw quota 100M on 'USERS'; grant unlimited tablespace to ...
0
votes
2answers
94 views

Enabling Edition Based Redefinition failed with ora-33819. Could I query why?

I want to enable Edition Based Redefinition for a user. Executing this command gives an ORA-33819 exception: ALTER USER user ENABLE EDITIONS; I know there's an option to enable it with the ...
0
votes
1answer
124 views

Selecting from a function that returns a sys refcursor or an alternative

I have a query that returns a resultset of three columns, namely SSN,PAID_YEAR and PAID_TOTAL. From this query I can: Create a view and then query it. Create a function and return resultset If I ...
1
vote
1answer
601 views

Database Design for lookup tables

I have a transaction table called questionnaire and a question lookup table called question_codes. questionnaire qst_id - number (primary key) question_type - number (foreign key) ...
0
votes
3answers
4k views

Why does “GRANT SELECT … TO Role;” does not allow members of Role to SELECT?

Please consider the following scenario on a Oracle 11g Database. The user ADMIN performs the following: CREATE USER Alice IDENTIFIED BY pwdalice; GRANT CREATE SESSION TO Alice; CREATE ROLE Viewer ...
-1
votes
1answer
175 views

Restoring Oracle XE database

I had Oracle 10G XE installed on my Windows machine. The machine got corrupted and I will have to install Oracle again. I did not take a backup in the form of SQL queries. How can I use the old ...
1
vote
1answer
58 views

Dropping a schedule whose name contain '.'

I was attempting to create a schedule called MYSCHEMA.MYSCHED in my Oracle database using SQL Developer 4.0 Early Access, and there appears to be a bug resulting in a schedule named MYSCHEMA. to be ...
0
votes
1answer
747 views

Problem using merge into a remote table over dblink

I'm attempting to perform a merge into a remote table, let's say on server A (over a dblink), using a local table on server B. Source and target databases are both on Oracle 10.2.0.3. We already have ...
1
vote
0answers
91 views

Oracle specifying join order hint

Oracle 2 node RAC 11G R2 I have a query (this is a simplified version, but still exhibits the same issue as the larger query) that looks like this: SELECT t1.trip_id, t1.origin, ...
1
vote
1answer
2k views

Oracle, Create table with inline constraint declarations using a different tablespace

I have two questions about inline constraints declarations for Oracle tables: Is it a bad practice? If so, why? How could one declare a different tablespace for the primary key and index like it's ...
3
votes
2answers
690 views

DB2 (LUW) equivalent for Oracle “drop user cascade”

This question is related to creating and initialising test and development databases for an application that has the database running on LUW (linux-unix-windows) On oracle, we have an os user (on ...
1
vote
1answer
116 views

How to -create- performance issues? - oracle [closed]

I want to dedicate some time to learn more about performance and tuning issues. I assign for that a clean DB. I want to know how can I load into it some data and performance-problem queries/DML/DDL? ...
0
votes
1answer
427 views

ORA-7445 Error on 12c

I am installing an Oracle 12.1.0.1 Enterprise Edition on RHEL 6.4 I set it up with ASMM and HugePages (64G of RAM) There may be a correlation between the errors and signing in to em express (not ...
1
vote
1answer
335 views

Connecting to Oracle Database with ODBC/Python

We've recently installed a new piece of software that uses an Oracle Database to store the data. My goal is to connect to that database and pull the data from the back end to do analysis on. I'm ...
0
votes
1answer
1k views

Target database memory exceed available shared memory

I am trying to install Oracle 12c on a RHEL 6.4 system with 64GB of memory. When I try to enable automatic memory to 48GB, I get the error: What do I need to change to allow Oracle to use more than ...
0
votes
0answers
30 views

Reliability of blobs in MySQL

I'm in the process of migrating an application working with Oracle to MySQL. That application stores data in the form of blobs, which can be pretty big. The Oracle production database is 60 Gb large ...
0
votes
0answers
797 views

Oracle merge query vs correlated update

I have a query which updates a table with data from another table. The current query is written as a correlated update with an exists clause to remove unnecessary rows. The problem is since both of ...
1
vote
1answer
1k views

Duplicate database for standby returns error ORA-19625: error identifying file /home/oracle/stdbyctrl.ctl

I'm working on creating duplication scripts for standby purposes. and it's constantly failing even though the backups are located in the exact same folder. DB structure is identical and spfile is ...
1
vote
0answers
159 views

How to store object with property of generic type in DB

In my business domain there exists an entity parameter. Beside other properties which are identical for each parameter, they have values. These values can be of different data types. There might be ...
0
votes
3answers
449 views

Getting the employee history of Sales department from HR database in Oracle

I am currently using the 'hr' database with Oracle 10g. You can view the schema diagram over here: http://docs.oracle.com/cd/B13789_01/server.101/b10771/diagrams001.htm#sthref63 What I would like is ...
0
votes
2answers
200 views

How force specific table to not logging (for all DML)?

We using Oracle 9i . I'm still learn about admin Oracle (I'm DBA but other vendor). We implement manually at our databases a trigger over LOGON and LOGOFF to save some information about our users ...
1
vote
2answers
337 views

How to call Oracle's stored procedure from DB2 stored procedure?

I am new to writing stored procedures and wondering if it's possible to call a Oralce' stored procedure from DB2 stored procedure. I have a scenario where a trigger on a DB2 table will invoke a DB2 ...
0
votes
2answers
201 views

CASE Statement - 1st case is being ignored

I have a table (usr_t_user_reg) in which I register all the users of the database. Now I want to create a view (usr_v_user_not_reg) which shows me all the users that I haven't registered yet or users ...
0
votes
2answers
244 views

Oracle - Clause where in case when

all! I want to know how wrote this code of SQL, I already trying but i get message error. I wrote two way different this SQL. See you below SELECT * FROM tabela WHERE coluna IN (CASE WHEN 1 = ...
0
votes
3answers
74 views

Oracle Data Guard - redo logs not in chronological order?

The disadvantages section of the Wikipedia entry for Oracle Data Guard says: If the network link connecting primary and standby is over-subscribed, the redo logs are not applied in chronological ...
3
votes
2answers
502 views

SQL*Plus default output format

I have noticed that using the same SQL*Plus client to connect to different Oracle database result in different outputs for the same SQL query. Database 1: C:\Oracle\instantclient_10_2>sqlplus ...
0
votes
1answer
112 views

Install and Configure Oracle 11g using c# program [closed]

I need to create a windows application program using c# which will allow me to install oracle client 11g and also configure ODBC automatically with just one click, as my customers have limitations in ...
0
votes
1answer
2k views

Reverse connect by prior level value for arbitrarily-deep hierarchy

Background Using a menu hierarchy to drive a login process for users. Users have the ability to set their preferred menu item. When they log in, if they have a preferred menu item set, the system ...
1
vote
0answers
128 views

Oracle 10g and Active Directory not behaving as expeted

I'm trying to connect to our Oracle 10g 64bit database running on a Windows 2003 server authenticating with an Active Directory user. I found this weird results on our Development environment which I ...
0
votes
1answer
535 views

Centralize Oracle RMAN scripts in one server

I want to centralize all my Oracle's DB backup shell scripts in one server, and all of them are in different servers, architectures and DB versions. For this purpose I'm going to use the same server ...
1
vote
1answer
940 views

Why does Oracle SQL*Plus consider two queries executed together on the command line as a syntax error?

Consider: SQL> select sysdate from dual; SYSDATE ----------- 22-Aug-13 0 SQL> select sysdate from dual; select sysdate from dual; select sysdate from dual; select sysdate from dual ...
2
votes
1answer
246 views

Recover encrypted Oracle database

A client fired their DBA. He used OWM to add a wallet to their Oracle database and secure a few necessary columns of tables that they need. I can see the walled using: orapki wallet display -wallet ...
0
votes
2answers
570 views

Oracle intermittent performance problem

I have some complex queries that are usually fast (< 1 sec), but occasionally slow (> 10 sec, even minutes). How do I track this down or identify what condition is causing it? One theory was ...
1
vote
1answer
249 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, ...
0
votes
1answer
56 views

Connecting to Oracle with encrypted pwd

I am reading my userid , pwd from a file with 400 permissions to connect to the external oracle application. Now my requirement is that i should not use the plain text pwd present in the file . I ...
0
votes
1answer
359 views

Oracle 11G Startup failed after altering the number of system processes

When I alter the number of system processes to 100000 and restart the database, I get the error below: ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semids_per_proc ...
1
vote
1answer
1k views

Creating a USER using the imp command [duplicate]

I was told I can use exp to export a certain USER, then DROP the user and with the imp command I can recreate that USER with all of its tables. Is it true? How do I do it? I already have an ADMIN ...
6
votes
1answer
89 views

Why are some aggregates treated differently in the RHS of a rule in the model clause?

With my limited understanding of the model clause, I'd expected the following three queries to return the same results because there are no nulls in the data, and the functions are equivalent: select ...
0
votes
0answers
222 views

Oracle prevent CPU usage per process/session

I have an Oracle instance to which several applications from different machines connect (http servers). Normally Oracle CPU usage is ~5-10% even on high server loads. But from time to time, one of ...
1
vote
1answer
300 views

Any advantage of creating table in temporary tablespace - Oracle

My on-site DBA told me to create the particular table on temporary tablespace.Is there any advantage of such action?
0
votes
2answers
238 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
2
votes
1answer
662 views

Is there a way to explicitely have a not-null column in a view

If I create a view in Oracle, the column definition's not null characteristic is inherited from the base table: create table tq84_t ( col_1 number, col_2 varchar2(15) not null ); create view ...