Hot answers tagged

5

NUM is not a reserved word. The best way is not to use reserved words. If you insist on using them, you can put them between quotation marks. This does not work: SET SERVEROUTPUT ON declare begin number; begin begin := 10; DBMS_OUTPUT.put_line('The value of begin ' || begin); end; / This does: SET SERVEROUTPUT ON declare "begin" number; ...


4

ORA-00913 too many values Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: ...


4

Seems like you need all combinations of products and customers (so a CROSS JOIN between the two tables) and then a correlated EXISTS subquery. This could either be a correlated subquery in the SELECT list: SELECT c.customer_id, p.product_id, CASE WHEN EXISTS ( SELECT * FROM "order" o WHERE ...


4

The simple answer is.... To enforce an order in ANY resultset, not just your specific case, you have to use an order by clause. The Oracle documentation spells it out clearly: Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve ...


4

As has already been mentioned more than once, you cannot expect rows to be in a certain order without specifying that order explicitly using the ORDER BY clause. For the problem described in your question, you actually do not need a UNION at all. Use only the LIKE condition to cover both full and partial matches: WHERE name LIKE '%roma%' Then use a ...


3

You can use ORA_ROWSCN pseudocolumn to find rows that have been changed since last update. By default it tracks changes by database block, not by row, but it can be used to limit the amount of data transferred between databases. I would suggest a following procedure: Find the current Oracle SCN (System Change Number) select ...


3

tnsnames.ora doesn't "come with" anything. The key is not that file, as it is nothing but a names resolution file. And sqlplus is nothing but a CLI application. If you want to connect to an Oracle database (or ANY database) you must have, at a minimum, the client software for that particular database product. For oracle you need to download and install the ...


3

You are only printing the last row, not every row: You need to move the dbms_output inside the loop: create or replace procedure Display is cursor ABC is select empno, ename, sal from emp where deptno=10; emp_rec ABC%rowtype; Begin Open ABC; Loop fetch ABC into emp_rec; exit when ABC%notfound; --- the output needs to be ...


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


3

You can calculate a RANK/ROW_NUMBER based on this logic: ROW_NUMBER() OVER (PARTITION BY IMMS_ID ORDER BY CASE WHEN status='DI' THEN 1 WHEN status='OI' THEN 2 WHEN status='FI' THEN 3 WHEN status='OV' THEN 4 WHEN status='FV' THEN 5 END)


3

I write a bash script to perform the backups (RMAN, datapump and also xtrabackup and dump for MySQL in a similar way) and then call that from cron. That way you can handle the email as you want. So a simple crontab line is like this 00 18 * * 0-6 /dba/bin/rman.sh > /tmp/dba_rman_SID.log 2>&1 Then everything is done in the script. So define ...


3

No, you would have to use separate parameter files/spfiles for each database. As you've pointed out, you'll need some settings to differ (locations of files, database name, etc.). In many environments, you'd want to adjust memory and other performance settings differently because the development environments often don't have nearly as much RAM as the ...


2

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';


2

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


2

Provided customer_id and p.product_id are primary keys in customer and product select c.customer_id, c.name, p.product_id, nvl((select 'Y' from dual where exists (select null from order o where o.product_id=p.product_id and o.customer_id=c.customer_id)),'N') order_stat from customer c full outer join product p on 1=1


2

ORA-02256: number of referencing columns must match referenced columns Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list. Action: Make sure that the referencing columns match the referenced columns. The problem is the foreign key should reference both columns used in the ...


2

Customers come and go. Sometimes they have the right to take their data with them. To extract a subset of data from one schema where the data could be anywhere and the only marker might be a tenant number is a lot harder than putting each customer's data in one schema. One solution I have seen used is to have a master schema with the metadata concerning ...


2

You can use the EXCEPT keyword: CREATE TABLE #T1(c1 char(1), c2 int) go INSERT INTO #T1 Values('A',1) INSERT INTO #T1 Values('A',2) INSERT INTO #T1 Values('A',3) INSERT INTO #T1 Values('A',4) INSERT INTO #T1 Values('B',1) INSERT INTO #T1 Values('B',2) INSERT INTO #T1 Values('C',1) INSERT INTO #T1 Values('C',3) INSERT INTO #T1 Values('C',4) INSERT INTO #T1 ...


2

I found the issue. It is documented an oracle note (Doc ID 1086519.1). I used the first workaround listed which is to set CURSOR_SHARING to exact. ALTER SESSION SET CURSOR_SHARING=EXACT;


2

select (select sum(value_) from a) - (select sum(value_) from b) from dual;


2

NULL value Absence of a value in a column of a row. Nulls indicate missing, unknown, or inapplicable data. A null should not be used to imply any other value, such as zero. There are no alternate definitions of NULL in Oracle. A column can't take less space than NULL unless the column doesn't exist at all. Also, if a null value is in the last ...


2

Kris Johnston said in a comment: nomount/mount/open are the states of the database... something can't connect to the db one way and connect as nomount, and connect to the same db a different way and connect as mount or open. It doesn't work like that. It sounds like you are connecting to a different database altogether. To check, verify the dbid's are ...


2

I think the article Raj quoted (https://www.pythian.com/blog/analyze-index-validate-structure-dark-side/) describes this pretty well. "clustering factor" was also my first guess while reading the description of your problem. I also prefer to use RMAN to check for corruption. RMAN> backup check logical validate database; Afterwards you can query ...


2

UPDATE: What Worked... So after reading the link from @Raj and reading @ora-600's answer I tried to validate the database with the RMAN command backup check logical validate database;. While this worked fine, it was also clear that it was not looking at everything that the ANALYZE INDEX command would. After trying many different variations, I finally ...


2

Excellent points by @joishi-bodio. Here's a simple diagram explaining the different entities, attributes, and their relationships in your model. The first step in modeling any database is to identify the real-world entities; the attributes that define each entity; and then their relationships and cross references. Here are different observations and ...


2

perhaps you looked at flashback queries, what you need are restore points. Create a guaranteed restore point before starting test Perform test, measure, save results startup mount the db, flashback database to restore point drop and re-create guaranteed restore point. lather. rinse. repeat It really doesn't get easier than that. I do it multiple times a ...


2

Sure you can, you can append the SAMPLE clause to your selects: For example: select * from table sample(20); sample_clause The sample_clause lets you instruct the database to select from a random sample of data from the table, rather than from the entire table. BLOCK BLOCK instructs the database to attempt to perform random block ...


2

Do I understand it correctly that you have a production database with 1mil++ rows in some tables and you want to reduce the amount of rows in your test/development database? This is pretty tricky and there is no feature which does this "in a few clicks". First of all you should analyze the relations between the tables. If you have 100 tables with a small ...


2

Reading the explanation in the question, it seems that you don't really care about the ordering but you want to know if for the same name there is at least one row with status = 'ACTIVE'. This can be done with a window function, using OVER (PARTITION BY name). The CASE expression has a default of NULL so MAX() will return null if there is no such value. ...



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