Stack Exchange Network

Stack Exchange network consists of 175 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

Questions tagged [oracle-11g]

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

0
votes
1answer
20 views

error writing input to commandERROR: Invalid username and/or password

This is the error I have been getting on my RMAN notifcations on my backup jobs for the last day or so. The title is exactly how the message is formatted. ~~~End Step Output/Error Log~~~ Error Log:...
1
vote
0answers
32 views

Using conditions to return specific rows

From the following table i am trying to retrieve specific rows based on a criteria +----+--------------+------+ | ID | UPDATED_DATE | TYPE | +----+--------------+------+ | 1 | 01/13 | P | |...
0
votes
0answers
11 views

How do I aggregate data based on CASE results?

I need to create a column that counts the total based off the CASE condition below. For example, the total occurrences the CASE meets the criteria for 'Group 1' AND 'Group 2'. Additionally, I need to ...
0
votes
0answers
43 views

Indexing binary data in PostgreSQL?

I am converting an Oracle 11G DB to PostgreSQL v11 DB. I am super new to the whole database thing and I have encountered a query using index type of ctxsys.context to create an index. I have no idea ...
0
votes
0answers
37 views

Silent install of Oracle XE 11g fails with error -3

I'm trying to install silently Oracle 11g XE in the default location (C:\oraclexe). PS D:\Profiles\xxx\Downloads\x.x\oraclexe\oraclexe-11.2.0> .\setup.exe /S /v/qn However, the command generates ...
0
votes
1answer
8 views

Oracle 11g, Will droping partition reuse free space on bigfile tablespace

I have four range partitioned tables which belongs to bigfile tablespace. Also local indexes on that tables belongs to other bigfile tablespace. My partitioning range is one day and I have stored ...
1
vote
1answer
47 views

Is it possible to show DML that is locking some table?

I already saw here how I can list my locked objects. Suppose I, using PL/SQL Developer(pink cylinder logo one), run select * from someTable for update and didn't commit nor rollback. Now using the ...
0
votes
0answers
75 views

TNS-12560: TNS:protocol adapter error

I cannot access oracle from remote due to my listener error. I checked all configuration files which are listener.ora, tnsanmes.ora and sqlnet but I didn't find any missing config. Below is my ...
0
votes
2answers
72 views

Splitting Comma Separated string into columns by using REGEXP_SUBSTR

Here is my example string: select 'po1,qty1,po2,qty2,..,POn,QTYn' str from dual. The string length is dynamic. It may have more than one PO and QTY. Now, I want the output displayed as all the POs ...
0
votes
0answers
21 views

Connect to Oracle 11g from remote computer

SQL> connect hr/hr@//192.168.1.113:1521/orcl ERROR: ORA-12541: TNS:no listener I'm getting this error while executing above command. Help me out how to connect to remote oracle database.
0
votes
0answers
21 views

Oracle Best-Buyer-Customer is Wholesale or Retail buyer

I have data warehouse with Fact table Orders, and CustomerDimension, and TimeDimention, and BookDimension. CREATE TABLE DIM_CUSTOMER( DIMCUSTID NUMBER(6) PRIMARY KEY, CustId NUMBER(4) ...
0
votes
1answer
58 views

How to create stored procedure that returns query result?

I have query that has a lot of columns (about 30) and I would like to place that query in stored procedure. The reason behind that is because this query is used in multiple places in the system. There ...
1
vote
0answers
57 views

Trigger for add discount on total price calculated from other table - oracle 11g

I have the following schema: CREATE TABLE ORDER( OrderId NUMBER PRIMARY KEY, Value NUMBER); CREATE TABLE ORDERITEM( OrderItemId NUMBER PRIMARY KEY, OrderId NUMBER, ...
1
vote
1answer
34 views

Order after rollup - oracle 11g

I am using rollup() function in group by expression to show the result query, but my output like this: Year Month AmountOfMoney ==== ===== ============= 1998 March 130....
3
votes
2answers
67 views

How can I justify making changes to my redo logs?

I don't spend a lot of time with my Oracle environment. I'm more of a SQL Server guy. I need to look into the alert logs to check for correlation of an application error. What I found instead was ...
0
votes
1answer
42 views

In a database trigger on event create - How do I reference the created object?

Given the following trigger: CREATE OR REPLACE TRIGGER TRG_CREATE_TABLE_INITRANS AFTER CREATE ON SCHEMA BEGIN IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN exec 'alter table '||<table_owner&...
-4
votes
1answer
47 views

Optimise my query [closed]

select REMITTORACCOUNTNUMBER, TRANTYPE, RRN, AMOUNT, responsecode , (CASE when auxrc is NULL then 'N' else auxrc END) as cbsstatus, responsecode as switchstatus ...
0
votes
1answer
50 views

Optimize Oracle 11g SQL*Plus connection for large remote SELECTs [closed]

Which options should I set in SQL*Plus for a script that runs multiple SELECTs, (some are JOINs) to SPOOL the results as text (CSV) in order to optimize these SELECTs, specifically one resulting in a ...
0
votes
1answer
59 views

Updating Database table from another machine

I am trying to update my database using a batch file. Batch file is located on machine say ABC which should pick a text file from the local directory on ABC and upload that file to DATABASE located on ...
0
votes
1answer
127 views

Is it feasible to escape ' (single quote) with ESCAPE clause, if yes, then how to do it?

I understand that we can use '' to escape a single quote in Oracle queries. However, I want to know if it is feasible to escape a single quote in the ESCAPE clause. set up: create table test_t ( x ...
0
votes
1answer
71 views

Consecutive dates count

I have table data like below: CLIAM_KEY MBR_ID SERV_FROM clm1 mem1 01-JAN-18 clm2 mem1 02-JAN-18 clm3 mem1 03-JAN-18 clm3 mem1 04-JAN-18 clm4 mem1 ...
3
votes
1answer
276 views

Oracle Database Server TNS Listener Remote Registration Vulnerability (CVE-2012-1675)

Some of our Internal DBs Oracle installed in Linux and Windows servers have been highlighted with the issue Oracle TNS Listener Remote Poisoning (CVE-2012-1675). This security alert addresses the ...
1
vote
1answer
1k 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 ...
0
votes
1answer
73 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
65 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
2answers
51 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
986 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
47 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
114 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
1answer
34 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
404 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'...
1
vote
0answers
37 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
31 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
63 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
275 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
957 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
143 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 ...
0
votes
0answers
16 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
302 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
220 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
244 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
457 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
91 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 ...
1
vote
2answers
222 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
70 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
283 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
632 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
309 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
25 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 ...