New answers tagged

0

Ok, so you have no backup. You made sure it is not a permission issue. But you have Data Pump dumps, at least that is something. First try to recover the datafile without data loss: startup mount alter database datafile 'V:\DB\CST001.DBF' online; recover datafile 'V:\DB\CST001.DBF'; Answer the prompts accordingly, if any. If this succeeds, you can open ...


0

Try this It will really help you to recover your database. copy D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL.old copy D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL SQL>startup nomount SQL>create ...


0

Closest thing that I know is to overload a function... create or replace package test_pkg as function variableParams ( a in varchar2, z in integer, y in integer ) return integer; function variableParams ( a in varchar2, z in integer, y in integer, x in integer ) return integer; end; / create or replace package body test_pkg as function ...


1

DECODE is an SQL function, not PL/SQL. You can not use it in plain PL/SQL, only as part of SQL statements. There is no nice and easy way for this. There is the method with collections, for example: create or replace type t_numbers as table of number; / create or replace function sum_plsql (p_numbers t_numbers) return number as rv number := 0; begin ...


1

In Oracle a schema and a user is the same thing. In Postgres it isn't. So there is no direct "mapping" on what you did in Oracle in "Postgres land". A table is always owned by the user who created it initially there is no way to change that. If you do not want to give the user ink the privilege to create tables, the "Postgres" way would be to give that ...


0

Give ink the right to create tables in the database and connect yourself as ink to the database. Then all the tables that you create there are owned by ink. PostgreSQL has a different approach on this then Oracle. The schema and owner are 2 different 'things'.


0

Are all of your parameters set up properly for data guard? On your primary site, FAL server is your standby DB and client is your primary. That should be flip flopped on the DG site. And what about your archive destinations? Are they set up to ship logs over to the standby? Are they enabled? If you do the follow: show parameter fal show parameter ...


-1

Looks like it's windowing what makes Oracle to start evaluate all expressions in CASE. See create table t (val int); insert into t select 0 from dual; insert into t select 1 from dual; insert into t select -1 from dual; select * from t; select case when val = -1 then 999 else 2/(val + 1) end as res from t; select case when val = -1 then ...


0

I tried creating /tmp which was not existing as a separate file system and kept 4gb of space. But the issue did not get resolved. I try to keep my temporary file in a different place where the space available around 20gb. Still issue did not get resolved. Finally I had to run the below command which resolved the issue. I couldn't find the solution so ...


0

Here are the main steps and links to the appropriate manuals: If you want to change the ORACLE_HOME this means you want to change the location where the Oracle software is installed. This is described in the Database Installation Guide of your Oracle tralease and your operating system. Either you install the Oracle software in the new ORACLE_HOME or you ...


0

Sorry it was a such a dumb thing to ask, but again if this is a dumb answer please feel free to delete it. I've used the intersect instead of inner joining the two queries as subqueries.


3

It's not really a requirement. The database itself will work perfectly without backup after switching between ARCHIVE / NOARCHIVE mode. But if something ever goes wrong (or database needs to be copied/cloned to other machine), you'll need a backup. You may or may not use incremental backups feature ; if you do want to use them you must take 0-level ...


3

You cannot use backups from before the switch to ARCHIVELOG mode to restore and recover the database to a point in time after the switch. Looking at this quote it makes sense. If you enable archivelog on a database where it was not enabled, then any backup that you have before archive log was enabled can't be used to do a point in time recover, since ...


1

This Error can happen with PL/SQL and came up with ORA-600 [kqlidchg0],ORA-00001 and/or ORA-01578 see MOS Doc ID 17210525.8. For 12.1 and 11.2 install latest Patch (April 2016) ORA-600 [kqlidchg0] with PL/SQL ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated Try: ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';


1

Looks like your oracle directory doesn't exists. You will need to log into the database, create a directory, and retry your import export ORACLE_HOME=/path/to/oracle/home export ORACLE_SID=YOUR_SID sqlplus / as sysdba create directory DUMPS as '/path/to/your/dump/files'; exit; And retry your import. Pending on the user you are using to do the import, ...


3

You could use some kind of row generation technique, for example: select rownum - 1 as q from dual connect by level <= (select max(controlnoto - controlnofrom) + 1 from booklet) This generates as many rows as the largest range requires, with data 0, 1, 2, ..., starting with 0, so no correction needed, when we add these values to controlnofrom. Now ...


2

