Hot answers tagged

4

You have just ended the single quote near 'cus_id in(select cus_id from employee where comp_id = NVL(SYS_CONTEXT(' so that the compiler is expecting the ; to end the statement but it has found Hello_Context','COMP_ID'),0)');. If you want to use single quote inside another single quote you can use q operator as demonstrated below. For details:Literals ...


3

Why would this not be sufficient? SELECT * FROM ( SELECT MOD.MODULE_NAME, MA.MARK FROM MODULES MOD JOIN MARKS MA ON (MOD.MODULE_CODE = MA.MODULE_CODE) JOIN STUDENTS S ON (S.STUDENT_NO = MA.STUDENT_NO) WHERE S.SURNAME = 'Doe' ORDER BY 2) x WHERE ROWNUM = 1;


3

The columns ABSENT, LATE and PRESENT in your output represent totals per student, whereas the date columns represent details per student, and details are the pivoted columns. In order to get such output with PIVOT, your source must provide totals on the same row with details. The source in your query is not providing that kind of information. It is grouping ...


3

Oracle, in contrast with the name, can not predict the future. If you have a retention policy configured as recovery window of 14 days or redundancy 2, the database does not know when your next full backup will succeed. Just because you have a full backup scheduled on every Saturady, that does not necessarily mean that backup will succeed and old backups ...


3

You move tables, not indexes. You rebuild indexes. However, when you rebuild indexes, you can specify a tablespace, not a datafile, so no difference in that aspect. Since you have to rebuild the indexes anyway, instead of rebuilding them immediately (and not having control in which datafile the new extents will be placed), you can just simply make the ...


3

If your aim is only delete rows from table1, you can re-write your query as follow: DELETE FROM table1 p WHERE EXISTS( SELECT 'MYROW' FROM table2 e JOIN table3 d ON d.col4 = 'IO' AND d.col5 = -1 AND e.col3 = d.col6 WHERE e.col1 = 'YU' AND e.username = p.username AND p.col2 = d.col3 ); You convert your ...


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 ...


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 ...


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 ...


2

In SQL*Plus, you can change the pagesize SQL> set pagesize 100 will repeat the headings every 100 rows rather than every 15. You can set the pagesize value to (just about) whatever you'd like. If you want to ensure that the headers are only written once, you can set it to a number much greater than the number of rows that the query will return.


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 ...


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 ...


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 ...


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 ...


2

Partial answer (select SQL Server tables from Oracle): On the Oracle side, you can create a database link to view data in SQL Server. Since this is a 5 step process, I've also included some debug steps that I use when diagnosing db links from SQL Server in order to track down where the failure is. On the Oracle machine: 1) Set up an ODBC connection to ...


2

Yes it is possible to use single tenant.. here you go --> Single Tenant Configuration


2

Setup: create table t3 (c1 number, c2 number); alter table t3 add supplemental log group sl1 (c1, c2) always; alter table t3 add constraint t3_uk unique (c1, c2); insert into t3 values (1, 2); commit; Try to drop the column: SQL> alter table t3 drop column c2; alter table t3 drop column c2 * ERROR at line 1: ORA-12991: ...


2

Oracle on Windows creates a service for database instances, called OracleService%ORACLE_SID%. By default, this service uses the Local System account so a password change of the installation user will not affect the database. It is however possible and recommended to change the service user from Local System to a standard Windows account. If you performed ...


2

There is no trcroute for the instant client, but even if you had the full administrator client, trcroute may not return anything useful. By the way, from the command line I can ping the destination DB (defined in tnsnames.ora) just fine, so I believe I can completely rule out the networking (e.g. DNS/firewall/accessibility). And that is the mistake ...


2

You cannot revoke the public pseudo-role from users. Every user always has public. You can revoke privileges from the public role. The first question would be whether these privileges were granted by default by Oracle, in which case revoking them might prove difficult. It might, for example, break upgrades and patches and/or be re-granted by future ...


2

I've gone for a simple sub-query approach partly because I like the ROW_NUMBER functionality. So this lists the marks per module is ascending order in the inner query and then just returns the values for the first row number returned. SQL> select * from wg_student 2 / STUDENT_NO SURNAME FORENAME ---------- -------------------- ...


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 => ...


2

The way you have acquired the tablespace size is incorrect. MAX_SIZE(NUMBER) returns Default maximum size of segments (in Oracle blocks). You can query dba_data_files and dba_free_space to get the result Following is the exceprt of monitoring script that we have been using. SELECT * FROM (SELECT a.tablespace_name, ...


2

According to the question all you need to do is attach a predicate on query to customer table as shown below. select * from customer where company_id in(select company_id from user_info where Admin='admin_user'); Right? So you have to create a function which should return a predicate as: company_id in(select company_id from user_info where ...


1

When we put the tablespace in backup mode Oracle copies whole changed data blocks into the redo stream in order to make the backup safe from fractured block. Copying whole data block instead of only the change may degrade the performance. Oracle strongly recommends to put the tablespace in backup mode only when required. What Happens When A ...


1

Looks like the JDBC 11g NAT+RAC issue. JDBC Connections Using SCAN Fail With ORA-12516/ORA-12520 (Doc ID 1555793.1) JDBC thin 11g connections using Single Client Access Name (SCAN) fail with errors: ORA-12520: TNS:listener could not find available handler for requested type of server ORA-12516: TNS : listener could not find instance ...


1

You cannot recover the committed transaction with a normal privilege. However, you may be able to ask your DBA to flashback(If the database is set up with flashback enabled for tables and UNDO_RETENTION has set approximately)the table to a specific point in time, or,assumin they are using hot backups, Database Point-In Time Recovery(This option will also ...


1

I am using these two functions in my application: CREATE OR REPLACE FUNCTION Ip2Decimal(IP IN VARCHAR2) RETURN INTEGER DETERMINISTIC IS DecimalIp INTEGER; BEGIN SELECT SUM(REGEXP_SUBSTR(IP, '\d+', 1, LEVEL) * POWER(256, 4-LEVEL)) INTO DecimalIp FROM dual CONNECT BY LEVEL <= 4; RETURN DecimalIp; END Ip2Decimal; and CREATE OR ...


1

Note that if your listener has the domain name included you may need to alter the oracle init parameters SERVICE_NAME and DB_DOMAIN depending on what is changing in the host name/ service name. Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File ...


1

The first query you have used returns Cartesian product of the rows from these three tables. For details: Join The following is the simple example to join three tables. SQL> select * from student; STUDENT_ID FIRSTNAME LASTNAME ---------- -------------------- -------------------- 1 Will Smith SQL> select * from module; ...



Only top voted, non community-wiki answers of a minimum length are eligible