Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Oracle Database 11g (not 11g R2). Please also tag oracle for search purposes.

0
votes
0answers
12 views

how to recover database if redo logs are deleted and I have no archives

I'm working on oracle 11g database, I accidentally removed all redo logs and it happens that I don't have archive logs, I've tried to recover the database using the steps in this link https://asktom....
1
vote
1answer
45 views

Oracle 11g XE to 18c XE - What is the difference? Is 18c can be used like 11g XE?

I have worked on Oracle 11g XE about two months ago. After that I de installed them about a week ago. Now, I am curious to learn a little bit more about databases and I tried to installed Oracle 11g ...
1
vote
0answers
13 views

Why would select statement take 80 seconds but CTAS taking a long time [on hold]

I have select statement that is taking about 80 seconds to execute but when I try to create a table using the same statement, the same query runs for more than 12 hours until I kill it. I have ...
0
votes
1answer
27 views

Oracle 11g: increasing buffer cache with AMM

On a 2-node RAC we have enabled Automatic Memory Management. The advisor views indicate that increasing the buffer cache by 4 GB will half the physical reads, and we have 4 GB to give. How must we ...
0
votes
1answer
39 views

Unable to connect to oracle database. Getting ORA12505 error

I am newbie to ORCALE I am trying to connect to oracle server and i am getting this error. This is was working fine before but now i am facing this issue. Need help An error was encountered ...
0
votes
0answers
32 views

Oracle IMPDP not importing rows

I am trying to import one table from a full DB export but the table I want will not import the rows. If I try another table then the rows import fine! Using Oracle 11.2.0.4 in Windows 2008 R2 The ...
0
votes
0answers
35 views

TNS-01153 : Failed to Process the String

I changed IP Server address which installed Oracle 11g on windows server. But my listener service has not worked so I can't access from the remote. Below are my listener.ora # listener.ora Network ...
0
votes
2answers
46 views

How to make a backup of an Oracle database that does not mess up the regular backup cycle?

Goal I want to move a database from server A to server B. Both server have the same technology so one way is to shutdown the source database, copy data files,control file, redo logs, parameter file ...
1
vote
1answer
134 views

How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0

I am running following command to dump my database ./expdp username/password@//host:port/database directory=/home/ubuntu/oracle/instantclient_12_2 dumpfile=dumpfilename.dmp content=METADATA_ONLY and ...
0
votes
1answer
31 views

ORA-24155 - Why am I getting this error when creating a chain rule

When trying to create a new chain rule, I receive ORA-24155 stating that the the rule I'm trying to create is not in the ruleset which doesn't make sense. I stop and drop all jobs/chains and then ...
0
votes
0answers
47 views

Oracle to SQL Server Conversion help: sys_connect_by_path, listagg, regexp

I've been tasked with converting a process from Oracle to Sql Server 2012. I've converted most of the query over to SQL Server but am having a hard time translating over these queries to SQL Server ...
0
votes
0answers
39 views

Creating a trigger on a column either deleting row or updating row on update, Oracle

I haven't had any experience with triggers, however I'm trying to make a trigger, but i'm making a change in our database setup so that we will follow GDBR regulations. For this I'm required to make ...
0
votes
1answer
30 views

How to get a selection from a join, where there is no result in one of the tables

I am working in an oracle database and looking to get some queries to identify any changes of address from a customer. I am creating a report from the results in a daily batch process. I will simplify ...
1
vote
1answer
49 views

Creating a table space sql developer

I asked this question over at https://stackoverflow.com/questions/52068320/creating-a-table-space-sql-developer, but there was no traction on the question, i thought i might try my luck over here. I'...
0
votes
0answers
37 views

Restore database without applying archivelogs