So it was hard for me to determine what your actual question was from the post, but I assume it is that when you execute: SELECT ref_no, CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0 ELSE CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN MAX(amount) OVER (PARTITION BY ref_no ORDER BY paid_date ASC ...


1

SQL defines what to do, not how to do it. While normally Oracle will short-circuit case evaluation, this is an optimization and therefore will be avoided if the optimizer believes a different execution path provides superior performance. Such an optimization difference would be expected when analytics are involved. The optimization difference is not ...


1

The concatenation is occurring before the addition causing it to fail trying to add a string to a number. Concatenation and addition both have the same level of operator precedence, but are processed from left to right, so if you had removed the leading concatenation, the trailing one would succeed. As Balazs explained, adding parenthesis fixes the order ...


0

RAC works on a quorum basis. For the server to be active it must belong to a cluster with quorum (over half the votes). With a two node cluster, you need unequally shared votes. One way to attain quorum is to use ASM. As ASM is on the first node, the second node can't use that for quorum. Try starting ASM on the first node, then the database on the ...


0

Something like: select city_name from ( select city_name , row_number() over (order by length(city_name) asc, city_name) as rn1 , row_number() over (order by length(city_name) desc, city_name) as rn2 from station ) as T where rn1 = 1 or rn2 = 1 The idea is to create an order number based on smallest length and in case of a tie, ...


0

Is this what you're looking for? It's just your comment query with a little bit added SELECT city_name,length(city_name) FROM station WHERE LENGTH(city_name) = (SELECT MAX(LENGTH(city_name)) FROM station) OR LENGTH(city_name) = (SELECT MIN(LENGTH(city_name)) FROM station ) ORDER BY (city_name) DESC, city_name; This was done on a MySQL instance, but ...


0

You cannot put the datafiles in Offline when your DB is in No Archive Log mode. So below are the steps to recover the database considering current situation : Shutdown immediate; startup nomount; Recreate the control file using backup trace. : alter database backup controlfile to trace as '$ORACLE_HOME/dbs/control_new.sql'; Now make changes in the ...


1

The standard approach to get rows N through M is to do something like SELECT * FROM (SELECT a.*, rownum rnum FROM (SELECT emp_id, last_name FROM employees WHERE positionID in (1,3) ORDER BY <<something>>) a WHERE rownum <= 60) b WHERE rnum > 50 Note that you need ...


0

INS-30131: Initial setup required for execution of installer validation failed Cause: "Failed to access the temporary location". This error occurs when you lack of space on /tmp directory. You should allocate space on /tmp according to the Installation Guide Also review this Oracle Support Document Doc ID 2092409.1


2

Archiving the Database Audit Trail Scheduling an Automatic Purge Job for the Audit Trail Manually Purging the Audit Trail Basically you copy the audit trail to another table, e.g: INSERT INTO table SELECT ... FROM SYS.AUD$ ...; INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ...; You can just export and drop these tables, then import them when you need ...


0

certainly it can be compressed after it is created. The only way to know by how much is to run a test. Just keep in mind that the compressed version is not really the same file - not the same internal format - that oracle's exp (expdp) created, and so will need to be uncompressed before being used by imp (or impdp).


1

The Optimizer takes the parsed representation of SQL statement and Statistics to generate final execution plan with the lowest cost. During this process the Optimizer generates multiple plans and compares them. Execution plans may change as the Optimizer inputs(Parsed SQL Statement and Statistics) get changed. Why Execution Plans Change Execution ...


0

You write "I don't need the time zone information", however datatype TIMESTAMP WITH LOCAL TIME ZONE has time zone information stored. For a TIMESTAMP WITH LOCAL TIME ZONE column I don't see any difference between CURRENT_TIMESTAMP and LOCALTIMESTAMP either of them should work in the same way. CURRENT_TIMESTAMP returns current data and time as of datatype ...


1

That's exactly the kind of thing that oracle AUDIT is created for. The basic view is DBA_AUDIT_TRAIL, though there are several others. By defalt, Oracle has some auditing already enabled, but you may want to review and modify. Also you want to use the dbms_audit_mgmt package to move the audit tables out of the SYSTEM tablespace into their own dedicated ...


2

Can nologging in certain tables boost the performance? Not for a normal transactional load. nologging only affects some very specific cases of DML (essentially direct-path inserts) and some DDL (see logging_clause). So essentially only for bulk data-loading scenarios. If that's not what your database is doing, it won't help at all. If it is what your ...


0

I will write the solution here for those who might encounter such error in the future. I solved the error by re-compiling invalid objects.


0

Single quotes inside single quotes need to be duplicated: default '' '' not null CREATE OR REPLACE PROCEDURE CREATE_TABLE AS strTABLE_NAME varchar2(32); BEGIN strTABLE_NAME := 'Inventory'; execute immediate 'create table '|| strTABLE_NAME || ' (STORE VARCHAR2(4) DEFAULT '' '' NOT NULL)'; END CREATE_TABLE ; See the manual for ...


-1

transpose: create view transpose as select j,i,val from a; multiplication: select a.i,b.j,sum(a.val*b.val) from a,b where a.j=b.i group by a.i,b.j;


0

On the database server, issue the command 'lsnrctl status'. You should get a list of database services known to the listener. There should be one for your database. It is the listed 'service name' that should be the subject of the 'SERVICE_NAME parameter in your tnsnames.ora. There is a more complete explanation of your error at ...


0

DBTIMEZONE is different than host machine timezone, and it needs to be set up separately (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions039.htm) . The first step is to change DBTIMEZONE to Moscow so it matches server settings which always makes sense. As a side effect, you will have your current issues "solved" because now db ...


0

Is the DBMS Output disabled by the DBA? Try running this in SQL*Plus SET SERVEROUTPUT ON SIZE 30000; EXEC PROCEDURE_1('SYN_NAME'); --replace with name of synonym you want to find You can also add DBMS_OUTPUT.ENABLE (buffer_size => NULL); to your procedure like this CREATE PROCEDURE PROCEDURE_1 AS strName VARCHAR2(50); CURSOR C_NAME IS SELECT ...


0

No grant needed. You do not see any results, because output is disabled by default. Enable output before running your code: set serveroutput on


1

You shouldn't need any special privilege to view the ALL_* views. If the procedure is owned by and is running as USER1 then it could be that USER1 has not been granted access to any synonyms. If the procedure is owned by a user other than USER1 it could be that that user hasn't been granted access to any synonyms. It all comes down to who's credentials ...


2

The installation of a PSU is not complete without the execution of the postinstall script. Opatch works at file level, it replaces/relinks files in the Oracle home. Changes inside the database are performed by running the postinstall script. Sometimes I see DBAs forget this step. The postinstall script maintains the SYS.REGISTRY$ table (on which the ...


1

You can install the latest version available at the time of purchase, but no newer versions after that. The "funny" part is, these versions (like 11.2.0.4) can be obtained from https://support.oracle.com/, but you can not download them, because you do not have a support contract. In this case, when you request the installation media, an Oracle ...


0

SYSTEM is a typical database user (such as HR or OE) The DBMS_LOB package belongs to SYS schema so that only the owner(Or user having the GRANT OPTION) can grant the object privilieges to other users. So if you connect as SYSDBA using SYSTEM user you will be connected to SYS schema. SQL> conn system/oracle Connected. SQL> show user USER is ...


1

I have done this: SELECT MOD.MODULE_NAME, MA.MARK FROM MODULES MOD INNER JOIN MARKS MA ON(MOD.MODULE_CODE = MA.MODULE_CODE) WHERE MA.STUDENT_NO = ( SELECT DISTINCT S.STUDENT_NO FROM STUDENTS S WHERE S.SURNAME = 'Lothbrock' ) AND MARKS.MARK = ( SELECT MIN(MARKS.MARK) FROM MARKS ) ORDER BY MA.MARK; Now I just have to figure out ...


0

It is very recommendable to do a dump in another media of all your database before trying anything else, because your filesystem is probably not going well. You will need to restart the server and run fsck not only because of that file is corrupted, but because the risk of more data corruption, then try to run sqlplus again, if it does not help, you will ...


-1

run given command.this will kill all process associated with oracle. like smon , pmon,dbwriter process. kill -9 ps -e | grep ora_* | awk '{print $1} ### and start again with . oraenv & sqlplus / as sysdba


1

You can achieve the same goal by having a UNIQUE composite index on the desired fields. ALTER TABLE YourTable add CONSTRAINT YourTable_unique UNIQUE (A, B,....); (A, B, ...) should be NOT NULL. This might be helpful if you have a surrogate PRIMARY KEY which you may want to keep. This was recently asked for MySQL here, but the principle is the same. ...


2

Sure, just create a job. For example, update column every midnight: begin dbms_scheduler.create_job ( job_name => 'UPDATE_TABLE', job_type => 'PLSQL_BLOCK', job_action => 'begin update table set column = value; commit; end;', start_date => trunc(sysdate+1), repeat_interval => ...


0

Yes you can. If you define composite primary key then one of the filed should contain unique value. Composite Primary Key Example: SQL> create table t1(id number, dt date,constraint pk_t1 primary key(id,dt)); Table created. SQL> insert into t1 values(1,sysdate); 1 row created. SQL> insert into t1 values(1,sysdate); 1 row created. SQL> ...


0

@Balazs Papp's answer is of course correct. Oracle has not implemented CREATE ASSERTION and no other major DBMS has. The specific assertion though can be as easily implemented with a simple UNIQUE constraint: ALTER TABLE collaboration ADD CONSTRAINT ir_collaboration UNIQUE (ngo, id_employee) ; No two rows with same ngo and same id_employee will be ...


0

All you need to do is swap the indexes. Original: with A as ( select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9 ) select * from A order by 1, 2 ; I J VAL --- --- --- 1 1 1 1 2 2 1 3 3 2 1 4 2 2 5 2 3 6 3 1 7 3 2 8 3 3 9 Transposed: ...



Top 50 recent answers are included