For Loop sample

begin
FOR i IN 1..20 LOOP
insert into test values(i,'empresa'to_char(i));
END LOOP;
end;

commit;

Oracle and BI studies

Database:
-Oracle database
-Oracle OLAP option

ETL:
-Oracle Warehouse Builder
-Oracle Data Integrator

Reports:
Oracle Discoverer (Administrator, Desktop, Portlet Provider, Viewer, Plus and Plus Olap)
Oracle Reports (Builder and Services)
Oracle Spreadsheet Add-in
Oracle BI Beans


Oracle Data Miner
Macro Oracle Predictive Analytics


OracleBI
-Oracle HTTP Server
-Oracle Containers for J2EE
-Oracle Web Cache
-Oracle Report Services
-Oracle Discoverer

FRench:ENglish
jauges= gauges

*ver Excel data crosstab report.

Real test

Real test did not work, here the error:


SQL> startup
ORA-01081: impossible de lancer ORACLE deja en cours - fermer d'abord le thread
SQL> shutdown immediate
ORA-01507: base de donnees non montee


Instance ORACLE arrÛtÚe.
SQL> startup
ORA-03113: fin de fichier sur canal de communication
SQL> conn SYS as SYSDBA
Entrez le mot de passe :
ConnectÚ Ó une instance inactive.
SQL> startup
Instance ORACLE lancÚe.

Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes

ORA-00209: discordance de taille de bloc pour le fichier de controle;
consultez le jounal des alertes

TESTS

C:\Documents and Settings\will>tnsping orcl9i

TNS Ping Utility for 32-bit Windows: Version 9.0.1.1.1 - Production on 16-JAN-20
10 02:53:36

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Fichiers de paramÞtres utilisÚs :
C:\oracle\ora90\network\admin\sqlnet.ora
C:\oracle\ora90\network\admin\tnsnames.ora

Adaptateur TNSNAMES utilisÚ pour la rÚsolution de l'alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL9I)))
OK (30 msec)



C:\Documents and Settings\will>lsnrctl services orcl9i

LSNRCTL for 32-bit Windows: Version 9.0.1.1.1 - Production on 16-JAN-2010 03:17:
44

Copyright (c) 1991, 2001, Oracle Corporation. All rights reserved.

Connexion Ó (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=will)(PORT=1521)))
RÚcapitulatif services...
Le service "ORCL9i" comporte 1 instance(s).
L'instance "ORCL9i", statut BLOCKED, comporte 1 gestionnaire(s) pour ce servic
e...
Gestionnaire(s) :
"DEDICATED" Útabli : 0 refusÚ : 0 statut : ready
LOCAL SERVER
Le service "PLSExtProc" comporte 1 instance(s).
L'instance "PLSExtProc", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce se
rvice...
Gestionnaire(s) :
"DEDICATED" Útabli : 0 refusÚ : 0
LOCAL SERVER
La commande a rÚussi

Oracle Ressuscitado

Oracle 9i : 9.0.1.1
My computer with oracle server is not working anymore, but I have the all the files and I'm able to create the same installation structure. Can I recover it? Find out in the end!


LOCAL TEST, real test will be done soon:
0 - installed oracle 9i
1 - Started the services, created the user, set default tablespace, stopped the services
2- backed up the files
3 - Started the services, set temporary tablespace to the user.
4 - created two tables with relationships
5 - created one row for each table and commited
6 - run select statement
7 - stopped the services
8 -copied backed up files to the local files.
9 - restarted all services



well it works but the thing is: al objects become part of the system tablespace.


1 - IDENTIFYING OBJECTS
see if the objects exist, using SYSTEM schema:
select * from all_objects where object_name='EMPRESA' or object_name='PESSOA'


we see owner and created date. TO see the indexes or other objects, try this:
select * from all_objects where created to_date('15-JAN-2010','DD-MON-YYYY')
It doesnt work for me. I have to check it.
For now, try this: select * from all_objects where owner='SYSTEM' order by CREATED DESC


2 - TABLES MUST BE MOVED:


before: see the rownum
select ROWID, a.* from system.EMPRESA a
AAAH2AAABAAAPCDAAA