I have full backup collected with this command RUN { BACKUP AS COMPRESSED BACKUPSET TAG ${ORACLE_SID}_arch FILESPERSET 40 FORMAT '$RMAN_DIR_ARCH/arc_PROD_%U' ARCHIVELOG ALL DELETE ALL INPUT; BACKUP ...
1
vote
0answers
36 views

Finding the different rows from two resultsets based on one of the resultsets (MINUS, NOT EXISTS)

I'm testing queries on two resultsets that have the same value in certain columns on Oracle 11g. I want to exclude the rows that share these columns from my final result based on the first table. Say ...
0
votes
1answer
28 views

Oracle PK index extremely low performance

There's a table MYHIST containing 42M rows. The table has a primary key column MYHIST_ID NUMBER(22) with a valid index PK_MYHIST on it, and 50 more columns (of types VARCHAR2, NUMBER and DATE), which ...
0
votes
1answer
21 views

How to convert the JSON date with to_date function

How to convert the specific string Aug 6, 2018 3:17:11 PM with the function to_date? That string is a result of a Java parsing mechanism for JSON. I would like to be able to recover it directly from ...
2
votes
0answers
111 views

Changing a JSON object parsed with APEX_JSON

I would like to be able to change a JSON object parsed with the APEX_JSON.parse() function, but I found nothing in the official documentation, no tips or examples about that. In the code below I ...
0
votes
2answers
288 views

Update row with multiple row in another table Error : ORA-01427: single-row subquery returns more than one row? [closed]

I have Table A with 1 row and wanted to update it from another table (which has 3 rows for it and I can take any one of them). Table A ----------- TrnID | trn_Status 2345 | null 9876 | null 9878 | ...
0
votes
1answer
68 views

How to create a trigger in oracle to prevent insert dates

I'm learning Oracle and I had a problem. Chat table: CREATE TABLE training ( id_training NUMBER, id_user NUMBER, start_training DATE, end_training DATE ); I want to ...
-7
votes
1answer
40 views

Please help to get OUTPUT as shown in figure [closed]

As shown in figure input, showing name, id and mount as 'AMT'. I want the output for that as I have written as OUTPUT1 and OUTPUT2 as final output
0
votes
0answers
14 views

How to link two Oracle servers

I am using two oracle servers. One is server A(local oracle server) and server B(remote oracle server). Note: Installed Oracle version is 11.2.0 Requirement I need to link server A and server B. I ...
1
vote
0answers
36 views

How to apply a condition on “count over partition”?

I'm using Oracle 11g. Assuming multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit. With this table: datetime_id | user_id | place_id and I want to ...
0
votes
1answer
106 views

How to handle -904 Invalid identifier error in oracle store procedure

My proc is like this CREATE OR REPLACE PROCEDURE TEST_INSERT IS TotalUpd pls_integer; err_code number; err_msg varchar(500); invalid_excep EXCEPTION; PRAGMA ...
2
votes
1answer
242 views

Inserting year with zero

Is it possible to enter a date with a zero year? What would the insert clause look like? I ask, as an application is doing this, see the dump of the date: dt_data: 05/01/00 dump(dt_data): Typ=12 Len=...
0
votes
0answers
323 views

Oracle 11g shows 'ORA-01017: invalid username/password; logon denied' for remote connection

I tried ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE; to change its case sensitivity, I also tried changing SYS password using SQL>password but still I am facing same issue. I am trying to ...
0
votes
2answers
45 views

How to switch between Oracle 11g and 12c on same linux

I have oracle11g and 12c installed on same machine and few test cases that need to be run on both versions. Can power up both database simultaneously and run test paralally. Do you know any document ...
-7
votes
1answer
47 views

i want output as shown in below image [closed]

max_unit_price=maximum of unit price product wise difference=max_unit_price-individual unit price
1
vote
2answers
92 views

Does oracle database have a mechanism to replace specific character when storing data

I'm using oracle 11g database, does oracle database have a mechanism to replace specific character with other characters over all tables when storing data. For example i want to go to specific ...
1
vote
1answer
27 views

Should DB_RECOVERY_FILE_DEST_SIZE be smaller than the respective partition size?

Is it OK to set the DB_RECOVERY_FILE_DEST_SIZE to be equal to a drive partition that is dedicated for this purpose? That is, if I use a partition of 10GB, is it ok to set the ...
1
vote
0answers
156 views

Oracle Number (10, 0) decimal in (C#) code

The following query is executed to add a new column to an existing table: ALTER TABLE TABLE1 ADD COLUMN1 NUMBER(10, 0) DEFAULT -1 NOT NULL. Using DBForge to check the database, result in NUMBER (10, ...
0
votes
1answer
267 views

Can't install Oracle 11g [closed]

Error while installing Oracle 11g: Please find the StackTrace of this error. ID: oracle.install.driver.oui.OUISetupDriver:13 oracle.cluster.verification.PreReqNotSupportedException: Reference data ...
0
votes
1answer
149 views

Rename column in a partitioned table

I want to rename a column in a partitioned table. Will oracle do this for all partitions? Is there anything special for this change?
0
votes
0answers
10 views

How can I confim that Master encryption key is created in Oracle TDE?

I create Master encryption key using ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "key_agent_password"; on external keystore but Since I don't have any element eg. Keyname or KeyID to match, how ...
0
votes
0answers
19 views

Grant in advance privileges on Schema A for dynamically created tables on Schema B in oracle

I have the following scenario in my Oracle Database (11g) and I am trying to work around grants and access privileges. Note that the following is just a Minimum Viable Example and it does not cover ...
1
vote
0answers
46 views

How to detect if insert transactions in oracle db are slow?

At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data. Here's the situation: Some production devices send data results from tests to Server A, ...
1
vote
0answers
37 views

How to configure partial replication of Oracle DB

Suppose we have 2 identical Oracle 11g DB schemas: PRODUCTION and ARCHIVE. The goal is to keep in PRODUCTION the minimal amount of data required for online operations, and use ARCHIVE to store all ...
0
votes
1answer
71 views

Oracle Logon Trigger ReCompile

Typically when an oracle object goes invalid due to a dependent object being modified, the next execution of the invalid object will compile it and everything will be execute as expected (assuming no ...
0
votes
1answer
50 views

ORACLE 11g Know Insert Record Details which Failed to insert

I have started auditing insert records by user on failure to any table in my oracle 11g Database. I have used following command to do the same. AUDIT INSERT ANY TABLE BY SHENA BY ACCESS WHENEVER NOT ...
1
vote
1answer
31 views

PL/SQL Loop Returning One Result

I'm working with the following script: create or replace PROCEDURE a_proc is vi_get_emails varchar2(500); cursor get_emails is SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl....
0
votes
1answer
33 views

Add column to a table for every shema in an oracle db 11g

Our oracle db has various users with same schema. I would like to add a column to one table in those schemas. The same column to same table in every schema. I know that I can do that manually, like ...
0
votes
0answers
49 views

get_ddl Returns ENABLE ON QUERY COMPUTATION for 11g Materialized Views

I am hoping someone can help us solve an issue that is causing errors in 12c (12.2.0.1.0) during our regression testing. We have recently migrated from 11g to 12c so that none of our materialized ...
0
votes
3answers
51 views

Restrict sysdba or system administrator from reading package in oracle 11g

I have a requirement wherein the oracle database contains packages which have important stored procedures and functions which should not be exposed to the client / customer. The client / customer will ...
0
votes
1answer
32 views

Group by checks constraints are possible?

I have a table with the following columns: TEAM_ID integer, NAME varchar(64), IS_LEADER char(1) not null default 'Y' I want to apply the constraint that, in the same team, only a leader can be ...
0
votes
1answer
141 views

Oracle data transfer rate determined by rows instead of data size

Is there a setting in Oracle 11g that can throttle the rows/second being pulled from the db? Our data loads from Oracle are really slow. It does not seem to matter whether its OPENQUERY, SSIS, or SQL ...
0
votes
0answers
29 views

IMPDP Parallel insert operation

I am hoping someone can shed some light on this. When running an import into an oracle database, I sometimes see different behavior based on the parallel option. Some times, I will see multiple ...
1
vote
0answers
323 views

Oracle database went down because J000, m000, w000 process died [closed]

I know it is a background process in Oracle 11g. But I don't know why it automatically died. alert log: Process W000 died, see its trace file Process W000 died, see its trace file Process W000 ...
0
votes
1answer
57 views

XML parsing failed in the sqlplus

I get following error when executing the query Invalid token in '[count(../[name(../)=name(.)])=count(../) and count(../*)>1' However when installing the Itstar_xml_util package, it asks for two ...
0
votes
2answers
47 views

How to fetch last two year annual data in oracle

There is table named sales_data which I used for my project. The table structure is below: +------+------+------+--------+ | ID | Name | Year | Sales | +------+------+------+--------+ | 1000 | ...