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

0
votes
1answer
29 views

When defining a foreign key in this Oracle example, what is the significance of the CONSTRAINT keyword?

In the code below, The first column I create for the table courses is a column called department_code. I then define fk_courses_department_code and specify that department_code in this table will be a ...
0
votes
0answers
36 views

Oracle rows to columns

I have the following question: I have the following resultset for my query: SELECT ID, quantity, date FROM table: id quantity date 32758669 255150 17.08.2016 32758669 271350 24.08....
0
votes
0answers
33 views

Row Directory in Oracle Block, How does it work?

Sorry for I am just a newbie in the Oracle. I read Oracle Concepts book. And The part I can't understand about the Row Directory in the Oracle Block Overhead is This portion of the data block ...
1
vote
1answer
35 views

what is oracle universal installer in oracle database products

I am new to oracle database products. I am planning to install oracle 11g express edition in my PC,it looks like a standard installer.But in many of the oracle database installation tutorial mentions ...
0
votes
2answers
39 views

Grant permissions to a user to grant select to specific tables in several schemas in Oracle

I administer a small datawarehouse in Oracle. My user dwhmanager was given the SELECT ANY TABLE privilege: GRANT SELECT ANY TABLE TO dwhmanager WITH ADMIN OPTION; The idea was to have the ability to ...
0
votes
1answer
41 views

Oracle DB refresh on 11g using 10g Cold backup

I am a sql dba, I am having only a little knowledge on Oracle. looking for help regarding oracle db restore. I am having an Oracle 10g cold backup, i need to restore this on Oracle 11g setup, can you ...
1
vote
1answer
23 views

Why does the scipt executes only once the sqlscript

the following script is pretty simple, but it works not as expected. I searched for an answer but I am stuck. #!/bin/bash db_con=user/pw@db bname='filename' for f in $(seq 1 10); do sqlplus -S ...
3
votes
2answers
67 views

How to sort the data fetched from horizontal partitioned tables

I have a telco billing software system. In it there are daily logs of users' calls. The logs are horizontally partitioned by date (month). Each partition is stored in a separate database and may be ...
0
votes
1answer
30 views

In Oracle how do I put certain tables or partitions in a different group so the rest of the db can be brought up with OR without them?

I've done this with MS SQL Server in the past where data that is not required for disaster recovery for instance is in a separate file group and file and not cloned to save significant space and ...
1
vote
1answer
52 views

Why can not user set current schema to SYS?

See the example bellow. User is created with dba (but not sysdba) privs. He's allows to access v$session (which is public synonym to view in schema SYS, having different name). When executing alter ...
0
votes
1answer
47 views

A short way to execute a dynamic SQL string

I have a table, and a column with a query in it. And I want to update this table, converting the value to the result of it's execution. This is a well known example of such table actually: ...
1
vote
4answers
78 views

Update data of one table from another with comparing columns have same initial strings

I have two tables: NewData: EName Job Sal smith clerk 2000 allen sales 2000 jones.domain.com Manager 6000 OldData: EMPNO ...
1
vote
1answer
42 views

What does remote database have to do for DB link in Oracle

In local database, it has to create DB LINK through CREATE DATABASE LINK. What about remote database side, what does it have to do? On fixed user link, local database need to know ID and password ...
0
votes
1answer
54 views

How to do 10 G Database recovery without archive logs but available redo logs, ctl files and .dbf files

My client's Oracle 10g database crashed abruptly on Win 2003 NT. The oracle was installed on C drive and redo logs, ctl files and .dbf files were in D: It was unable to backup C: but successfully ...
0
votes
1answer
42 views

ASM Query - Oracle 11gR2

I have two node RAC servers hosting 4 databases running on 11.2.0.3 version in RHEL OS. The underlying LUNs assigned to the ASM is not currently multipathed, meaning the disk sequence would change ...
-2
votes
2answers
35 views

Trying to understand “tns_alias”

What would be tns_alias on this syntax? sqlplus -L user_name/Password@tns_alias @d:\my_bat_file\sql_script.sql
1
vote
2answers
84 views

CMD prompt can't display Unicode characters that are stored in oracle database correctly

