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