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
1answer
29 views

Using of delete obsolete in Oracle 11gR2 RMAN on Linux

I'm using crontab for automatic backup (full backup). It works 2 times in a day (8 AM and 23 PM). In my script I use delete obsolete but I have a question. I want to delete old backups without last 2 ...
0
votes
1answer
48 views

Oracle 11gR2 can I delete datafiles to reduce tablespace?

I have the following doubt, maybe a little silly, if I have a tablespace for example TBS_MYDATA of 155 GB, this tablespace is composed of 5 datafiles of 31GB each, in turn, each of these datafiles ...
0
votes
1answer
31 views

Oracle SQL query - select or rows without all ids in other tables

I need to delete customers if customer not have any accounts or all of accounts not have any operations (no records in id_customer, id_account_sender, id_account_receiver in operation table). example ...
-1
votes
1answer
40 views

How to know needed UNDO_RETENTION for a specified query of long duration? [closed]

I'd like to know whether there is some way to know the undo_retention needed for a specified query with long duration that need keep the consistence. In other words, I'd like to know how long my ...
1
vote
1answer
81 views

SQLcl: set color in sqlprompt (ANSI/VT100 Control sequences)

This is my login.sql SET sqlprompt "_user'@'_connect_identifier > " SET sqlformat ansiconsole SET serveroutput on SET lines 3000 How to set the sqlprompt to color red for Prod (and green color ...
3
votes
1answer
170 views

SQLcl history (up-arrow) rewrites my queries

Version sql -version SQLcl: Release 18.4.0.0 Production I type this: SQL> exec dbaspace.long_ops; SID % Done Start Time Rem [s] Elapsed Message ==== ======= =================== =====...
0
votes
3answers
57 views

Oracle 12c and Jobs running one hour apart after changing time for hourly use

I have an Oracle 12c database in which I have detected that the scheduled jobs are running one hour apart after the change of time on the server, checking in the database I see that the SYSTIMESTAMP ...
0
votes
1answer
48 views

Oracle Tablespace not using 100% of available space

About a year ago, we added a new tablespace to our Oracle 11g RAC database and we are now using all of the space and are running out of room occasionally. I check on the space available with this ...
0
votes
1answer
51 views

Cannot connect database as sys as sysdba using sqlplus

I am new to oracle server 18c (currently taking online course). My problem is sql*plus where I tried to use sqlplus then sys as sysdba (it did not work) and the error appeared below. However, if I ...
3
votes
1answer
64 views

RMAN - How to add date in backup tag

I know that the default of the RMAN is to add the date in the following format: TAGYYYYMMDDTHHMMSS, But if I wanted something like this: BKP_FULL_11112019. How I do this? I tried using %T but it does'...
-5
votes
3answers
67 views

Oracle trigger with checking day routine doesn't work correct [closed]

CREATE OR REPLACE TRIGGER SYS.Restict_user AFTER LOGON ON DATABASE BEGIN IF(TO_CHAR(SYSDATE, 'DAY')) = UPPER('SUNDAY') OR (TO_CHAR(SYSDATE, 'DAY')) = UPPER('SATURDAY') THEN ...
1
vote
1answer
42 views

can I do a restore controlfile from autobackup out FAST RECOVERY AREA?

I know that I can issue the command restore controlfile from "location_here". But there is some config that can I do for issue the command restore controlfile from autobackup even whether the ...
0
votes
1answer
30 views

Oracle JSON ADT in Collection

Why is the table() operator not working? create or replace type tmp_011040_t2 as table of (JSON_OBJECT_T) NOT PERSISTABLE; declare l_objects tmp_011040_t2; begin l_objects := tmp_011040_t2(); ...
0
votes
1answer
42 views

How to remove these duplicate records in an Oracle Database 12c?

I have these two tables: messages(id primary key, message_date, created_at, ...) user_messages(user_id, message_id references messages(id)) I have some duplicate rows in messages: select user_id, ...
0
votes
1answer
44 views

How To Create A New bash_profile And Connect New Database To It In Oracle [closed]

OS -> Linux DB -> Oracle 11gR2 Normally my .bash_profile file looks like this: TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_HOSTNAME=abcde.localdomain; export ORACLE_HOSTNAME ...
0
votes
4answers
66 views

Alternative to Triggers

I am working on an oracle database and want to record the exact time of any transaction happening on a specific table in the schema. Using triggers on that table is out of question as the client do ...
0
votes
0answers
41 views

ORA-01466: unable to read data - table definition has changed

What happen when is done a import from a export with this erro? I have a table that every day is altered the structure during the consistent export (flashback_time=systimestamp) and I always receive ...
0
votes
1answer
29 views

How to combine subqueries?

There are two subqueries select a set according to certain criteria, such as select id, dt_beg, dt_end from ................... where .............. it is necessary to select data from the ...
0
votes
0answers
26 views

Difference in converting varchar2 column to number in Oracle 11 and 12

I have a table tblA ( ID varchar2(5), DESC varchar2(200) ) which includes data: '1', 'One' '2', 'Two' My query is something like: select anotherTbl.ID, T.txt from anotherTbl, (select 0 as ...
0
votes
0answers
32 views

Oracle Express 18c install is hanging

Trying to Install Oracle Express 18c on Windows 10/x64 machine. Constantly hangs on "Creating Oracle Listener". Looking in the install log file does not show any error. Log files are huge, but can ...
1
vote
3answers
74 views

Are there any reasons why a table wouldn't show in sys.dba_tables? (Oracle)

I have 2 queries SELECT * FROM sys.dba_indexes WHERE Table_Name = 'XDB$RESOURCE' and SELECT * FROM sys.dba_tables WHERE Table_Name = 'XDB$RESOURCE' Are there any reasons why a table wouldn'...
1
vote
0answers
23 views

Are LOBs also backed up if I take dump of all partitions of table individually?

I am taking export of a large table in oracle through datapump. I am taking export of all partitions one by one. The table has a LOB column as well. Are LOBs also backed up with partitions? Because ...
1
vote
0answers
33 views

Does the target database need to have the same structure of the source database before to use IMPDP?

When I do a import from another database have I need the same estruture of the source database in the target database? For example, if my source database use ASM and my target database no, what have ...
1
vote
0answers
84 views

Oracle 12c Datapatch Error

We have an old Oracle 12c database server that is not up to date (version 12.1.0.1.0). Unforunately a full version upgrade is out of scope right now, but I was hoping to at least apply the latest ...
0
votes
1answer
41 views

How to query Last inserted row for each group in oracle 10

I have two tables about users where I want to join two tables and show the latest remarks of each user bellow is table I want to query the latest remarks for each user (1 remark per user) bellow ...
0
votes
0answers
34 views

Case inside case in oracle query

Can anyone help me? I want to use query using CASE WHEN with logic where time less than 03:00 PM on Monday until Friday will get value start from tomorrow 03:00 PM until today 03:00 PM SELECT ...
0
votes
0answers
29 views

Why expdp does not include identified by statement?

I have the following error when I do a impdp (from a pdb in a 12c to 11g. I also used the parameter version=11.2.0) IMPDP LOG: ORA-01938: IDENTIFIED BY must be specified for CREATE USER Failing sql ...
1
vote
0answers
18 views

Can multiple process pull off the same record from an Oracle FIFO queue?

My understanding of Oracle AQ (Advanced Queuing) is that no two (or more) processes can pull off (drain/consume) the same record of an Oracle FIFO queue. Is this correct or is some code needed to ...
2
votes
1answer
82 views

ORA-00904: Invalid Identifier when selecting from view [closed]

I am attempting to view a view with: select * from DISCOUNT_AMOUNTS; ERROR at line 1: ORA-00904: "SALE_GIFT_VOUCHER_AMOUNT_AMOUN": invalid identifier I then check the view to see what is there in ...
1
vote
2answers
47 views

Export SQL query output to a file in a scheduler job

I need to export data to a csv file in a scheduler job. Here are the commands: SPOOL "\\AServerName\Output\RMS_DataFileSizes.csv" REPLACE; SELECT /*csv*/ TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS ...
0
votes
0answers
32 views

Oracle 11.2.0.1.0 showing “no matching authentication protocol”

I have installed the above oracle version (personal edition) for my developmental purpose. But when I try to login through sqlplus, always it throws me the error no matching authentication protocol. ...
-1
votes
1answer
45 views

Oracle database - how to switch instance

I'm totaly new in oracle, so sorry for my probably stupid question... I have two instances on oracle server, when I'm connecting to the server via sqlplus in default opens connection to instance X but ...
0
votes
1answer
51 views

How to get TNS connect strings (like tnsping) to check a dblink via a session?

I would like to know, if there is any chance to get the currently used TNS connect string, that will be used for a DB-Link from a db-server. I'm connected to a schema on a database located on any ...
0
votes
1answer
41 views

It's a good practice always restore controlfile before restore the database?

I'm beginner in Oracle, but I'd like know if it's a good practice always restore the control-file of a backup-set before restore the database. I ask this because always when I restore the control-file ...
1
vote
0answers
31 views

ORA-02063 Encoding problem with Oracle querying SQL Server through ODBC link

We have a problem querying some data from the SQL Server from the Oracle database with the ORA-LNX connecting to SQL-SRV using ODBC and Heterogeneous Service (Oracle HS). When we send the query ...
3
votes
1answer
52 views

Patching after Oracle 12c install? [duplicate]

I am not able to find concrete information about patching after install. So I have downloaded Oracle 12cR2 software from Oracle and I have successfully installed it. But now I am wondering if I need ...
1
vote
0answers
94 views

Connecting to SQL Server 2017 from Oracle SQL Developer

Pretty much as the title says, has anyone tried connecting to SQL Server 2017 via Oracle SQL Developer? I downloaded the latest jTDS (jtds-1.3.1-dist) but this only seems to support SQL Server 2012 (...
0
votes
3answers
58 views

Error messages while migrating from Oracle to MSSQL [closed]

I got these error messages when migrating from Oracle to MSSQL (Unfortunately, the error messages are in German languages) Verletzung der PRIMARY KEY-Einschränkung "DTR_PK". Ein doppelter Schlüssel ...
0
votes
1answer
30 views

Does canceling update operation perform automatic rollback

I ran into a bit of confusion. I'm under the belief that database operations are automatic - that is either the query succeeds or the transaction is automatically rolledback. For example - if you have ...
0
votes
1answer
23 views

Toad for Oracle: Procedures, Packages, Job, and DBMS_SCHEDULER

If I have about 10 queries which contain subqueries, would it be a best practice to contain all of them in one procedure? This script creates a small fraction of the data required for a report. DROP ...
0
votes
1answer
39 views

Automating SQL scripts in Toad for Oracle (13.1)

I'm not a DBA, so bear with me. I've scheduled tasks through Toad's automation designer, but this uses Windows task scheduler. It's using Oracle 12c. All I'm doing is updating (creating/dropping) ...
-1
votes
3answers
117 views

Oracle data file size

My client’s tablespaces are autoextend off and i add monthly or bimonthly 3 or 6 gb space for tablespaces. Can you advise me if there is a recommended datafile size for Oracle 11gR2 on Windows 64 Bits?...
0
votes
1answer
43 views

How to extend oracle database to a newly added hard disk

I'm a begineer to Oracle database admin role. Recently I have added a new hard drive to extend the capacity of an oracle database which runs on Linux. How to extend the oracle database to utilize new ...
1
vote
1answer
41 views

Patching after upgrade (windows 11gr2 -> 12cR2)

I have a following scenario where I had a patched oracle 11gR2 DB (applied Windows Bundle and OJVM patches) and then it was upgrade to Oracle 12cR2. 12cR2 was a clean installation (no patches applied, ...
1
vote
1answer
140 views

ORA-39097: Data Pump job encountered unexpected error -942

Would somebody know what is that error? This is during the impdp command and there is nothing imported. The full error is: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - ...
0
votes
2answers
41 views

How to add time from a Date value to another without a varchar conversion?

Let's say I have 2 variables in a Stored Procedure, both are Dates Foo contains '13/10/2019 00:00:00' Bar contains '01/10/1914 16:33:11' I want to add Bar's time to Foo's date, so that I get a ...
0
votes
0answers
65 views

How can I import the fga_log$ table with impdp Oracle 12 R1

I am trying to import with impdp a save made as follows: expdp '"sys/mmm as sysdba"' full=y COMPRESSION=ALL DIRECTORY=LOGICS_EXPORTS COMPRESSION_ALGORITHM=BASIC LOGTIME=ALL CONTENT=ALL DUMPFILE=FULL-...
1
vote
1answer
31 views

Oracle Database 11g Failing to mount on windows server 2012

I have an Oracle 11g database installed on Windows Server 2012. It's failing to mount after shutdown. The reason why i want to mount it is that i want to enable Archive log mode, and i have been able ...
1
vote
0answers
58 views

Oracle 12c Database Config Manager very slow at creating a new database

I have installed Oracle 12c on a number of machines (Windows 10, 2012, 2016 mostly) before and created several databases on them, using the Database Config Manager, all with no real hiccups. However ...
0
votes
1answer
21 views

Moving columns based in last analyzed date

I have to create a report to summarize some metric results over the last 12 months by account, each time a metric is processed is added to the account attainment with the last 12 months results. The ...