Questions tagged [oracle-11g-r2]

Oracle Database 11g Release 2. Please also tag oracle for search purposes.

Filter by
Sorted by
Tagged with
1 vote
1 answer
29 views

insertion in a table of objects with nested table in oracle

I have a problem inserting in a nested table in oracle These are the relevant types and tables; create type movies_type as Table of ref movie_type; create type actor_type under person_type ( ...
user avatar
  • 13
0 votes
1 answer
59 views

Oracle 11gR2 - Expdp Full Database With Exclude Some Table Data

We will migrate our database using datapump. It will be full database export with exclude some table's datas. But we want to do a different operation for 2 different tables. We want to export the data ...
user avatar
0 votes
1 answer
33 views

Query to update one column based on the amount of another column

I have a table with below structure : create table Z_TEST_ONE ( col_id NUMBER, bed_amnt NUMBER, bes_amnt NUMBER, bop NUMBER ) Sample data is like this : col_id bed_amnt ...
user avatar
  • 1,302
0 votes
1 answer
34 views

Oracle: sub select with AS, COALESCE, GROUP BY, HAVING CASE WHEN and ORDER BY

I am typing the below code and it is returning me "ORA-00923: FROM keyword not found where expected". What is happening? SELECT max(alias.id) AS "id", (SELECT ...
user avatar
0 votes
2 answers
69 views

Silent Installation of Oracle 11gR2 Fails During NETCA setup in Redhat Linux 8

I am trying to install Oracle 11gR2 in Rhel 8. There was a few incidents of missing packages here and there which I managed to fix during the SOFTWARE ONLY installation. But now I am completely stuck ...
user avatar
0 votes
1 answer
24 views

what does the number in the "A_ROWS" column of the execution plan shows for operation "Index scan"?

I have a query as you can see below : select /*+gather_plan_statistics*/ * from mi_dimcustomer t where t.CUSTOMER_NUM = 321937 There's a Unique Index (IDX1_DIMCUSTOMER) on Customer_Num column of ...
user avatar
  • 1,302
0 votes
0 answers
21 views

Help recovering Oracle Datafile :selecting from or loading data into an oracle 11g r2 ED Datatabase: ORA-00376 & ORA-01110

A user created a datafile with size 200M and when the datafile was created it appeared in the OS as a Zero Kilobytes file. And then the user tried to drop the datafile but after that using query: ...
user avatar
  • 19
0 votes
1 answer
32 views

which process has start my db?

I have to restart a database, but it seems, it has not been start by the good process: ps -ef | grep smon | grep mydb oracle 54621 1 0 2021 ? 00:10:55 ora_smon_mydb . oraenv ORACLE_SID = [oracle] ...
user avatar
  • 304
1 vote
1 answer
91 views

Three "Nested loop join" in the Execution plan despite using two inner joins in the main query

I have a query as you can see below : with cte as (select customer_num from vmi_segment_customer_relation where effective_date = to_date('12/30/2021', 'mm/dd/yyyy') and segment_id = ...
user avatar
  • 1,302
0 votes
1 answer
117 views

Getting warnings while generating query Execution plan in Oracle , PLSQL developer

These are the steps I follow to generate a real Execution Plan in PLSQL developer : 1)Using this hint in my main query /+gather_plan_statistics/ select /*+gather_plan_statistics*/ from mi_factcustomer ...
user avatar
  • 1,302
-1 votes
1 answer
44 views

Example tablespcae in Oracle 11g

I need to know why example table space is created by Oracle 11g ? Does it effects the performance if it is full ? if yes how can we stop it being used ? Thanks
user avatar
0 votes
1 answer
108 views

Oracle SQL query: Best way to remove unwanted characters?

I want to remove all characters that are neither underscore, hyphen or alpha-numeric. Additionally, I don't want underscore or hyphen as the first character, so that needs to be removed as well. This ...
user avatar
  • 5,766
0 votes
0 answers
84 views

Oracle 11g to 19c - Upgrade Error

We upgraded our test database from 11gR2 to 19c (19.12) (OS OL 7.9). After the upgrade, all the components in the dba_registry seem to be in the UPGRADED state, not VALID. At the same time, we ...
user avatar
0 votes
2 answers
91 views

Create view with inline function in db<>fiddle

I'm trying to create a view with in inline function in db<>fiddle. I've removed the CREATE VIEW part for now, and am just trying to run the query, just to get to the root issue. select /*+ ...
user avatar
  • 1,387
0 votes
0 answers
64 views

See Max Simultaneous Sessions Per User

In an effort to set a reasonable sessions_per_user profile setting for shared database accounts or other non-unique account (ie. an account an application uses to log into the database for all app ...
user avatar
0 votes
1 answer
24 views

Query to show whether a record is appeared in the previous date or not

I have a table with below structure and sample data: create table TEST_ONE ( purchase_date DATE, customer_id NUMBER ) | purchase_date | customer_id ---------------------------------- |...
user avatar
  • 1,302
0 votes
3 answers
85 views

Does using "qb_name()" hint (query block name) in a query have significant effect on performance?

I've just become familiar with the hint "qb_name" (query block name) in Oracle and below you can see an example of using this hint in a query: select /*+ qb_name(main)...
user avatar
  • 1,302
2 votes
1 answer
315 views

Tuple comparison with ORACLE and SQLSERVER

I am working on cursor based pagination using multiple columns. I need to make a tuple comparison since date might not be a unique value. When I am using MySQL and PostgreSQL, I can do it like this. ...
user avatar
0 votes
2 answers
47 views

How to Escape dot(.) In a substitution variable

I have a variable called ERROR_DETAIL and inside my file file.sql I have this line: SELECT 'error detail : &ERROR_DETAIL ' FROM DUAL; Once executed sqlplus complains with the following error ...
user avatar
  • 11
0 votes
0 answers
17 views

how to migrate on-premise oracle databae to oracle cloud?

I want to migrate my on-premise oracle 11g database to oracle cloud(11g) database. -my system is windows 64bit. -i have already created free tire account in oracle cloud and inside db system i have ...
user avatar
  • 1
0 votes
1 answer
27 views

Index not used in a partitioned Oracle table

I have a table partitioned by list create table ICT_ENCOURS_BRUT ( code_exercice VARCHAR2(4), CODE_PRODUIT VARCHAR2(2), ....... ) partition by list (CODE_EXERCICE) ( ...
user avatar
0 votes
0 answers
38 views

How to find and delete partial duplicates in Oracle

I'm deleting duplicate values in an Oracle database using this: DELETE FROM results a WHERE a.ROWID IN (SELECT ROWID FROM (SELECT ROWID, ROW_NUMBER() OVER (PARTITION BY sample_id, ...
user avatar
0 votes
0 answers
56 views

Scheduled job not present on database, but visible from dba_scheduler_job_log

I'm seeing this error in the alert log (sorry, it's in italian!): *** 2021-09-14 23:50:17.891 *** SESSION ID:(249.6897) 2021-09-14 23:50:17.891 *** CLIENT ID:() 2021-09-14 23:50:17.891 *** SERVICE ...
user avatar
0 votes
1 answer
62 views

Oracle 11gR2 (11.2.0.4.0) - Drop and Remove Datafiles

We have a database of 2000 users. We will drop all remaining schemas, leaving only 2 end users, excluding Internal (Oracle maintained) users. After this operation, there will be a very high reduction ...
user avatar
0 votes
2 answers
90 views

Inserting data into the VIEW , result in data being inserted into the original table, why is this happening?

I have a table as you can see below: create table table_test (col_id number(1)) The table has below data: col_id ------ 1 2 3 I create a view based on this table: create view v_table_test as ...
user avatar
  • 1,302
0 votes
3 answers
293 views

Oracle database won't start, ORA-03113

Running Oracle 11.2.0.4.0 on CentOS and discovered the hard drive filled up, giving error in alert log: ORA-19815: WARNING: db_recovery_file_dest_size of 64424509440 bytes is 99.98% used, and has ...
user avatar
  • 149
0 votes
1 answer
39 views

When to perform which type of incomplete recovery?

This is purely learning purpose, that which incomplete recovery type is use in which condition? And DBA is bound to choose one or can choose anyone of following Incomplete recovery type? Scn-based ...
user avatar
0 votes
1 answer
69 views

Trouble querying the correct info from gv$encryption_wallet

I'm trying to get the wallet status for the current instance of an Oracle 11g 2 node RAC DB (but ideally all RDBMS versions). The following command returns values for both nodes instead of one: SQL>...
user avatar
  • 41
0 votes
1 answer
180 views

Why doesn't /*+ NO_INDEX*/ hint , affect the "execution plan"?

I have a sample table with below structure : create table mi_dimcustomer (customer_num number(10), <other columns> <data types>) And there is a unique index on column customer_num. I'...
user avatar
  • 1,302
0 votes
1 answer
379 views

Facing error "cannot fetch plan for SQL_ID" while trying to capture the execution plan

I'm trying to capture the execution plan of my query on PL/SQL developer as you can see below : select * from vmi_dimcustomer t1 inner join vmi_factcustomer t2 on t1.customer_num = t2....
user avatar
  • 1,302
0 votes
1 answer
182 views

The correct way for generating "Execution plan" for a PL-SQL statement in Oracle

I'm studying about Explain plan and Execution plan in Oracle and according to what I've read so far , The explained plan can differ from the actual plan used during statement execution. So I think ...
user avatar
  • 1,302
2 votes
2 answers
163 views

weird behaviour of NVL(<Column_name>,Sequence.nextval) when the colummn has data?

I've created a sequence like this : CREATE SEQUENCE seq_test2 MINVALUE 0 MAXVALUE 999999999999999999999999999 START WITH 0 INCREMENT BY 1; Both of these two queries show me the number 0: ...
user avatar
  • 1,302
-2 votes
1 answer
130 views

How to auto run oracle stored procedure [duplicate]

Is there any possibility to auto run procedure after a specific time/date? For example I want to auto run this stored procedure after 2 days: CREATE OR REPLACE PROCEDURE deleteresetlinks AS BEGIN ...
user avatar
0 votes
1 answer
248 views

Oracle 11g TDE wallet autologin

I have setup Oracle TDE for my 11.2.0.4 database. I created the wallet. I created the autologin wallet and everything looked good. {ORACLE_HOME}/bin/orapki wallet create -wallet cwallet.sso -...
user avatar
  • 41
0 votes
1 answer
27 views

Oracle 11g Encryption

Can somebody please tell me how to encrypt an existing tablespace in 11g? The wallet has been setup and the sqlnet.ora points to it. SQL> select STATUS FROM V$ENCRYPTION_WALLET; STATUS -----------...
user avatar
  • 41
2 votes
3 answers
3k views

How to solve error "ORA:01-006 :bind variable does not exist" when working with bind variables?

I have a table with below structure and data : create table TEST_TABLE ( item_number NUMBER, item_name VARCHAR2(50), item_col VARCHAR2(50), item_pol VARCHAR2(50) ) Sample data: ...
user avatar
  • 1,302
0 votes
0 answers
32 views

Updating status on delete from another table (Oracle SQL)

How can I merge these two queries? delete from login where email='test@test.com' and date_created > created_date + interval '1' day; update staff set status_id=1 where staff.email=login.email;
user avatar
  • 13
0 votes
1 answer
40 views

Fetch all rows and leave column value empty if no equality match

I have a query where I want to fetch all rows where the value in column x is between 400-600. Including in this I want a matching column value from antoher table. But I want all 200 rows between 400 ...
user avatar
  • 103
1 vote
2 answers
135 views

auto set value of a specific column after 24 hours and to delete record after one week: Oracle SQL

Here is my table: create table passwordResetLink( link_id number(5) primary key, link_date timestamp(0) default sysdate NOT NULL, isExpired number(2) email varchar2(110) UNIQUE NOT NULL, linkUsed ...
user avatar
  • 13
0 votes
0 answers
35 views

RMAN Catalog requirement with DataGuard

If you have an 11g or 19c Oracle Standby DB setup using dataguard must you use an RMAN catalog when backing it up? I ask, because it was a requirement in much older versions of Oracle (e.g. 9i) but I ...
user avatar
  • 41
0 votes
1 answer
298 views

Oracle 11g - Decreasing Big Table Size

I want to reduce the size of a table. I thought if I could do this with export / import, but when I check it with the ESTIMATE_ONLY option, it doesn't seem to work. I have no chance of using ...
user avatar
0 votes
0 answers
148 views

import .dmp in Toad : Invalid format of Import utility name Verify that ORACLE_HOME is properly set

When I tried to import a DMP file using TOAD (9.0.1) Import Utility Wizard I get an error Invalid format of Import utility name Verify that ORACLE_HOME is properly set Any suggestions please? I ...
user avatar
1 vote
1 answer
140 views

The "unexpected greater" PGA size allocated in rman channel

Environment: RHEL 6.9、 Oracle 11.2.0.4 When we running some rman backup/restore jobs, we found the actual PGA size could greatly exceed the predefined limit value about PGA: Why the following ...
user avatar
  • 11
0 votes
0 answers
271 views

Oracle XE 11.2.0 data pump expdb works on but impdb doesn't on Windows 10

New to Oracle DB administration here. I have a fresh localhost installation of Oracle XE 11.2.0 (x64 installation from here) on my computer using Windows 10 64-bit. I am able to connect to the DB ...
user avatar
0 votes
1 answer
52 views

Better way of handling incorrect date format in a column with "char(10)" data type / TRY_CONVERT equivalent in PLSQL

I have a source table with below structure: create table customer_info (customer_num number, birth_date char(10)) Unfortunately the birth_date column data type is char(10) instead of date. ...
user avatar
  • 1,302
1 vote
0 answers
33 views

Naming a datafile with spaces in the name

I would like to know if there could be a problem when naming a datafile with spaces in the name, I have an Oracle 11gR2 database, using ASM as storage, a datafile was created, belonging to an ...
user avatar
0 votes
1 answer
41 views

Why is the schema name necessary here and how to compile without it

I'm working on an Oracle Forms 11g application linked with an Oracle 18c database and I stumbled upon a strange error when compiling my form. Here's a snippet to illustrate the problem (I changed the ...
user avatar
  • 101
1 vote
1 answer
307 views

Oracle Enterprise Manager 11g Database Control - Agent Unreachable

Single instance DB. I can login to Oracle Enterprise Manger (OEM). However, on the Home Page it says: Status - Agent Unreachable Most tasks such as checking RMAN status, adding datafiles, etc. ...
user avatar
1 vote
2 answers
736 views

Query to find all dates between two specified dates using plsql

I have a table with below structure create table test_table ( customer_num NUMBER, card_number char(2), card_issue_date DATE, card_expire_date DATE ) Example data is: ...
user avatar
  • 1,302
1 vote
1 answer
70 views

Query to find the "Number of days" a customer has an active card between a specified interval

I have a table with below structure : create table test_table ( customer_num NUMBER, card_number char(2), card_issue_date DATE, card_expire_date DATE ) Example data is: ...
user avatar
  • 1,302

1
2 3 4 5
28