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
32 views

Oracle - Scan listener and multiple databases

I have a two node RAC database server hosting 8 databases. I have 3 scan listeners running. 2 in node 1 and one in the 2nd node. All the 8 databases are running for different applications. Are these ...
2
votes
2answers
217 views

Number of sessions connected to database

I need to know how many sessions was connected in a period of time for each snapshot. Is this possible in Oracle? Thanks
3
votes
2answers
46 views

Oracle “matches” multiple columns

I am developing a web service where a term of search is given, then I return all the matches from employees table. In MySQL I used to do the following: SELECT * FROM employees WHERE MATCH(name, ...
0
votes
0answers
52 views

Can't delete Oracle Datafiles after DROP TABLESPACE INCLUDING CONTENT AND DATAFILES

Just sharing this as a lesson learned... On Windows, when using DROP TABLESPACE INCLUDING CONTENT AND DATAFILES, the actual files on the harddrive don't get removed. Sometimes (not always) after a ...
-1
votes
2answers
40 views

Copy tables of a schema to a new schema using where 1=2

I am trying to spool the below SQL to copy schema objects to another, but its not working. select 'CREATE TABLE INTDTA.' ||table_name|| '(AS SELECT * FROM all_tables where owner ='ZRTDTA' and 1=2;)' ...
0
votes
1answer
36 views

Failure to parse data returned by server

i login oracle database 12c with sys user with sysdba privileges.But in user section when i want to show the properties of other users by double clicking it, it shows the following error- 'Failure to ...
0
votes
0answers
44 views

ASM Query on Diskgroups

Platform - RHEL. Database - 11.2.0.3. I have a diskgroup name TEST with AU as 1. I need to have the diskgroup with AU as 4 therefore I decided to drop the existing DG and recreate the new one with ...
0
votes
1answer
94 views

ORA-00905: missing keyword

I am trying to run the below Pl/SQL statements on my Oracle DB 11g on Linux box, but getting the error "missing keyword". Please let me know if I miss anything. BEGIN FOR X in (select * from ...
0
votes
1answer
48 views

What is the default username and password to the oracle pre-built virtual machine?

I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box. I logged ...
-2
votes
2answers
66 views

Query taking very long time in 12C environment, but the same query is taking just a few seconds in 10G production environment

We are migrating a database from 10G to 12C and currently we are testing in a dummy 12C production environment. Particularly 3 SQL queries are taking almost 50 min. time to complete and show the ...
0
votes
1answer
24 views

Can I have more than one db_recovery_file_dest?

Can I use more than one db_recovery_file_dest? I have several partitions which could be used, but I think it is not possible to have more than one db_recovery_file_dest.
0
votes
0answers
21 views

How can I get Data from 4D ODBC using Oracle 11 g

I want to recover 4D data in an Oracle database ,I followed the steps below : 4D side : Installation of 4D v14 ODBC 64bit . Creating an ODBC source (successful Connection Test ) Creating and ...
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
38 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
38 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
46 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
45 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
68 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
58 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
48 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
80 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
43 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
61 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
93 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
84 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
200 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
4answers
111 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
45 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
46 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
64 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
46 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
27 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
39 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
40 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 ?