removing rows with 5 numbers in a 6 number field

create table SAA_RESP_STRAT_BILL19dec as select * from SAA_RESP_STRAT
--5136 sur 203246 = 198110
select count(1) from SAA_RESP_STRAT
delete from SAA_RESP_STRAT where cfam in
(select distinct cfam from SAA_RESP_STRAT where length(cfam)=6 )
commit
select 203246 - 5136 from dual

to be updated

http://www.techiegyan.com/?p=182


with sqlplus i create db link:create database link xxx.yyy.zzzconnect to uuu identified by pppusing 'xxx.yyy.zzz';tnsnames.ora on the server has a nentry :xxx.yyy.zzz = ....in sqlplus i select:select * from table@xxx.yyy.zzz;ERROR at line 1:ORA-12154: TNS:could not resolve service name-----I can manually connect uuu/ppp@xxx.yyy.zzz;I can select * from ttt;-----So what am I forgeting???????
marist89
01-16-2003, 04:11 PM
Something is probably messed up with either your tnsnames.ora or sqlnet.ora on the server. Create the database link with the full description from your tnsnames.ora like:create database link xxx.yyy.zzzconnect to uuu identified by pppusing '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = jeffhpc)(PORT = 1521)))CONNECT_DATA =(SERVICE_NAME = nt817.us)))';


SELECT ‘ALTER TABLE ‘substr(c.table_name,1,35) ‘ DISABLE CONSTRAINT ‘constraint_name‘ CASCADE;’ FROM user_constraints c, user_tables u WHERE c.table_name = u.table_name;

dblinks and tnsnames error

Tenho q analisar mais isso.


tnsnames.ora

MAP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.112.23)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MAPE)
)
)


MAP.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.112.23)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MAPE)
)
)

tanto antes como esse:


MAP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.112.23)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MAP) -- nao existe
)
)


MAP.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.112.23)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MAP) --nao existe
)
)

Resultados do
select * from all_db_links where username='MAPDBA'

owner
PUBLIC
db_link_name
MAPE_PROD.WORLD
username
MAPDBA
host
MAP 7/12/2005 10:54:04


Erro de tnsnames:
TNS: could not resolve service name.



PQ?

FindinOracle quick script

SELECT
T.TABLE_NAME,
C.COLUMN_NAME
FROM
USER_TABLES T, USER_TAB_COLUMNS C
WHERE
T.TABLE_NAME = C.TABLE_NAME
AND C.COLUMN_NAME LIKE '%ENT%'

Constraints

Coisas estranhas que descobri com constraints

1
desabilitei em lote varias constraints de varias tabelas, alguns erros aconteceram por que existia uma ordem. Nao sabia disso. qual seria?

2
Eh necessario o uso de tablespace temp para habilitar.

3 Tentei habilitar uma constraint com o seguinte nome:

alter table RF02_PAYS enable constraint FK_RF02_PAYS _RF01_ZONE

se prestar atençao tem um espaço apos pays

so funcionou assim:

alter table RF02_PAYS enable constraint "FK_RF02_PAYS _RF01_ZONE"

para renomear fazer assim:

ALTER TABLE table_name RENAME CONSTRAINTconstraint_name TO new_constraint_name;

alter table RF02_PAYS rename constraint "FK_RF02_PAYS _RF01_ZONE" TO FK_RF02_PAYS_RF01_ZONE

Ora-01652 : Unable to extend temp segment by 128 in tablespace

Erro ORA-01652 não é possível estender o segmento temp. em 64 no tablespace TEMP Luciano 3 resposta(s)
Olá pessoal, estou tentando executar um select no servidor e estou recebendo essa mensagem de erro:
"ORA-01652 não é possível estender o segmento temp. em 64 no tablespace TEMP"

O que posso fazer para resolver esse problema ??

Atenciosamente,

Luciano Henrique

Enviado por: Chiappa em 25/11/2004
Como referido nos manuais de Admin, o procedimento seria :

ALTER DATABASE DATAFILE 'pathenomedodatafileaaumentar' RESIZE nnn;

ou

ALTER TABLESPACE nomedatablespace ADD DATAFILE 'pathenomedoNOVOdatafileaaumentar' SIZE nnn;

onde nnn é o tamanho que vc quer. Pra consultar dados da tablespace e dos datafiles, vc faz :