select ROWID, a.* from system.PESSOA a
AAAH2CAABAAAPCJAAA


alter table system.EMPRESA MOVE TABLESPACE USERS;
alter table system.PESSOA MOVE TABLESPACE USERS;


check results:
select * from all_tables where table_name='PESSOA'


indexes are still in system and now UNUSABLE:
select * from all_indexes where index_name='SYS_C002715'


Moving:
ALTER INDEX system.SYS_C002715 REBUILD TABLESPACE INDX;


Status valid, but one problem:
http://www.dba-oracle.com/t_change_table_schema_owner.htm


The data and objects are in right place, but the owner is not. Unless you import/export. At least, the data and objects are usable!

One thing to remember is that ‘MOVE TABLESPACE’ does not work if table contains column with LONG or LONG RAW data type. You will run into ‘ORA-00997: illegal use of LONG datatype’ error. Such tables can be moved to new tablespace using exp/imp command.

Recovering complete!

Readaptar oracle backup

Hi ,

Its Great Pleasure to share this Automation of Oracle Cold Backup as a Scheduled job to run as per the requirement of the DBA for Windows Platform.

Oracle 8.1.7 DB Cold Backup on Win2000.

To Automate a Coldbackup of a DB implement the following steps.
===============================================================

Require 3 files.

Backup.bat;shutdown.sql;startup.sql the contents of each files is given below.

Save the files with Proper extension as mentioned above.

Also Place them in a Proper directory... as in this case c:\

Check where are the oracle DB files located as Per your installation and mention them in Backup.bat file.

backup.bat file Content:
========================

echo Backing up DB in Cold Backup mode... >c:\Backup.log
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\shutdown.sql" >>c:\Backup.log
copy c:\oracle\oradata\orcl\*.* c:\backup >>c:\Backup.log
copy C:\oracle\admin\orcl\pfile\*.* c:\backup >>c:\Backup.log
copy C:\oracle\admin\DEVDB\bdump\*.* c:\backup >>c:\Backup.log
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\startup.sql" >>c:\Backup.log

Backup.log file is created and all the execution steps in .bat file are traced to it.

Shutdown.sql Content
====================
connect internal/password
shutdown immediate
exit

Startup.sql Content
===================
connect internal/password
startup pfile=C:\oracle\ora81\database\initorcl.ora
exit

Place all this file in C:\ or D:\ wherever you like.

Now go to Start==>Settings==>Control Panel==>Scheduled Tasks==>Add Scheduled Task==>Open(double click)==>A Scheduled Task Wizard is Opened==>Click Next==>Browse==>Choose the Batfile ==>Now Plan how you want the Backup Daily,Weekly as per your requirement==>It will ask for the System Administrator Pwd twice==>If you want open the Advance option to have a Look.

The Scheduled Task for Cold Backup is finished.

Ensure the destination Cold Backup has enough space.

The above coldbackup still can be re written as per the following requirements:

1)Also we can include Compress the Backed up files after copying
2)Delete the old copies (say not more then 3 days old backup)
3)Cold back up first to disk and then Moving it to Tape drives etc

In coming day Hot Backup Automation etc will be posted in the same Thread.

To Automate the Analyze for a User in the Prod DB as a Nightly Job.
===================================================================
To do a Nightly Analyze of Schema in a Production DB.
Require 2 files.

ANALYZE_SYS.bat & Analyze.sql
Save the files with Proper extension as mentioned above.
Also Place them in a Proper directory... as in this case c:\

ANALYZE_SYS.bat
===============
echo Analyzing SYS Schema... >c:\Analyze.log
call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\analyze.sql" >>c:\Analyze.log

Analyze.sql
===========
connect internal/password@opsdb
execute DBMS_UTILITY.ANALYZE_SCHEMA('SYS','COMPUTE')
exit

The Analyze.log will trace all the Scheduled Job as Trace file.