I've been meddling with charset/encoding problems of Oracle database, PL/SQL developer and JDBC and these are what I want to achieve: Input Unicode characters from a browser into the database ...
31
votes
3answers
5k views

Why does this query work?

I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c. Why does this query not return an exception? select * from table_a where name in (select name from table_b); From ...
1
vote
1answer
81 views

Select query with views crashes with ORA-03113: end-of-file on communication channel

I've installed an application developed on 11g XE in a 11g Enterprise Edition server (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production) and main SELECT query triggers this: ...
2
votes
1answer
169 views

ORA-01017: invalid username/password;logon denied

There were other similar questions asked, but none that were able to tell me why I can't connect using a C# .NET web site I've stood up. While I know this forum is more for DBAs connecting to ...
0
votes
0answers
33 views

Query Adjustment Issue - going Haywire

I am having difficulty with a query and after a day or researching, etc, I have decided to seek some advice here. My current query runs and looks like the table below: Type Resource Code ...
1
vote
1answer
38 views

Test connection for linked server fails on desktop but not on server [closed]

On SQL Server 2012 I have a linked server to Oracle 12c. On the server a test connection succeeds. Security is set to be made using this security content with an Oracle user and no local logins are ...
0
votes
3answers
101 views

After successfully installation, Is it possible create database without DBCA?

Sorry for I am not familiar with the Oracle. Please help me grow up. :) As the title said . Could someone please tell me the steps how to create a database manually through SQL without using DBCA? ...
-3
votes
1answer
32 views

Oracle takes more time to export csv

Oracle takes more time to export csv,i have tried with UTL_FILE & spool both takes more time,if there are any other way in coding for fastest export?
1
vote
3answers
69 views

Which one is created first? Database or Instance?

Forgive me I just learned Oracle. There exist so many terminologies in the Oracle world. Regarding the concept of database and instance. I already know some differences between them. The Oracle ...
1
vote
2answers
44 views

Block size is for Database or Tablespace?

Forgive me I am just a newbie in the Oracle. I saw there is a db_block_size parameter which can be specified the block size for a database so that the whole database will be read and written data with ...
0
votes
1answer
45 views

Unable to view partition table in a schema

