Questions tagged [oracle]

All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.

Filter by
Sorted by
Tagged with
0 votes
1 answer
280 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 ...
0 votes
1 answer
345 views

Can't start oracle after recovery

I'm trying to import the database to another instance. So I imported the database with restore controlfile, restore database and recover database command. When I try to start the database, it ...
1 vote
2 answers
397 views

Oracle MV requires object type to be defined as FINAL?

I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY: The MV would do a COMPLETE refresh on a schedule — on a local table. Some info ...
0 votes
1 answer
44 views

Oracle partition strategy suggestions

I have a transaction table with 1 billion rows per month (Approx. 50 GB/month). I will have aggregation of transactions for a specific user_id and a date range. The aggregation can be like Aggregate ...
0 votes
1 answer
357 views

Oracle: Is there a way to put exception handling into a function and just reference that function inside procedures?

If you were going to write 10 procedures that all have the same exceptions, you'd want to just reference the procedures defined somewhere else, right? You can call a function inside a procedure create ...
1 vote
1 answer
277 views

Getting the latest timestamp in Oracle

I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in ...
0 votes
0 answers
26 views

See an arbitrary SQL statement in oracle enterprise manager

I like to see any sql statement in the Oracle Enterprise Manager (oem) Cloud Control 13c, e.g. in the active session history. A simple select is not shown in any view. After some research I found in ...
0 votes
1 answer
388 views

Oracle RAC and DataGuard configuration

We have deployed an Oracle RAC cluster which consists of three Oracle instances. As we are considering the HA issue, and as far as I know, the only way of Oracle HA is DataGuard. So is it possible ...
0 votes
1 answer
22 views

How to register static listener in listener_scan as well?