For any Job to be Automated after Placing the files as said in the above cases.Scheduling is done as follows
===================================================================
Now go to Start==>Settings==>Control Panel==>Scheduled Tasks==>Add Scheduled Task==>Open(double click)==>A Scheduled Task Wizard is Opened==>Click Next==>Browse==>Choose the Batfile (backup.bat or ANALYZE_SYS.bat) ==>Now Plan how you want the Backup or Analyze Daily,Weekly as per your requirement==>It will ask for the System Administrator Pwd twice==>If you want open the Advance option to have a Look.

Errors

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

Essa mensagem aparece quando o banco de dados esta shutdown. Poderia ser outra mensagem.

ISQLPLUS

http://192.168.0.4/isqlplus default port is 80

Views

SQL> desc all_views
Name Null? Type
----------------------------------------- -------- ---------------

OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)

SQL> set long 1000000

select text from all_views where view_name='YOURVIEW'

what is new in Oracle 11g

Oracle Audit Vault

We know that DBAs who are skilled enough can view and change data if they really want to. They can then alter the audit trail—wipe away their footprints—to make sure no one can find out what they've done. It's always been a problem, and now it's also a major compliance issue.

Oracle Audit Vault changes that scenario considerably. The audit trail goes into a one-way "lockbox." No one, not even a highly skilled DBA, can alter that audit trail once it's in the lockbox. People can view it, but no one can change it or delete it.

Hot Patching

Another feature that DBAs will really appreciate is hot patching, which allows you to apply updates and bug fixes to the database without bringing the system down.

Data Compression

Another new feature in Oracle Database 11g is the ability to store data in compressed format—called advanced table compression. Suppose you're storing a lot of long documents. Keeping them in compressed format can save up to two-thirds of the usual space necessary.

Query Result Cache

Oracle Database 11g will cache the results of queries in memory, where they act like tables. Multiple users can see these results without repeating the same query. For common and frequently used queries, this can save system effort and improve performance. You can configure how much space to allocate to query result cache and how long to retain results.

Oracle Enterprise Linux

Oracle offers Linux distribution, that is based on Red Hat Linux.
Of course, for Oracle database server hosting.

download here: http://edelivery.oracle.com/linux

Start and shutdown

For a DBA, starting up and shutting down of oracle database is a routine and basic operation. Sometimes Linux administrator or programmer may end-up doing some basic DBA operations on development database. So, it is important for non-DBAs to understand some basic database administration activities.

In this article, let us review how to start and stop an oracle database.

How To Startup Oracle Database
1. Login to the system with oracle username
Typical oracle installation will have oracle as username and dba as group. On Linux, do su to oracle as shown below.

$ su - oracle2. Connect to oracle sysdba
Make sure ORACLE_SID and ORACLE_HOME are set properly as shown below.

$ env grep ORA
ORACLE_SID=DEVDB
ORACLE_HOME=/u01/app/oracle/product/10.2.0
You can connect using either “/ as sysdba” or an oracle account that has DBA privilege.

$ sqlplus '/ as sysdba' (linux)

c:\Documents and settings\will\Desktop> sqlplus "/ as sysdba" (windows)

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:28 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
SQL>3. Start Oracle Database
The default SPFILE (server parameter file) is located under $ORACLE_HOME/dbs. Oracle will use this SPFILE during startup, if you don’t specify PFILE.

Oracle will look for the parameter file in the following order under $ORACLE_HOME/dbs. If any one of them exist, it will use that particular parameter file.

spfile$ORACLE_SID.ora
spfile.ora
init$ORACLE_SID.ora

Type “startup” at the SQL command prompt to startup the database as shown below.

SQL> startup
ORACLE instance started.

Total System Global Area 812529152 bytes
Fixed Size 2264280 bytes
Variable Size 960781800 bytes
Database Buffers 54654432 bytes
Redo Buffers 3498640 bytes
Database mounted.
Database opened.
SQL>
If you want to startup Oracle with PFILE, pass it as a parameter as shown below.

SQL> STARTUP PFILE=/u01/app/oracle/product/10.2.0/dbs/init.ora

How To Shutdown Oracle Database
Following three methods are available to shutdown the oracle database:

Normal Shutdown
Shutdown Immediate
Shutdown Abort

1. Normal Shutdown

During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

2. Shutdown Immediate

During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enough time to request users to log-off.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

3. Shutdown Abort