SELECT * FROM DBA_TABLESPACES;

e

SELECT * FROM DBA_DATA_FILES;

(ou, se a tablespace estiver usando tempfiles, vc faz SELECT * FROM DBA_TEMP_FILES; Neste último caso a sintaxe pra adicionar é um pouco diferente, consulte nos manuais que vc acha...

[]s

resource busy and acquire with NOWAIT specified

ORA-00054:
resource busy and acquire with NOWAIT specified
Cause:
Resource interested is busy.
Action:
Retry if necessary.

Erro dado quando numa sessao c/ usuario agodba fiz insert sem commit e na outra, tbm com usuario agodba, dei truncate.
Teste: se eu der rollback na segunda sessao, aquela q dei truncate, nada acontece pq nao houve manip de dados nessa sessao.
Se eu der rollback ou commit, o truncate na outra sessao executa.


SQL> delete from ag22_mvt;
0 rows deleted.
SQL> truncate table ag22_mvt;truncate table ag22_mvt *ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified

import export in oracle

http://www-it.desy.de/systems/services/databases/oracle/impexp/impexp.html.en

http://www.orafaq.com/wiki/Import_Export_FAQ

What are the common Import/ Export problems?
ORA-00001: Unique constraint (...) violated
You are importing duplicate rows. Use IGNORE=NO to skip tables that already exist (imp will give an error if the object is re-created).
ORA-01555: Snapshot too old
Ask your users to STOP working while you are exporting or use parameter CONSISTENT=YES
ORA-01562: Failed to extend rollback segment
Create bigger rollback segments or set parameter COMMIT=Y while importing
IMP-00015: Statement failed ... object already exists...
Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

Oracle: Why is shutdown immediate not immediate ?

Author's name: Norman Dunbar;
Author's Email: Oracle@bountifulsolutions.co.uk
Date written: 22/08/2001
Oracle version(s): 8.1.7

There are four ways to shut down a database :

Shutdown
Shutdown immediate
Shutdown transactional
Shutdown abort
Shutdown waits for everyone to finish & log out before it shuts down. The database is cleanly shutdown.

Shutdown immediate rolls back all uncommitted transactions before it shuts down. The database is cleanly shutdown.

Shutdown transactional waits for all current transactions to commit or rollback before it shuts down. The database is cleanly shutdown.

Shutdown abort quickly shuts down - the next restart will require instance recovery. The database is technically crashed.

The key reason for an immediate shutdown not being immediate is because of the need to rollback all current transactions. If a user has just started a transaction to update emp set sal = sal * 2 where emp_id = 1000; then this will be rolled back almost instantaneously.

However, if another user has been running a huge update for the last four hours, and has not yet committed, then four hours of updates have to be rolled back and this takes time.

So, if you really want to shutdown right now, then the advised route is :

shutdown abort
startup restrict
shutdown
When you shutdown abort, Oracle kills everything immediately. Startup restrict will allow only dba users to get in but, more importantly, will carry out instance recovery and recover back to a consistent state using the current on-line redo logs. The final shutdown will perform a clean shutdown. Any cold backups taken now will be of a consistent database.

There has been much discussion on this very subject on the Oracle Server newsgroups. Some people are happy to backup the database after a shutdown abort, others are not. I prefer to use the above method prior to taking a cold backup - if I have been unable to shutdown or shutdown immediate that is.

Marcado pra morrer

SQL> alter system kill session '12,1600';alter system kill session '12,1600'

*ERROR at line 1:ORA-00031: session marked for kill

ORA-00031:
session marked for kill
Cause:
The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptable operation is done.
Action:
No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.

How do I embed the ampersand (&) as part of the literal text I use in an SQL statement ?

How do I embed the ampersand (&) as part of the literal text I use in an SQL statement ?
--------------------------------------------------------------------------------
IN ORACLE
in simple words:

just put this after the ampersand ''

***NOTICED THAT THIS BLOG HIDES THE PIPES BETWEEN THE SINGLE QUOTES***

ampersand becomes

ampersand
simple quote
pipe
pipe
simple quote

insert into tab1 values('R&D'); <---error in oracle

insert into tab1 values('R&''D'); <--there are two pipes

between the quotes in the middle*****blog supressed

Voilà la solution!

DECODE IF

the user wants the object available for all users, we must put on the universe

Sur l'objet "Plateforme R&D France", il faut ajouter que si "Location of Technical Leader"="Novecare - Europe", alors "Plateforme R&D France" = "PMPS"
SQL on Universe

before
D_PROJET.PLAT_RD_FR

after - SOLUTION
decode(@Select(Identification\Location of the technical leader),'Novecare - Europe','PMPS',D_PROJET.PLAT_RD_FR)


decode(D_PROJET.TEC_LEA_LOC,'Novecare - Europe','PMPS',D_PROJET.PLAT_RD_FR)


SQL On BO - RESULT

SELECT
decode(( D_PROJET.tec_lea_loc ),'Novecare - Europe','PMPS',D_PROJET.PLAT_RD_FR),
D_PROJET.tec_lea_loc
FROM
D_PROJET


before



after

ISQLPLUS

full doc

Overview

What is iSQL*Plus?

iSQL*Plus is a browser-based interface to SQL*Plus. iSQL*Plus is a component of the SQL*Plus product, and it enables you to use a Web browser to connect to Oracle9i and perform the same actions that you would otherwise perform through the command line version of SQL*Plus.

iSQL*Plus Architecture

iSQL*Plus uses a three-tier architectural model:

	Client tier: The iSQL*Plus user interface, typically a Web browser
	Middle tier: The iSQL*Plus Server, Oracle Net, and Oracle HTTP Server
	Database tier: Oracle9i

iSQL*Plus User Interface (Client Tier)

The iSQL*Plus user interface runs in a Web browser. There is no installation or configuration required for the iSQL*Plus user interface other than this Web browser.

Oracle HTTP Server (Middle Tier)

The iSQL*Plus Server is installed with the Oracle HTTP Server with Oracle9i. The iSQL*Plus Server enables communication and authentication between the iSQL*Plus user interface and the RDBMS.

Oracle9i (Database Tier)

Oracle Net components provide communication between the iSQL*Plus Server and Oracle9i.

Doublons dans ma vie

SQL> select count(1) from ga_faits where annee='2007' and mois='12';

COUNT(1)
----------
33448

SQL> select count(1) from (select pernr, count(1) from ga_faits where annee='2007' and mois='12' group by pernr having count(1) >1);

COUNT(1)
----------
16724

SQL> select rownum, pernr, annee,mois from ga_faits where annee='2007' and mois='12' and pernr='10015098';

ROWNUM PERNR ANNEE MOIS
---------- ---------- -------- --------
1 10015098 2007 12
2 10015098 2007 12

SQL> delete from ga_faits where pernr='10009309' and annee='2007' and mois='12' and rownum<2;> rollback;

Rollback complete.

SQL> @c:\deleteduplicates.sql

1 row deleted.


1 row deleted.

::::::


1 row deleted.

SQL> select count(1) from ga_faits where annee='2007' and mois='12';

COUNT(1)
----------
16724

SQL> commit;

Commit complete.

SQL> spool off

A2 sao os ids extraidos via spool
criado com excel com a formula ="delete from ga_faits where pernr='"&A2&"' and annee='2007' and mois='12' and rownum<2;"

deleteduplicates.sql parcialmente mostrado

delete from ga_faits where pernr='10009309' and annee='2007' and mois='12' and rownum<2;
delete from ga_faits where pernr='10015098' and annee='2007' and mois='12' and rownum<2;
delete from ga_faits where pernr='10015879' and annee='2007' and mois='12' and rownum<2;
delete from ga_faits where pernr='10032005' and annee='2007' and mois='12' and rownum<2;

How to find duplicate rows with SQL

This article shows how to find duplicated rows in a database table. This is a very common beginner question. The basic technique is straightforward. I’ll also show some variations, such as how to find “duplicates in two columns” (a recent question on the #mysql IRC channel).

How to find duplicated rows

The first step is to define what exactly makes a row a duplicate of another row. Most of the time this is easy: they have the same value in some column. I’ll take this as a working definition for this article, but you may need to alter the queries below if your notion of “duplicate” is more complicated.

For this article, I’ll use this sample data:

create table test(id int not null primary key, day date not null);

insert into test(id, day) values(1, '2006-10-08');
insert into test(id, day) values(2, '2006-10-08');
insert into test(id, day) values(3, '2006-10-09');

select * from test;
+----+------------+
| id | day        |
+----+------------+
|  1 | 2006-10-08 |
|  2 | 2006-10-08 |
|  3 | 2006-10-09 |
+----+------------+

The first two rows have the same value in the day column, so if I consider those to be duplicates, here’s a query to find them. The query uses a GROUP BY clause to put all the rows with the same day value into one “group” and then count the size of the group:

 select day, count(*) from test GROUP BY day;
+------------+----------+
| day        | count(*) |
+------------+----------+
| 2006-10-08 |        2 |
| 2006-10-09 |        1 |
+------------+----------+

The duplicated rows have a count greater than one. If you only want to see rows that are duplicated, you need to use a HAVING clause (not a WHERE clause), like this:

select day, count(*) from test group by day HAVING count(*) > 1;
+------------+----------+
| day        | count(*) |
+------------+----------+
| 2006-10-08 |        2 |
+------------+----------+

This is the basic technique: group by the column that contains duplicates, and show only those groups having more than one row.

Why can’t you use a WHERE clause?

A WHERE clause filters the rows before they are grouped together. A HAVING clause filters them after grouping. That’s why you can’t use a WHERE clause in the above query.

How to delete duplicate rows

A related question is how to delete the ‘duplicate’ rows once you find them. A common task when cleaning up bad data is to delete all but one of the duplicates, so you can put proper indexes and primary keys on the table, and prevent duplicates from getting into the table again.

Again, the first thing to do is make sure your definition is clear. Exactly which row do you want to keep? The ‘first’ one? The one with the largest value of some column? For this article, I’ll assume you want to keep the ‘first’ row — the one with the smallest value of the id column. That means you want to delete every other row.

Probably the easiest way to do this is with a temporary table. Especially in MySQL, there are some restrictions about selecting from a table and updating it in the same query. You can get around these, as I explain in my article How to select from an update target in MySQL, but I’ll just avoid these complications and use a temporary table.

The exact definition of the task is to delete every row that has a duplicate, except the row with the minimal value of id for that group. So you need to find not only the rows where there’s more than one in the group, you also need to find the row you want to keep. You can do that with the MIN() function. Here are some queries to create the temporary table and find the data you need to do the DELETE:

create temporary table to_delete (day date not null, min_id int not null);

insert into to_delete(day, min_id)
select day, MIN(id) from test group by day having count(*) > 1;

select * from to_delete;
+------------+--------+
| day        | min_id |
+------------+--------+
| 2006-10-08 |      1 |
+------------+--------+

Now that you have this data, you can proceed to delete the ‘bad’ rows. There are many ways to do this, and some are better than others (see my article about many-to-one problems in SQL), but again I’ll avoid the finer points and just show you a standard syntax that ought to work in any RDBMS that supports subqueries:

delete from test
where exists(
select * from to_delete
where to_delete.day = test.day and to_delete.min_id <> test.id
)

If your RDBMS does not support subqueries, or if it’s more efficient, you may wish to do a multi-table delete. The syntax for this varies between systems, so you need to consult your system’s documentation. You may also need to do all of this in a transaction to avoid other users changing the data while you’re working, if that’s a concern.

How to find duplicates in multiple columns

Someone recently asked a question similar to this on the #mysql IRC channel:

I have a table with columns b and c that links two other tables b and c, and I want to find all rows that have duplicates in either b or c.

It was difficult to understand exactly what this meant, but after some conversation I grasped it: the person wanted to be able to put unique indexes on columns b and c separately.

It’s pretty easy to find rows with duplicate values in one or the other column, as I showed you above: just group by that column and count the group size. And it’s easy to find entire rows that are exact duplicates of other rows: just group by as many columns as you need. But it’s harder to identify rows that have either a duplicated b value or a duplicated c value. Take the following sample table, which is roughly what the person described:

create table a_b_c(
a int not null primary key auto_increment,
b int,
c int
);

insert into a_b_c(b,c) values (1, 1);
insert into a_b_c(b,c) values (1, 2);
insert into a_b_c(b,c) values (1, 3);
insert into a_b_c(b,c) values (2, 1);
insert into a_b_c(b,c) values (2, 2);
insert into a_b_c(b,c) values (2, 3);
insert into a_b_c(b,c) values (3, 1);
insert into a_b_c(b,c) values (3, 2);
insert into a_b_c(b,c) values (3, 3);

Now, you can easily see there are some ‘duplicate’ rows in this table, but no two rows actually have the same tuple {b, c}. That’s why this is a bit more difficult to solve.

Queries that don’t work

If you group by two columns together, you’ll get various results depending on how you group and count. This is where the IRC user was getting stumped. Sometimes queries would find some duplicates but not others. Here are some of the things this person tried:

select b, c, count(*) from a_b_c
group by b, c
having count(distinct b > 1)
or count(distinct c > 1);

This query returns every row in the table, with a COUNT(*) of 1, which seems to be wrong behavior, but it’s actually not. Why? Because the > 1 is inside the COUNT(). It’s pretty easy to miss, but this query is actually the same as

select b, c, count(*) from a_b_c
group by b, c
having count(1)
or count(1);

Why? Because (b > 1) is a boolean expression. That’s not what you want at all. You want

select b, c, count(*) from a_b_c
group by b, c
having count(distinct b) > 1
or count(distinct c) > 1;

This returns zero rows, of course, because there are no duplicate {b, c} tuples. The person tried many other combinations of HAVING clauses and ORs and ANDs, grouping by one column and counting the other, and so forth:

select b, count(*) from a_b_c group by b having count(distinct c) > 1;
+------+----------+
| b    | count(*) |
+------+----------+
|    1 |        3 |
|    2 |        3 |
|    3 |        3 |
+------+----------+

Nothing found all the duplicates, though. What I think made it most frustrating is that it partially worked, making the person think it was almost the right query… perhaps just another variation would get it…

In fact, it’s impossible to do with this type of simple GROUP BY query. Why is this? It’s because when you group by one column, you distribute like values of the other column across multiple groups. You can see this visually by ordering by those columns, which is what grouping does. First, order by column b and see how they are grouped:

a	b	c
7	1	1
8	1	2
9	1	3
10	2	1
11	2	2
12	2	3
13	3	1
14	3	2
15	3	3

When you order (group) by column b, the duplicate values in column c are distributed into different groups, so you can’t count them with COUNT(DISTINCT c) as the person was trying to do. Aggregate functions such as COUNT() only operate within a group, and have no access to rows that are placed in other groups. Similarly, when you order by c, the duplicate values in column b are distributed into different groups. It is not possible to make this query do what’s desired.

Some correct solutions

Probably the simplest solution is to find the duplicates for each column separately and UNION them together, like this:

select b as value, count(*) as cnt, 'b' as what_col
from a_b_c group by b having count(*) > 1
union
select c as value, count(*) as cnt, 'c' as what_col
from a_b_c group by c having count(*) > 1;
+-------+-----+----------+
| value | cnt | what_col |
+-------+-----+----------+
|     1 |   3 | b        |
|     2 |   3 | b        |
|     3 |   3 | b        |
|     1 |   3 | c        |
|     2 |   3 | c        |
|     3 |   3 | c        |
+-------+-----+----------+

The what_col column in the output indicates what column the duplicate value was found in. Another approach is to use subqueries:

select a, b, c from a_b_c
where b in (select b from a_b_c group by b having count(*) > 1)
or c in (select c from a_b_c group by c having count(*) > 1);
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  7 |    1 |    1 |
|  8 |    1 |    2 |
|  9 |    1 |    3 |
| 10 |    2 |    1 |
| 11 |    2 |    2 |
| 12 |    2 |    3 |
| 13 |    3 |    1 |
| 14 |    3 |    2 |
| 15 |    3 |    3 |
+----+------+------+

This is probably much less efficient than the UNION approach, and will show every duplicated row, not just the values that are duplicated. Still another approach is to do self-joins against grouped subqueries in the FROM clause. This is more complicated to write correctly, but might be necessary for some complex data, or for efficiency:

select a, a_b_c.b, a_b_c.c
from a_b_c
left outer join (
select b from a_b_c group by b having count(*) > 1
) as b on a_b_c.b = b.b
left outer join (
select c from a_b_c group by c having count(*) > 1
) as c on a_b_c.c = c.c
where b.b is not null or c.c is not null

Any of these queries will do, and I’m sure there are other ways too. If you can use UNION, it’s probably the easiest.