⬅️ **[[$-Tools|Tools]]** *** # Oracle ## Tools - Oracle SQL Developer - DBiewer ## SQL Commands and Folders | Command | Description | |:------------------ |:-------------------------------------------------------------------------------------------------------------------------------- | | SQL Plus Login | `sqlplus / as sysdba` | | Count Entries | `select count(*) as AllEntries <from Table_Name>;` | | Truncate | `TRUNCATE table PRIMA_PLUS_ISI_EM_RULES; commit;` | | Delete | `delete from prima_plus_cfss_cardinalities where version_number != (select max(version_number) from prima_plus_mapping_info);` | | Nested Selects | `delete from prima_plus_cfss_cardinalities where version_number != (select max(version_number) from prima_plus_mapping_info);` | | Date and Time | `select sessiontimezone from dual;` --> SESSIONTIMEZONE = +02:00 | | | `select current_timestamp from dual;` --> CURRENT_TIMESTAMP = 23-OCT-18 04.52.18.708580 PM +02:00 | | Drop Table | `DROP TABLE table_name;` | | Create Table | `CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );` | | Show Session Stats | `select * from v$resource_limit where resource_name = 'processes';` | | | `select name, value from v$sysstat where name like '%logon%';` | | | `show parameter sessions;` | | | | | Folder | Description | |:--------------------------------------- |:-------------------------------------------------------- | | `app/oracle/diag/rdbms/orcl/orcl/trace` | Error Traces (orcl war Instanz). `.trc` and `.trm` files | SELECT * FROM V$VERSION; select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; ## User ### Umgebungsvariablen für oracle User In `~/.bash_profile` folgendes eintragen: ```bash export ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1 ## for lsnrctl, ... export LD_LIBRARY_PATH=$ORACLE_HOME/lib export ORACLE_SID=orcl export ORACLE_UNQNAME=orcl export PATH=$PATH:$ORACLE_HOME/bin ``` ### User and Password Management - [-> Oracle Turn off Oracle Password Expiration](https://stackoverflow.com/questions/1095871/how-do-i-turn-off-oracle-password-expiration#6777079) - Passwort aktualisieren: `alter user <USER> identified by <PW>;` - Disable Password Expiration: - `select resource_name,limit from dba_profiles where profile='<profile_name>';` - `select profile from DBA_USERS where username = '<username>';` - `alter profile <profile_name> limit password_life_time UNLIMITED;` ## Start / Stop ### Autostart In `/etc/rc.local` folgendes eintragen: ```bash su - oracle -c 'dbstart' su - oracle -c 'lsnrctl start' su - oracle -c 'emctl start dbconsole' (falls vorhanden) ``` ### Start / Stop DB ```bash sudo su - oracle lsnrctl status sqlplus / as sysdba SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. lsnrctl status sqlplus / as sysdba SQL> startup ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2233344 bytes Variable Size 910167040 bytes Database Buffers 150994944 bytes Redo Buffers 5541888 bytes Database mounted. Database opened. lsnrctl status ``` ### Start / Stop DB Listener ```bash sudo su - oracle lsnrctl status lsnrctl start lsnrctl stop ``` ## Install Oracle ``` sudo mkdir /opt/mount1/oracle sudo ln -s /opt/mount1/oracle /home/oracle sudo chmod ugo+w /opt/mount1/oracle sudo useradd oracle sudo passwd oracle --> 1qay”WSX sudo yum install xdpyinfo compat-libcap1 compat-libstdc++-33 libaio-devel –y Increase temporarily the swap file by adding another file to minimum 8 GB: sudo dd if=/dev/zero of=/opt/mount1/extraswap bs=1M count=8096 sudo mkswap /opt/mount1/extraswap sudo swapon /opt/mount1/extraswap swapon -s sudo mkdir /opt/mount1/tmp sudo chmod 777 /opt/mount1/tmp unzip linuxamd64_12102_database_1of2.zip unzip linuxamd64_12102_database_2of2.zip 1. Starte XMing auf Laptop 2. Login via SSH: DISPLAY=localhost:0.0 ssh -Y oracle@vre12 3. Starte nun Oracle Installation cd /opt/mount1/tmp/database/ ./runInstaller 4. Xming Öffnet sich mit Oracle Installation Wizard - Desktop Class Installation - Advanced Install Type - Uncheck „Pluggable DB“ / „Create as Container DB“ - Administrative Password: 1qay2wsx§EDC - Configuration - Pfad `/home/oracle/app/oracle` - orcl - PW - ``` ## Export und Import von User mit all seinen Tabellen ```bash sudo su - oracle exp cw/cw@orcl FULL=Y FILE=cw.dmp imp cw/cw@orcl FULL=Y FILE=cw.dmp ``` ## Fixes ### Fix - ORA-12519, TNS:no appropriate service handler found ``` select * from v$resource_limit where resource_name = 'processes'; cmd>sqlplus / as sysdba sqlplus>alter system set processes=600 scope=spfile; sqlplus>shut immediate; sqlplus>startup ``` ### Fix - SQLRecoverableException: IO Error: The Network Adapter could not establish the connection - check Listener - check tnsnames - check IP adress, Hostname - check connection string - check if SQL Connection are closed properly in the Code - optimize code to use a single connection object as Singleton. Only create a new one, if the connection object is not set. # *** Related: - [[Datenbanken]]