I have a listener called "monitoramento" which has three static listener. SID_LIST_MONITORAMENTO= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=BRXXX_MONITORAMENTO) (...
0 votes
1 answer
3k views

What causes temp tablespace to be full and generates ORA-01652 error

I would like to understand in what sort of situations or what issues could cause TEMP tablespace to run out of space. Is it too much sorting occur on TEMP? How do I identify such issue from AWR? And ...
0 votes
1 answer
2k views

How can i Restore Oracle 12c database Schema from a full EXPDP dump on a Test Server providing a new scheam name?

I have a production database whose full dump is 500Gig and and I cannot do a full restore on a test server because of space issues. However I need to restore a certain schema in from that dump into ...
0 votes
1 answer
642 views

SQLcl Oracle to export data to CSV without blank first line and without carriage return character at the end

For reasons, I am using the following here-doc approach to export data to CSV. $ /oracle/sqlcl/bin/sql -s username/password@//host.mybiz.com.my:1521/dwh > arca.csv <<EOF SET SQLFORMAT CSV SET ...
1 vote
1 answer
299 views

How to sort query filename output like Windows explorer?

i would like to order my query output the way Windows Explorer does. First special characters like _, then the numbers, and the letters last. _ttt.bmp 0.gif bbb.png
1 vote
0 answers
18 views

Composite key with a auto-incremented column

I'm using Oracle, but this may apply to other Relational DBs, as well. Suppose I have a table T with columns C1, C2, C3, C4. Note: C4 is numeric. Is it possible to make C2, C3, and C4 a composite key ...
1 vote
1 answer
2k views

Will I have to restore controlfile in RMAN level 1 backupset before recover database?

For example, I have a level 0 and level 1 RMAN backupset files like inr00_0413_* inr01_0413_1_* Is it necessary to do this when recover from RMAN level 1 backup: restore controlfile from ...
0 votes
1 answer
274 views

How to print empty string as a result from xml node creation using oracle in a procedure

i want to print one of the node value as Author ="" empty string in xml node element as below: This syntax is in oracle: CREATE OR REPLACE FUNCTION GetRtData ( pf_wkstn_oid_sh number, ...
1 vote
1 answer
2k views

Oracle - Delete dmp files from ASM diskgroup

I want to delete my export files daily from ASM diskgroup with a crontab job. I want to prepare a script for it. ASMCMD> ls exp1.dmp exp2.dmp exp3.dmp exp4.dmp ASMCMD> pwd +DATA/EXP How can I ...
0 votes
1 answer
1k views

Oracle 11gR2 Enterprise manager problem

I joined a company as a fresher DBA and in testing server there is weird problem. My listener is running very well and developers can connect remotely to database but whenever I tried to start ...
-2 votes
0 answers
11 views

Pivot rows to columns (Oracle SQL)

I would like to transpose the old table to new table(see screenshot below) Old table New Table
0 votes
1 answer
852 views

Oraagent many SYSRAC processes open

I see in my 19c Cluster database a lot of sessions (over 350) with SYSRAC user and status INACTIVE with oraagent.bin TNS V1. They can consume my connection pool (how can I make this bigger number). ...
0 votes
1 answer
14 views

Flashback Logs Not Deleted With PDB Drop

There is a known issue with Oracle where the flashback logs are not reclaimable when a PDB is dropped and a restore point exists in the PDB. I can see a restore point in x$kccrsp but no restore points ...
0 votes
1 answer
624 views

After RAM upgrade on 32-bit Oracle server, ORA-04031: unable to allocate x bytes of shared memory

We are in the process of upgrading our Oracle database server at work, from SUSE Linux Enterprise Server 10.4 / Oracle 10g / 4GB RAM (Intel 32-bit) to Scientific Linux 6.4 / Oracle 12c / 8GB RAM (...
1 vote
2 answers
2k views

Insert Into queries take too much time in Apache spark

I am using Oracle Database with Apache spark 2.3.0 In my code I am writing final output (after all the processing) in three tables using below spark command. billableChargeKDF.write().mode("append")....
1 vote
2 answers
2k views

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 01589

I have oracle_19C. I get this error unable to extend index string.string partition string by string in tablespace string for users tablespace, then I create DATAFILE by mistake as shown as: ALTER ...
0 votes
1 answer
594 views

Enterprise manager failed to start in oracle 10g

I am using oracle 10g with 4 instance with same server but one of them has failed to start Oracle Enterprise Manager and rest of three are working fine. Starting Oracle Enterprise Manager 10g ...
0 votes
1 answer
19 views

Network utilization maxed out during backup. Is it bad in itself?

We have a cluster of two Oracle DBs. Monitoring software agent is installed on both of the machines and overnight, when backups are happening, we get warnings that network interface utilisation on the ...
0 votes
1 answer
1k views

Delete backup directories using RMAN

Is there any way RMAN can delete empty backup directories?
0 votes
2 answers
4k views

Oracle user created information

Is it possible to determine what user created another user's account in Oracle database? I have two types of users in database: Users having system and object privileges Users having only system ...
0 votes
1 answer
1k views

How to view databases and tables after 'imp' import using Oracle SQL developer

Maybe that's a wrong form of answer... The problem is: I've got an Oracle database dump file (very old, created with imp utility) At last, I've imported it. The command was imp file=E:\Downloads\...
0 votes
0 answers
22 views

Is there an analytical function to normalize values in Oracle?

In the following example we have ten random values from 6 to 233 (or any other integer range). Using common math we have, on last column, the values normalized from 0 to 1 (the usual range in this ...
0 votes
1 answer
2k views

Is it possible to set Audit_trail parameter to both DB and OS in Oracle database

In Oracle database ,we can direct audit records to either db , os or in xml file by using the below command: ALTER SYSTEM SET AUDIT_TRAIL= DB SCOPE=SPFILE; But is it possible to direct audit records ...
0 votes
1 answer
514 views

Oracle equivalent of PostgreSQL ARRAY_AGG

I'm looking for an aggregate function supported by Oracle SQL to collect values into arrays, something similar to PostgreSQL ARRAY_AGG. I already use listagg for strings, I use cast() with collect() ...
0 votes
1 answer
991 views

Why only ~half of RAM is used by Oracle on Linux server?

Why Oracle 11.2 would be using only around half of available RAM on RHEL5 box? I have 64GB but 32GB is free which really annoys me. Any memory not used is wasted memory in my books - especially on ...
0 votes
1 answer
19 views

Number of Sessions Opened in Remote Database when Executing Query with Oracle Database Link

I have a query on an Oracle database that involves accessing a remote database through a database link. Here's the query: SELECT t1.col1 , t2.col2 FROM Table1 t1 LEFT JOIN Table2@dblink t2 ON (t1.id=...
0 votes
2 answers
1k views

Unable to import DMP file that is made in 11g2 to Oracle 18c

My OS=CENTOS 7 Oracle=18c XE I am trying to import a DMP file that is exported from XE 11g2 and now trying to import it in 18c XE. Here are the errors: [root@linux gsw]# impdp user/pass ...
1 vote
3 answers
2k views

sqlldr running slow after indexes created

let me explain this.... I had ran 1 session of sqlldr while I run indexes into a table for 6 fields... now the indexes are completed but the sqlldr running very slow it loads one file after each 30 ...
0 votes
1 answer
494 views

Oracle 12c create manual script for automatic startup on RHEL7

I need to create a script to start and shut down a 12c database in rhel7 automatically when the operating system starts and shuts down, it turns out that I have tried the common options with dbstart ...
0 votes
1 answer
352 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:...
0 votes
1 answer
1k views

Oracle TDE - opening/closing an encryption wallet

I have a quick question relating to Oracle TDE. Could somebody please explain why both of the following pairs of commands appear to work when opening/closing an ecryption wallet? Is the wallet ...
1 vote
1 answer
1k views

Created a Materialized View in ORACLE which won't refresh

I created a materialized view log along with a materialized view called update_nboe based on table NBOE_ EMPLOYEES_TEST using the following code CREATE MATERIALIZED VIEW LOG ON NBOE_EMPLOYEES_TEST ...
0 votes
1 answer
796 views

Query Over Oracle DB Link Requiring global_name = true

I'm running the following queries over a private dblink (Oracle Database 19c): delete from myschema.mytable@mydblink where SERVER = (select host_name from gv$instance); insert into myschema.mytable@...
2 votes
1 answer
526 views

ORA-00957 View of JOINed tables with same column names

Is there a way to create a view of two joined tables so that all columns of both tables are in the view and there is a column of the same name in both joined tables and the columns are not explicitly ...
0 votes
1 answer
905 views

Oracle prevent CPU usage per process/session

I have an Oracle instance to which several applications from different machines connect (http servers). Normally Oracle CPU usage is ~5-10% even on high server loads. But from time to time, one of ...
0 votes
1 answer
492 views

How to we get history of sql statements of sysdba?

We have been running a list of commands in cli: sqlplus / as sysdba We did not have "history on" and we logged out of the session. How do we get the list of sql statements we ran before as ...
0 votes
1 answer
391 views

Exclude Supplemental Logs from Import

I am trying to import tables from a production environment, to a test environment. The production environment has supplemental logs on a number of tables for golden gate replication. The ...
0 votes
1 answer
19 views

It is possible to install ASM Grid Standalone without creating a DATA diskgroup first. It seem ohasd need to be install first

I have installed OEM grid standalone and have it working fine. However I would like to install GRID without having to create the DATA group first. In other words install ASM software using a local ...
0 votes
1 answer
722 views

How to create multiple container databases in 12c R1?

As multiple databases can be created under the same/different Oracle home(s) in Oracle 11g on a host, please advise me on creating multiple container databases to create multiple pluggable databases ...
0 votes
1 answer
2k views

ORA-31626: job does not exist during schema export

I'm exporting schema with following command - expdp system/[email protected]:1521/orcl schemas=schema-name directory=test_dir dumpfile=Schema.dmp logfile=SchemaLog.log but it results into ...
1 vote
1 answer
3k views

Why Oracle 19c Database EM Express Webpage is not opening in Microsoft Edge?

I have recently installed Oracle Database 19c on my Laptop which has Windows 10 OS. Now, I want to open the localhost:5500/em page in Microsoft Edge browser, but when I try to do so, the webpage does ...
0 votes
1 answer
972 views

Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628)

I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say ...

1
2 3 4 5
156