Questions tagged [oracle-11g-r2]

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

Filter by
Sorted by
Tagged with
-1 votes
0 answers
18 views

how to get explain plan from pl/sql function call?

consider this working pl/sql block: declare o_result boolean; io_error varchar2(30000); tab1 someTable1; tab2 someTable2; begin tab1(1).col1 := 'str'...
rado's user avatar
  • 109
0 votes
0 answers
6 views

How to migrate from Oracle 11g Single Instance residing on a Azure Linux VM to Oracle 19c RAC?

What are the different ways to migrate from Oracle 11g Single Instance residing on a Azure Linux VM to Oracle 19c RAC on Azure VMs?
Sunil Bopanna's user avatar
-1 votes
1 answer
83 views

ORA-01400: cannot insert NULL into

I have two tables: create table country( country_code varchar2(2) PRIMARY KEY, country nvarchar2(57) NOT NULL ); SQL> desc airport_final Name Null? Type -------------------...
Java_Beginner's user avatar
0 votes
1 answer
50 views

Oracle statement inserting duplicate data

I am using this statement to insert records from another table: SQL> insert into city(city_code, country_code, city) select distinct(city_code), country_code, city_name from airport_final; 2 ...
Raakh's user avatar
  • 17
0 votes
2 answers
34 views

inserting data from another table