I am using oracle 11g enterprise edition, I have created a schema as "TESTSCHEMA" and in it i have created a partition table as "sales_test". The query for the table, create table sales_test (...
0
votes
2answers
44 views

Messed up output

Whenever I run sql statements like (select * from employees;) to display long outputs, the output is always unaligned, scattered and very difficult for me to read. I just signed up here and I hope ...
1
vote
1answer
31 views

Setting parameters like PCTUSED when creating new tables in ORACLE 12 C

When creating new tables in ORACLE 12 C, do we need to explicitly set parameters like PCTUSED, PCTFREE, INITRANS, MAXTRANS, STORAGE etc. (as shown in example below)? Or will ORACLE automatically set ...
0
votes
0answers
63 views

What are PDE files and how can I use them?

I've got a legacy DB which was given to me as a collection of PDE files. I've googled around and didn't come up with any useful information. Saw this question: Import .pde file on PL/SQL Developer .....
1
vote
1answer
40 views

Why all users excep sys and system can connect to oracle db after database deletion on multi database environment?

I have 2 database on one server. I can connect to the database with normal users but cannot connect with sys and system users and invalid username/password occurs. before that I dropped one of the ...
1
vote
1answer
30 views

Accept use in sqlplus

I want to ask in SQL script for a start and finish date with accept but I get an error. accept v_FechaInicio date format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de inicio ('DD/MM/YYYY-HH24:MI:SS'): ' ...
0
votes
1answer
34 views

Abstracting away filtering behavior from Oracle SQL Function

I have a Oracle SQL function, which returns a bunch of records through various sub-queries. However, there are countless filters that can be applied to these result records. In an application ...
-1
votes
1answer
45 views

Golden Gate Privilege Missing for using Encryption

dblogin userid ogg_user, password AADAAAAAAAAAAAIAKAQIJAGAMFUFKFBGQHXBJIWGECTEIAZHJFYALFFIBGHGJBPFBCPBKEPCRCRHAESJ, AES128, encryptkey securekey1 ERROR: Unable to connect to database using user ...
0
votes
1answer
25 views

SQL Error: Must be GROUPed or used in aggregate function

I have just created a SQL statement in Crystal Reports using the command feature, but I am having issues with displaying the data. The error reads: Attribute FUND_ACCT_REG_VIEW.FUND._ACCT_NO must ...
1
vote
1answer
37 views

How can I use an array variable inside the IN operator for Oracle SQL?

I declare a type TYPE arr_type is TABLE of VARCHAR2(11 BYTE); and then I initialize it: MY_array arr_type := arr_type();. I insert some varchars into it, and then attempt to use it with an IN ...
0
votes
1answer
46 views

Argument out of range error

I have this query: WITH I1 AS ( SELECT * FROM XYZ A WHERE UPPER(A.DES) LIKE '%%' ESCAPE '\' --' AND NVL(A.END_DATE, TO_DATE('9999-12-31', 'YYYY-MM-DD')) >= ...
0
votes
1answer
38 views

exporting some table table from oracle DB to Mysql DB

I need to export some of the tables from Oracle DB to Mysql DB..i dont have any previous experience of export and Import. Could some please tell me how to do that ?
0
votes
1answer
54 views

connect to sqlplus without writing as sysdba [closed]

I want to connect sqlplus sys/test without writing as sysdba. I'm wondering if there is a way to avoid that.
0
votes
1answer
49 views

Oracle sqlldr convert date field with different NLS_DATE_FORMAT

I want to load data file which contains date fields in this format: 10-AUG-16 but, my oracle NLS_LANG is "Simplified Chinese_China.ZH16GBK", if I specify date format in control file like this: dd-...
0
votes
1answer
47 views

Oracle return the first row, not returning the first row

I have this query : select (quarter_1 + quarter_2 + quarter_3 + quarter_4) as total from budget where as_of_dt < to_date('2016-06-28', 'yyyy-MM-dd') order by as_of_dt desc; Which returns bunch ...
1
vote
1answer
62 views

How do I get multiple rows output in one line?

The data is like Jane has items pen pencil eraser. If I write the statement 'select * from item' the output is: Jane pen Jane pencil Jane eraser Jack box Jack wiper I want the output to be like: ...
1
vote
1answer
53 views

SP2-0584: EXIT variable was non-numeric error

I'm using following PLSQL loop to delete a large number of records from my table. However it is giving me an error that loop exit condition is not correct. FOR i IN 1..100 LOOP DELETE FROM ...
1
vote
1answer
50 views

impdp expects wrong file path

Summary: I'm using expdp / impdp to export a database from a test / demo database (Oracle 11.2) and import it into an instance of Oracle Express (11g) on my own machine. The import fails because ...
0
votes
1answer
48 views

How do i connect to remote oracle isql server using ODBC?

I have access to a public oracle iSQL*Plus Release 9.2.0.1.0 server (ex url : 1xx.1xx.1x.200/isqlplus). I need to set up an ODBC Data Source using this isql server. Please help me to setup an ODBC ...
1
vote
0answers
56 views

Getting Error/Exception In GoldenGate Replication

I am planning to configure a simple GoldenGate configuration and want to implement an exception table that will store all the exceptions. I followed two links, neither of which work for me: ...
1
vote
0answers
89 views

Unique constraint violated even though the table is empty

The item_type table is empty, yet on this: INSERT INTO item_type (id, name) VALUES (1, 'PHYSICAL') I get error: Error: ORA-00001: unique constraint (MYDB.SYS_C0013475) violated SQLState: ...
1
vote
0answers
65 views

Can't run procedure on Oracle SQL Developer [closed]

Since yesterday, I have not been unable to run procedure on Oracle SQL Developer. When I click on Run button, progress bar with "Launching" text appears on the right bottom corner of the SQL Developer,...
1
vote
1answer
43 views

How to select all attributes of object type

I am running select query with a function in it. That function returns an object type with 4 attributes and I'd like to select all those attributes. When I try it int this way. It returns just the ...
1
vote
2answers
87 views

How to create database/tablespace/schema and users for quickstart developers on Oracle 12?

Background I need to create a contained development space for a new application. This requires a user account to connect with all permissions, such as create/drop tables and stored procedures. ...