Differences

This shows you the differences between two versions of the page.

--- database:oracle [2014/08/28 07:27] – skipidar
+++ database:oracle [2023/11/01 07:13] (current) – ↷ Page moved from business_process_management:camunda:database:oracle to database:oracle skipidar
@@ Line 1: / Line 1: @@
+==== 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>