create user TESTING identified by "Test"; grant connect, resource to TESTING; grant dba to TESTING; connect TESTING/Test; set define off; create table airport_final ( iata_code varchar2(...
Raakh's user avatar
  • 17
-1 votes
1 answer
65 views

Need help with DBMS_COMPRESSION.GET_COMPRESSION_RATIO

I need some help on how to run the DBMS_COMPRESSION.GET_COMPRESSION_RATIO package on all the tables of a database to see if is worth the implementation or not. This is the procedure I'm running alone ...
Daniel Soto's user avatar
-1 votes
2 answers
64 views

SQL breaking my query where there is NULL value

Here is my sql statement and the error I am facing: SQL> INSERT INTO COUNTRY(COUNTRY_CODE) select unique(country_code) from AIRPORTDEMODATA where country_code IS NOT NULL; 2 INSERT INTO COUNTRY(...
Java_Beginner's user avatar
-1 votes
1 answer
27 views

Expecting "," or ")", found "nvarchar2"

Here is my SQL*Loader: Release 11.2.0.2.0 - Production on Fri Nov 10 00:39:56 2023 OS=UBUNTU 20 I am trying to load data from csv file with the help of following code: OPTIONS (SKIP=1) LOAD DATA ...
Java_Beginner's user avatar
0 votes
0 answers
48 views

Discrepancy Between ORACLE_UNQNAME, db_unique_name, and v$database Causing OEM Startup Issues

I am experiencing an issue with Oracle 11g where the ORACLE_UNQNAME, db_unique_name, and the name from v$database are not aligned, causing problems with Oracle Enterprise Manager (OEM) startup and ...
Trevor's user avatar
  • 101
0 votes
1 answer
24 views

Is that possible to count number of specific columns in Oracle

I have a table like this: Is that possible to return the number of rules that pass or fail based on the no? Thanks, T
tnddld's user avatar
  • 3
1 vote
0 answers
23 views

Restore oracle database on a new machine with only datafiles and control files?

I lost my laptop and I had an oracle database 11g r2 in it but I still have all the data files, create control files script and nothing else now I bought new laptop and I want to get my data back with ...
Hussein Sarea's user avatar
0 votes
0 answers
185 views

MySQL connection error : ORA-28500: connection from ORACLE to a non-Oracle system

Most of the time we don't have issues with the connection from Oracle (using DB link and ODBC driver) to the mySQL DB. However, every once in a while we get the error which persistes for like 30 min: ...
Amir's user avatar
  • 1
0 votes
1 answer
42 views

Update or insert records to the same table based on a similar table but with conditions

I'm tryin to update or insert records on the same table based on a similar table using Merge but based on the count of records in the source table. I have 2 tables say one is POV [pov, dc, item](the ...
Akash Reddy's user avatar
0 votes
1 answer
850 views

ORA-32771: Cannot Add File To Bigfile Tablespace

I have 1TB tablespace that have one bigfile , The disk size now full and added extra disk to my server but there is no way to expand Drive that bigfile in it , so need to add another bigfile ,if ...
FarshiD Naqizadeh's user avatar
0 votes
1 answer
176 views

Why is the SYS user granted both the DBA role and the SYSDBA privilege?

If the SYSDBA privilege gives the grantee ultimate authority, then why does the SYS user also have the DBA role in addition to the SYSDBA privilege?
Mehdi Charife's user avatar
0 votes
1 answer
42 views

Can database administrators access and modify the database before it's opened?

I'm trying to answer the following question (asked in a previous exam and supposedly having one correct answer): To create a DATAFILE and Assign it to a DATABASE, the instance can't be started in ...
Mehdi Charife's user avatar
0 votes
0 answers
87 views

Cross Version EXPDP/IMPDP of Indexes

Our source database is 11g and target is 19c. My question is about exporting and importing indexes. Does the EXPDP command "exports" the actual index structure or just the "Create index&...
oradbanj's user avatar
  • 141
0 votes
0 answers
56 views

Can a user connected as sysdba always bypass auditing mechanisms?

Can a user connected as sysdba always bypass auditing mechanisms? What could be done to minimize (or prevent) auditing prevention?
Mehdi Charife's user avatar
0 votes
2 answers
530 views

expdp fails with error ORA-31626 without any details on a full export

I am exporting a complete database (full=y) on oracle 11gR2 on Linux, and always get this error. There are no other messages, so the problem is not about timeout or permissions as shown elsewhere. ...
eppesuig's user avatar
  • 4,962
0 votes
0 answers
17 views

In Oracle SQL, How do I select an aggregate column from the result of a group by function? [duplicate]

SELECT * FROM Clients c JOIN Info i ON i.id = c.info_id JOIN ( SELECT site_owner_id, SUM(p.amount_paid) FROM Site s JOIN SecurityArrangement sa ON sa.site_id = ...
Kun.tito's user avatar
  • 101
1 vote
1 answer
99 views

Patching Oracle Client 11g2 versions on SQL Server

We have a SQL Server 2014 running on a Windows Machine, with Oracle Client 11.2.0.1.0 running. This client is used to Allow SQL server connect to remote Oracle Data sources. This is the directory for ...
Depth of Field's user avatar
0 votes
1 answer
712 views

How To turn a string with "pipe-separated" values into individual rows in Oracle PL/SQL

I have a table with below structure : create table student_info ( item_number number, st_firstname varchar2(50), st_lastname varchar2(50), st_score varchar2(50) ) Here is a ...
Pantea's user avatar
  • 1,412
2 votes
1 answer
783 views

Listener error ORA-12518 & ORA-12520 after changing the IP with domain

We have a server with Oracle 11g and a database. The network administrator added an alias domain to the ip of the server where oracle is. We haven't changed anything else. After that, when SSRS server ...
eathan's user avatar
  • 23
0 votes
1 answer
325 views

Receiving error "Local collection types not allowed" while working with associative arrays

I have an input parameter with this format '0,1,2,3,4' , what I want to do is to declare an array with a variable size (by this I mean the input parameter might be like '0,1' , so the size is not fix),...
Pantea's user avatar
  • 1,412
0 votes
1 answer
93 views

create table based on another table

I have a table called " Product " it has: ID_P, Name, expiration date Now I want to create another table called "Stock" this table has ID, Quantity_of_the_product, ...
kylie's user avatar
  • 41
0 votes
1 answer
2k views

how to create a calculated column

I want to create a table, this table's name is "Prod", in his table, I have the: ID, name, Buying_price and selling_price when the user inserts the data, I want him to enter "name, and ...
kylie's user avatar
  • 41
0 votes
0 answers
126 views

How to restore an oracle database from a manual cold backup on different server?

I have all the contents of the oradata folder and my intuition tells me that on the new server I just need to install oracle binaries of the same version, recreate the directory structure, create a ...
number09128's user avatar
0 votes
1 answer
23 views

Filtering a table by different properties id, value in another table

I have this structure of data TABLE_MAIN ID NAME 1 object 1 2 object 2 TABLE_COMPL_DATA ID ID_MAIN ID_TYPE VALUE 1 1 5 'house' 1 1 7 '2' 1 1 3 '1' 1 2 7 '2' 1 2 3 '1' I want to extract all ...
JuniorGuy's user avatar
0 votes
1 answer
87 views

Oracle 11g Understanding UNDO behaviour

Couple of weeks ago i faced UNDO issue at my database. Now it is always full and system asks to add some space. I have added another 32GB autoextensible file now it is 32+32+32, but in a couple days, ...
0 votes
1 answer
172 views

Restrict access to certain tables for users who have DBA roles?

I have an Oracle Database which includes many users and some of them have DBA roles. The thing is, DBA's shouldn't delete any records from SYS.AUD$ table. Is there a way that i can achieve this?
postgresnewbie's user avatar
0 votes
1 answer
123 views

My subdirectories under $ORACLE_HOME are missing

Im using oracle 11g. I cannot access the software products since they are missing. When I cd to $ORACLE_HOME, there are no files under it. They are missing. I cannot access the sqlplus. What to do in ...
beginner_01's user avatar
0 votes
0 answers
471 views

Procedure updating table with CLOB data type column does not work properly

I have a table with below structure: create table bb_analysisitems_batch ( batch_id number, batch_name varchar2(100), batch_result clob ) There are other ...
Pantea's user avatar
  • 1,412
5 votes
2 answers
542 views

How to create intervals of 100 based on a column in a query?

I have a table with below structure: create table item_test (item_id varchar2(10), item_row number) Sample data is like below , please note that numbers in Item_Row column starts at 1 and ...
Pantea's user avatar
  • 1,412
0 votes
1 answer
34 views

"Unconfigure" oracle database

I have an Oracle 11g RAC installation which I want to "unconfigure". This is a cloned machine which has an Oracle 11g RAC installation. I want to "reset" it (drop databases, ...
user2465039's user avatar
0 votes
0 answers
59 views

Make the SQL statement stop! Stopping supplemental log data never ends

We’re running a single-instance Oracle 11gR2 database for QA and development on Exadata hardware. I was asked to enable database-level minimal supplemental logging. The database has just over 2T of ...
S M's user avatar
  • 196
0 votes
3 answers
9k views

export multiple tables using expdp with query condition

I have requirement to export multiple tables using datapump job and the ultimate goal is that we are doing a migration from on-Prem . They have something like 6TB of AUD data and we are not required ...
sarat's user avatar
  • 60
0 votes
1 answer
26 views

Better ways of concating the value of columns

I have a table with below structure and sample data: create table CUSTOMER_TEST ( customer_num Number, rel_one Number, rel_two Number, rel_three Number, rel_four Number ) ...
Pantea's user avatar
  • 1,412
1 vote
1 answer
93 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 ( ...
user062's user avatar
  • 13
0 votes
1 answer
2k 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 ...
jrdba123's user avatar
0 votes
1 answer
107 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 ...
Pantea's user avatar
  • 1,412
0 votes
1 answer
235 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 ...
Boneman's user avatar
0 votes
2 answers
402 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 ...
the_ruffler's user avatar
0 votes
1 answer
47 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 ...
Pantea's user avatar
  • 1,412
0 votes
0 answers
229 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: ...
Obert's user avatar
  • 19
0 votes
1 answer
55 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] ...
eliatou's user avatar
  • 316
1 vote
1 answer
564 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 = ...
Pantea's user avatar
  • 1,412
0 votes
1 answer
421 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 ...
Pantea's user avatar
  • 1,412
-1 votes
1 answer
92 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
Waseem Hassan's user avatar
0 votes
1 answer
5k 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 ...
dbdemon's user avatar
  • 6,301
0 votes
0 answers
304 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 ...
jrdba123's user avatar

1
2 3 4 5
28