During shutdown abort, before the oracle database is shutdown, all user sessions will be terminated immediately. Uncomitted transactions will not be rolled back. Use this option only during emergency situations when the “shutdown” and “shutdown immediate” doesn’t work.

$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jan 18 11:11:33 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.

SQL> shutdown abort
ORACLE instance shut down.
SQL>

Oracle tips

********
TIP1 Shutdown/offline messages
********

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

or

ERROR at line 1:
ORA-12571: TNS:packet writer failure


Essas mensagens aparecem quando o banco de dados esta shutdown. Poderia ser outra mensagem.


********
TIP2 Oracle init.ora file
********

The init.ora file stores the initialization parameters of Oracle. The values that are currently in effect can be viewed through v$parameter. The init.ora file is read when an instance (for example using startup in SQL*Plus). Default location and name

The default location of init.ora is

$ORACLE_HOME/dbs> on unix and
%ORACLE_HOME%\database on Windows.

On Windows, the location can be changed by changing ORA_%ORACLE_SID%_PFILE. The default name for the file is init$ORACLE_SID.ora (unix) or init%ORACLE_SID%.ora (windows)

However, it is possible to start the database with another init.ora file than the default one. In this case, there is no way to determine which init.ora was used when the database is running (at least up to Oracle 9i).


********
TIP 3 Oracle Password file
********

$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and
%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

********
TIP 4 Mostrando codigo de views
********


SQL> desc all_views
Name Null? Type
----------------------------------------- -------- ---------------

OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)

SQL> set long 1000000

select text from all_views where view_name='YOURVIEW'


ps. oracle: Ao criar views baseado em colunas de tabelas, tudo fica bem ate qdo uma coluna é removida da tabela e a view fica desatualizada

Relembrando o basico

sqlplus system/senha@orcl9i

create user will identified by senha
default tablespace users
temporary tablespace temp;

grant dba to will;

//to see this role granted, logged with the user:

//select * from user_role_privs;

create table Pessoa(
pessoa_id number primary key,
empresa_id number references Empresa(empresa_id),
nome varchar2(200),
sobrenome varchar2(200),
tratamento varchar2(10),
telefone varchar2(12),
email varchar2(200),
celular varchar2(12)
)


create table Empresa(
empresa_id number primary key,
empresa_name varchar2(200),
website varchar2(100),
address varchar2(200),
address2 varchar2(200),
telefone varchar2(12),
telefone2 varchar2(12),
cidade varchar2(10),
provincia varchar2(10),
comentario varchar2(200)
)


alter table
candidatar_vaga
add constraint
fk_anuncio FOREIGN KEY (anuncio_id)
references
anuncio (anuncio_id)
initially deferred deferrable;


create sequence seq_ex start with 1 increment by 1;




-------------------------------------------- PROMPTING
fichier:SITEEMPREGOS.sql
insert into siteempregos values(seq_siteempregos.nextval,&NOME,&SITE);
---

@'C:\Documents and Settings\Invité\Mes documents\SITEEMPREGOS.sql'

preencher com aspas simples as strings ao responder
-------------------------------------------------------

http://192.168.0.4/isqlplus




*****************************************Fazendo HOT BACKUP
http://www.youtube.com/watch?v=MOoUFIS-uoQ
http://www.cuddletech.com/articles/oracle/node58.html
http://www.dba-oracle.com/concepts/starting_database.htm

1 - archivelog mode on

SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG


Aqui vemos que nao esta ON.

Faremos ficar on entao:

adicionar no fim do init.ora:

############################
# Archive Log Destinations will(16/12/09)
############################
log_archive_dest_1='location=/u02/oradata/cuddle/archive'
log_archive_start=TRUE


parar e iniciar o servico:

entre no prompt e digite:

C:\Documents and Settings\Will>set oracle_sid=orcl9i
C:\Documents and Settings\Will>sqlplus "sys as sysdba"

SQL*Plus: Release 9.1.0.1.0 - Production on Tue Dec 16 23:00:58 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

Verificando - archivelog mode on

SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG


Digitando:

rman target /
RMAN> backup database plus archivelog;

