==== Oracle ====

=== Installation ===
The Installation for Windows is described [[http://blog.mclaughlinsoftware.com/2011/12/29/oracle-11gr2-on-windows-7/ | here]] in detail.

===== Syntax =====
The syntax is listed [[http://ss64.com/ora/syntax.html|here]]

====Commands ====

Requirenments:
  - The environment variable should point to a tnsnames.ora: <code>TNS_ADMIN = \\folder\of\tnsnamesora\location </code>


== Ping the DB ==
To ping the DB - do:
<code>
tnsping AVSP31DB02_faredb
</code>

== Connect to the DB as passwordless system user "sysdba" ==
If you have forgotten the DB password - you can connect to the Oracle DB as system user sysdba.
For that the following requirenments should be fulfilled:

  - Set the environment variable **ORACLE_HOME**. This is the folder, which contains the "bin" directory. E.g.  <code>d:\oracle\11.2_SE\</code>
  - Add the **%ORACLE_HOME%** to the Path environment var as the first entry

Then Open a console and type:

<DBNAME> - the name of the DB. 
Achtung: ORACLE_SID soll so gesetzt werden, wie sie in **tnsnames.ora** auf die die Variable **TNS_ADMIN** zeigt, mit präfix. \\
Also z.B. **ANB13010_ivudb** wenn tnsnames.ora Eintrag so aussieht:
<code>
ANB13010_ivudb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ANB13010)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = ivudb))
  )
</code>


<code>
set ORACLE_SID=ANB13010_ivudb 
sqlplus /nolog
SQL> connect / as sysdba
</code>

To login to TNSNSNAMES key (here APCE12FARE01_faredb) use the following:
<code>
set ORACLE_SID=APCE12FARE01_faredb
sqlplus login/pass@TNSNAMESKEY

sqlplus login/pass@APCE12FARE01_faredb
</code>
For details see: http://doganay.wordpress.com/2012/09/12/connect-as-sysdba-on-windows-fails-with-ora-12560-tnsprotocol-adapter-error/

== Drop the Database ==
To drop a DB do login as "sysdba", as explained above.
<code>
SQL> shutdown immediate;
oracle database closed
oracle database dismounted
oracle instance shutdown
SQL> startup restrict mount;
SQL> drop database;

Database dropped
SQL> exit
</code>


==Login as ==
<code>
sqplus user/pass@dbname, e.g. cli/cli__cli@faredb
</code>

<code>
SELECT * FROM v$session; // Display logged in users. Session user are in the table **v$session**. CLIENT_INFO is the column, where userdefined locks may be saved.
</code>

== Select field with the type "date" ==
<code>
SELECT * FROM test_schichtabrechnung WHERE SCHICHT_START > TO_TIMESTAMP ('04/18/2013 10:03:27' ,'mm/dd/yyyy HH:MI:SS');
</code>

== Show All Constraints ==

To check all table constraints, which may prevent you from deleting table data do:
There you can see the dependent tables, lookup the data in this table, delete it and then delete the initial data.
<code>
select * from all_constraints ORDER BY CONSTRAINT_NAME DESC; 
</code>


== Modify Table ==
<code>
# insert a column of type max 2 Number, 0 Numbers after decimal point
ALTER TABLE limit_state ADD EVALUATION_NORM_VALUE NUMBER(2,0)
</code>

== Insert some Data ==
<code>
insert into GERAETE_SAM 
("GERAETE_ID","MANDANT_ID","SAM_NR","VDVKA_ORG_ID","GUELTIG_AB","GUELTIG_BIS","LETZTE_AKTUALISIERUNG","STATUS","LETZTER_ZUGRIFF","LETZTER_BENUTZER","ERSTELLT_AM","ERSTELLT_VON") 
values ('fzg_0018','DEF',4712,-1,TO_DATE('11.11.2008', 'DD.MM.YYYY'),TO_DATE('11.11.2018', 'DD.MM.YYYY'),TO_DATE('11.11.2008', 'DD.MM.YYYY'),1,TO_DATE('11.11.2012', 'DD.MM.YYYY'),'CLI',TO_DATE('11.11.2008', 'DD.MM.YYYY'),'CLI');


insert into USERCARD 
("ID","ORG_ID","CARD_TYPE","CARD_NR","VALID_FROM","VALID_TO","CARD_STATE","CARD_SYNCHRON_NR","MANDANT_ID","LOCKED") 
values ('uno',2,2,2,TO_DATE('11.11.2008', 'DD.MM.YYYY'),TO_DATE('11.11.2018', 'DD.MM.YYYY'),2,2,'DEF',0);

insert into SHIFT_STATE ("ID","SELLER_MANDANT_ID","SELLER_ID","DEVICE_MANDANT_ID","DEVICE_ID","SHIFT_ID","SHIFT_STATE","CASH_AMOUNT","REFRESH_TIMESTAMP") 
values ('7a0ecd62-0a01-490b-999-35f7a148b655','DEF',508,'DEF',1,NULL,1,19, to_date('2013/12/19:12:22:00PM', 'yyyy/mm/dd:hh:mi:ssam') );

# update 
update limit_state set EVALUATION_NORM_VALUE=6 WHERE EVALUATION_NORM_VALUE>6
</code>

== Modify DATE row ==
<code>
UPDATE schichtabrechnung SET DATUM_ABRECHNUNG=TO_DATE('12/03/2015 12:11:00', 'dd/MM/yyyy HH24:MI:SS') WHERE DATUM_ABRECHNUNG > TO_DATE('27/11/2014 12:11:00', 'dd/MM/yyyy HH24:MI:SS') AND DATUM_ABRECHNUNG < TO_DATE('27/11/2014 13:13:00', 'dd/MM/yyyy HH24:MI:SS');
  

</code>

== Export/ Import Data ==

Details are described here: http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

If DATA_PUMP_DIR does nt exist yet - create it in oracle.

<code>
expdp system/password@db10g full=Y directory=DATA_PUMP_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log
impdp system/password@db10g full=Y directory=DATA_PUMP_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

exp userId=cli/cli__cli@ac-kennedy_ivudb full=Y file=d:\datadump.dmp log=d:\datadumplog.txt
import using the newest dbutils
</code>


or exporting of a single table
<code>
exp userid=cli/cli__cli@ac-kennedy_ivudb file=CLI.usercard.dmp log=usercard.log compress=no tables=CLI.usercard statistics=none consistent=yes

</code>