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

0
votes
0answers
188 views

After RAM upgrade on 32-bit Oracle server, ORA-04031: unable to allocate x bytes of shared memory

We are in the process of upgrading our Oracle database server at work, from SUSE Linux Enterprise Server 10.4 / Oracle 10g / 4GB RAM (Intel 32-bit) to Scientific Linux 6.4 / Oracle 12c / 8GB RAM ...
0
votes
3answers
431 views

Oracle won't start

While it was working fine, I had to stop the server once. When trying to start Oracle using a script we have, I got the following error: SQL> Connected to an idle instance. SQL> ORA-01081: ...
5
votes
6answers
512 views

Is Oracle free to download?

I am a Java developer and currently I use PostgreSQL as the RDBMS. However, I would also like to learn Oracle. So can I download a latest version of Oracle (11g I guess) for free? I found this link: ...
3
votes
1answer
4k views

How to find the schema name of a data pump dmp file?

I have been given a DMP data pump export file to import into my local Oracle instance. I've tried running this command line: impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp ...
1
vote
0answers
170 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
1answer
121 views

retrieve data based on a condition

I have 2 tables, TA and TB: TA -------- id name 1 a 2 b 3 c 4 d 5 e 6 f TB -------- id p_id name 1 1 a 2 1 b 3 1 c 4 2 a 5 2 b 6 2 d I need : if ...
2
votes
2answers
645 views

Error on Oracle calling external procedures

I have a C library which should be called from a pl/sql function so I make a .so library and after that I create a pl/sql function to call it. the C source(libcprog.c) is like this: int exec_hmmftg ...
1
vote
1answer
669 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) ...
-2
votes
1answer
764 views

Where can I download the Oracle Fusion Middleware Infrastructure Software? [closed]

I understand that since version 12c the Repository Creation Utility for Oracle Database 12c (RCU) is part of the Oracle Fusion Middleware Infrastructure Software. This brings me to the question: ...
23
votes
3answers
27k views

What is the difference between sys and system accounts in Oracle databases?

There are two ways to connect to Oracle as a administrator using sqlplus: sqlplus sys as sysdba sqlplus system/manager These accounts should be uses for different purposes, I suppose. Which tasks ...
3
votes
2answers
488 views

How make convincing case to DBA to use Oracle external tables for 500 MB to 5 GB files?

In our data feeds, we have a stack of XML files as well as numerous flat files to put into an Oracle11g database. We flatten the XML into delimited files, and load the whole set using SQLLoader. I ...
0
votes
2answers
263 views

Example optimisic offline lock in SQL/PL?

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

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

I am getting this error as a result of performing Insert and Select operation which involves calling of number of pl/sql procedures and functions. I am not getting exact reason. I tried optimizing ...
0
votes
2answers
99 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 ...
3
votes
5answers
1k views

Modifying table structure within a transaction?

In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction. We have a tool (written in Java) that should modify a schema by: adding ...
3
votes
2answers
20k views

Expired archived logs not deleted from RMAN catalogue

If I issue a LIST EXPIRED command in RMAN, I see a list of files with expired status, that is, they exist in the catalogue, but not on the disk (verified by ls). After issuing CROSSCHECK and DELETE ...
1
vote
1answer
201 views

Customizing nls_sort and nls_comp in Oracle

There is different letters for 'KAF' (ک , ك) and 'YA' (ی , ي) in Persian with different unicode codes and same spell. I am using these commands to set Persian sort and comparing in my project ALTER ...
-3
votes
1answer
645 views

How to retrieve a query from 3000 tables in SQL? [closed]

I have plan to create 3000 tables for my database. But I am afraid how will I retrieve some data from those many tables. Eg: Select * from table1, table2, table3.... table3000 where ...
4
votes
3answers
5k views

Import Oracle full dump file to fresh Oracle installation

I am importing a Oracle dump fill created on a AIX Oracle 9.2 installation with a Full Export option to a Windows Oracle 10.2.0.4 on Windows 2008 R2 x64. I want to import the file to a clean ...
0
votes
1answer
116 views

Tablespaces in oracle

I understand that tablespace is a logical collection of tables and database objects. It could be made up of multiple data files (which is the physical collection of objects). Apart from this, what is ...
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 ...
1
vote
3answers
5k views

Oracle empty string converts to null

When doing an insert, an empty string is converted to null: insert into test (f) values (''); Now, there is a row with f containing a null. But, when I query the table, I cannot use '': select * ...
0
votes
0answers
26 views

Create a schema like an existing schema [duplicate]

I have a existing schema in my database called SCOTT, how can I create a new schema with exactly the same privileges which SCOTT schema is having? Is there any create like or some command to create ...
0
votes
0answers
1k views

Not able to login in Oracle 11g console using sys/system@SID as sysdba

I am not able to login into Oracle 11g console using administrative account, when I tried it gives me error of wrong username and password. Then I tried from SQL plus, it got logged in without any ...
2
votes
1answer
2k views

Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working

I have an install of Sql Server 2008 x64, installed on two boxes. One is a Win 7 x64 workstation (Sql Server 2008 x64 SP2 developer edition), the other is a Windows Server 2003 x64 (Sql Server 2008 ...
0
votes
1answer
62 views

Is it possible to dynamically retrieve the number of columns in a view in Oracle?

As the title says, is it possible to retrieve the number of columns in a view dynamically? I'm learning Oracle 11gR2 and I'm checking out the V$ views. One of the things I want to do is output them ...
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 ...
0
votes
1answer
313 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', ...
-1
votes
1answer
182 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 ...
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) ); ...
1
vote
1answer
8k views

Oracle: what permission is necessary to “grant create table” to another user?

What permission should A have in order to grant B permission to create a table? logged in as user A, I execute grant create table to B; and receive the following error: SQL Error: ORA-01031: ...
0
votes
1answer
133 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 ...
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
vote
1answer
141 views

How do I get value from parent row

I have two tables. One contains a hierarchical structure of topics and another table that contains optional configuration of that topic, that controls it's visibility in several contexts (one column ...
1
vote
1answer
63 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
801 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 ...
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 ...
1
vote
0answers
94 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 ...
0
votes
1answer
439 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 ...
2
votes
4answers
372 views

Oracle hint failure

When I run the PL/SQL block after the CREATE statement, I receive the duplicate key error I would expect if I did not add the hint. Any ideas to why the hint does not work? CREATE TABLE Sandbox.Clue ...
1
vote
1answer
117 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
608 views

Oracle 11g http listener configuration for PL/SQL server pages

Could please someone point me how to configure oracle db to be able to display PL/SQL Server pages. I have successfully created and mapped dad with the DBMS_EPG. Here is the listener: # listener.ora ...
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
31 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
929 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 ...
0
votes
3answers
480 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
211 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 ...
1
vote
2answers
365 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
1answer
3k views

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

I need to figure out a way to embed an Oracle PL/SQL sub-procedure call in a SELECT statement, within another procedure in the same package. I am using SQLDeveloper 3.0 with an Oracle 11g database. ...