PARECE QUE DPS DESSE PROCEDIMENTO DEVE SE INICIAR O BANCODEDADOS MANUALMENTE:

sqlplus " / as sysdba"
SQL> startup

Oracle 9i

Quebrando a cabeça com a rede P2P.

Apos penar para configurar a rede, constatei o seguinte:

-ao dar ping, vc deve conseguir dar ping em vc mesmo via
ping 127.0.0.1, ping localhost E ping 192.168.0.1 (seu ip)
-verificar se vc consegue pingar das duas pontas, ida e volta.
-verificar se nao existe algum software barrando:
no meu caso Norton internet security. Um firewall por exemplo.
-verificar se o endereco fisico da placa de rede nao é identica, pois nao funciona,
mesmo com ips diferentes e subnet mask iguais.
-constatei que a LUZ minha placa de rede dell inspiron 1300, fica amarela e nunca verde. Mesmo funcionando.
-verificar se os drivers da placa de rede estao ok.
Depois de ver se a rede esta funcionando, configurar o oracle db e o oracle client.

Network configuration (ORACLE DB SERVER):

# TNSNAMES.ORA Network Configuration File: c:\oracle\ora90\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.


ORCL9I=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL9I)
)
)


# LISTENER.ORA Network Configuration File: c:\oracle\ora90\network\admin\listener.ora
# Generated by Oracle configuration tools.

ORCL9I =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\Oracle\Ora90)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL9i)
(ORACLE_HOME = c:\oracle\ora90)
(SID_NAME = ORCL9i)
)
)


# SQLNET.ORA Network Configuration File: C:\oracle\ora90\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

#NAMES.DEFAULT_DOMAIN = localdomain

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES)

quick check for duplication in the fact table

select
count(1), num_annee,num_mois,
AG14_HRID.COD_MATRICULE
-- AG14_HRID.LIB_NOM,
-- AG14_HRID.LIB_PRENOM
from
AG14_HRID
--where
-- AG14_HRID.cod_matricule='FRA0592979'
-- and num_annee='2009' and num_mois='01'
group by
num_annee,num_mois, AG14_HRID.COD_MATRICULE
having count(1)>1

Addresses

C:\Oracle\Ora81\network\admin
tnsnames

C:\WINDOWS\system32\drivers\etc
hosts

https://localhost:2002/main.html
logmein

Dicas

oracle: Ao criar views baseado em colunas de tabelas, tudo fica bem ate qdo uma coluna é removida da tabela e a view fica desatualizada, no BO apos clicar ok qdo for rodar a query, a tela volta na tela de objetos.

Ao atualizar a view e depois a estrutura no designer para que a view seja atualizada, deve se apagar o objeto no painel esquerdo pq ele nao eh atualizado/removido.

BO designer: ao copiar relacionamentos, copiar a clausula where ao inves de ligar os relacionamentos.

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.

Excel formula to make scripts in Oracle

="update saa_resp_strat set login_ach='"&B5& "' where cfam='"&A5&"' AND creg='AR'"

Como Verificar o Oracle instalado é 32 ou 64 bits ??

agpadm@frparux7:/home/agpadm>cd $ORACLE_HOME
agpadm@frparux7:/oracle/product/8.1.7_64>cd bin
agpadm@frparux7:/oracle/product/8.1.7_64/bin>file oracle
oracle: module exÚcutable ou objet AIX Ó 64 bits non rÚduit

Removing world from tnsnames

Using 8i, you may come across to this

you cant connect to the database if you dont put xxx.world in you TNSnames

So


If you dont want to use the default domain name go to
sqlnet.ora file D:\Oracle\Ora81\network\ADMIN\sqlnet.ora
if oracle 8i or in oracle8 in \network\ADMIN\sqlnet.ora

remove the NAMES.DEFAULT_DOMAIN or disable it with # before the parameter.

then ||rly comeback to tnsnames.ora file edit and remove the word WORLD.

TEST.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = padmam)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)

Dicas Gerais de SQL*Loader

-O TOAD versão 9 (não sei as anteriores) possui um SQLLOADER Wizard

-Procedures PL/SQL também poderia ser usada, mas via de regra um SQL puro simples e direto é
**muito muito ** mais eficiente que PL/SQL.

"Regra Geral" de Validação / Correção de dados para carga, dos experts:

PRIMEIRO tentar em SQL apenas

SEGUNDO tentar pra stored PL/SQL

TERCEIRO tentar java ou C dentro do banco

EM último caso aí sim usar a linguagem cliente

Chamando SQL*Loader dentro de uma procedure

sendo o sql*loader um simples programa-executável externo,
a pergunta passa a ser "como executar um programa externo ao Pl/SQL",
e a resposta é : é *** FACÍLIMO **** fazer isso, há muitas muitas
opções mas infelizmente a pessoa ** terá que ** escrever e usar algo
externo ao PL/SQL, nativo em PL/SQL simplesmente não há como... As
opções seriam dependem do banco usado, mas de modo geral : na versão
10g do
banco pode ser um job, na 9i e acima pode ser external procedure (ie,
chamar uma .DLL ou no caso de unix um .so), na 8i e acima pode ser
uma rotina escrita em java, OU em todas essas e nas anteriores pode-se
ter um programa
externo (shell script mesmo poderia ser em unix), em pooling
esperando receber comandos via PIPE ou arquivo de texto - pesquise em
asktom por essas palavras-chave que vc acha exemplo de TODAS as
técnicas citadas.

Links uteis para Oracle SQL*Loader

Versao SQL*Loader Oracle 8i


Versao SQL*Loader Oracle 9i


PDF do doc SQL*Loader Oracle 9i - Oracle Database Utilites

nolog

/ is a default logon using operating system authentication

/nolog establishes no initial connection to Oracle Database.

When you connect using sysdba or sysoper, then you are sys or public user, no matter what username/password you provide :


C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Aug 11 06:11:39 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> show user
USER is ""

SQL> conn / AS SYSDBA
Connected.
SQL> SHOW USER
USER is "SYS"

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Aug 11 06:13:02 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / AS SYSDBA
Connected.
SQL> SHOW USER
USER is "SYS"
SQL> conn / as sysoper
ERROR:
ORA-01031: insufficient privileges

SQL> CONN SCOTT/TIGER AS SYSDBA
Connected.
SQL> SHO USER
USER is "SYS"
SQL> CONN WILLIAM/pass AS SYSOPER
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.




-------------------------------
Comandos em sequencia para inicio do banco de dados manual

>sqlplus /nolog
>conn / as SYSDBA
>startup


Clarificando ainda mais:

Clique aqui

Começando em PL/SQL

Os códigos abaixo sao codigos de PL/SQL para Oracle.
Abrir o sqlplus copiar e colar...
depois so digitar / e pressionar. Um script de cada vez.


Hello World em pl/sql
---------------------inicio script--------------
set serveroutput on

BEGIN
DBMS_OUTPUT.put_line('Hello World');
END;
---------------------fim script-----------------


Codigo para mostrar o quadrado de cada valor de 0 a 10.

---------------------inicio script-----------------
DECLARE
square INT;
BEGIN
FOR i IN 1..10 LOOP
 square := i * i;
 DBMS_OUTPUT.put_line( to_char(square)  ' ' );
END LOOP;
END;

---------------------fim script-----------------

Falha de instancia ou mudança de IP

Quando a instancia nao esta mais rodando ou mudanca de IP do server da o seguinte erro:

SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-12571: TNS:packet writer failure

SQL> /
select sysdate from dual
*
ERROR at line 1:
ORA-03114: not connected to ORACLE

SQL> conn william/xxxxx@delldb9i
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

Configurando o ambiente Oracle

VI como editor default do sqlplus

Para aqueles que adoram esse editor, como eu, é muito bom para praticar os comandos:

1 baixar e instalar VI para windows.
2 colocar no path do windows C:\vim63\
3 No sqlplus executar o comando define _editor= "C:\vim63\vim.exe"
4 Se funcionou com o comando ed, colocar o comando do passo 3 no arquivo para ser permanente

C:\oracle\ora90\sqlplus\admin\glogin.sql

Arquivos *.bat (windows) - Para Inicializacao e finalizacao num so clique.

Para inicializacao ou finalizacao dos servicos oracle, coloque esses arquivos no desktop para facil acesso:

Oraclestart.bat:
net start OracleOraHome90TNSListener
net start OracleServiceDELLDB9I

OracleStop.bat:
net stop OracleOraHome90TNSListener
net stop OracleServiceDELLDB9I

ORA-27101: shared memory realm does not exist

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Aug 11 03:12:33 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Solution :

sqlplus /nolog

Uma vez no sqlplus, veja a minha sessão abaixo :

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 126950220 bytes
Fixed Size 453452 bytes
Variable Size 109051904 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> quit

A partir daqui a instancia volta a funcionar !

Melhorando SQL*Loader exemplo

Vi que o exemplo anterior de sql*loader esta com alguns problemas então decidi fazer um melhor, já q nada se cria e tudo se copia (e melhora). Acho q vale a pena fazer o exemplo abaixo, eh muito fácil.

Boa carga...


No sqlplus:
1) VERIFIQUE SE O USUARIO SCOTT ESTA HABILITADO E A SENHA ESTA CORRETA "TIGER" NO SQLPLUS

2) CRIE UMA TABELA PARA Seu TESTE, PARA NAO MODIFICAR A DE EXEMPLO. DIGITE:"CREATE TABLE EMP2 AS SELECT * FROM EMP;"

3) O ORACLE RECLAMARÁ QUE A TABELA CONTEM DADOS, ENTAO DIGITE: "TRUNCATE TABLE EMP2;"

No Windows:
4) ABRA O NOTEPAD, COLOQUE O CONTEUDO ABAIXO E SALVE COM NOME c:\LOADER.CTL

CONTEUDO DO MEU ARQUIVO LOADER.CTL
-----------------------------------INICIO DO ARQUIVO---------------------------------------------
load data
infile 'c:\mydata.csv'
into table emp2
fields terminated by "," optionally enclosed by '"'
( empno, ename, sal, deptno )
-----------------------------------FIM DO ARQUIVO---------------------------------------------

ABRA O NOTEPAD NOVAMENTE, COLOQUE O CONTEUDO ABAIXO E SALVE COM NOME c:\mydata.csv

1001,"Scott Tiger", 1000, 40
1002,"Frank Naude", 500, 20


5) Digite esse comando no DOS (command prompt) colocando o prompt no c:\

sqlldr scott/tiger control=loader.ctl

Isso gerará um log (c:\LOADER.LOG) onde será descrito o evento de carga.

6) vERIFIQUE O c:\LOADER.LOG abrindo o arquivo, vc verá o que causou problema e se as linhas foram inseridas.

CONTEUDO DO MEU ARQUIVO LOADER.LOG:

-----------------------------------INICIO DO ARQUIVO---------------------------------------------

SQL*Loader: Release 9.0.1.1.1 - Production on Thu Aug 9 13:30:13 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Control File: loader.ctl
Data File: c:\mydata.csv
Bad File: mydata.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table EMP2, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , O(") CHARACTER
ENAME NEXT * , O(") CHARACTER
SAL NEXT * , O(") CHARACTER
DEPTNO NEXT * , O(") CHARACTER


Table EMP2:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 66048 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Thu Aug 09 13:30:13 2007
Run ended on Thu Aug 09 13:30:13 2007

Elapsed time was: 00:00:00.10
CPU time was: 00:00:00.03

----------------------------------FIM DO ARQUIVO----------------------------------------------

7) No sqlplus Verifique os dados na tabela emp2 com o usuario scott/tiger
>select * from emp2

Voilà


#ATENÇAO#
SE ESSA MENSAGEM TE AJUDOU, DEIXE UMA MENSAGEM ANONIMA ME XINGANDO
#########

Scritps substituindo ferramenta ETL

Existem empresas que não possuem ferramenta de ETL (Extration Tranformation Loading), o caso mais comum para quem usa o Oracle, é usuar o SQL*Loader e scripts pl/sql.

Assim, deixo um link de SQL*Loader:

basico sql*loader

e programação pl/sql:

pl/sql


Bom treino.