Oracle

CLI

${ORACLE_HOME}/bin/sqlplus -s user1/pass1 <<EOF
select * from TABLE1;
delete from TABLE1;
quit
EOF
 
echo "select * from TABLE1;" | ${ORACLE_HOME}/bin/sqlplus user1/pass1

Show all tables

select table_name from all_tables;

Links
ORACLE Free and Open Source Software
http://oss.oracle.com/

Oracle Database 10g Express Edition
http://oss.oracle.com/oracle-database-xe.html

Oracle SQL Developer
http://oss.oracle.com/sqldeveloper.html

Backup
https://oracle-base.com/articles/11g/data-pump-enhancements-11gr1

su oracle -
 
$ crontab -l
MAILTO=foo@example.com
ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1
 
0 20 * * 0-5 ${ORACLE_HOME}/bin/expdp system/PASSWORD@INSTANCE full=y directory=backup compression=all dumpfile=INSTANCE.full.$(date -I).dmp logfile=INSTANCE.full.$(date -I).log
0 21 * * * find /oracle/backup -mtime +3 -name "INSTANCE.full.*" -exec rm {} \;

Export single schema

${ORACLE_HOME}/bin/expdp USER/PASS schemas=SCHEMA_NAME directory=backup dumpfile=INSTANCE.full.$(date -I).dmp logfile=INSTANCE.full.$(date -I).log 

Import

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE
${ORACLE_HOME}/bin/imp user1/pass1 FROMUSER=user2 TOUSER=user3 TABLES=table1,table2 file=${FILE} log=/tmp/imp.log
 
# schemas=schema1,schema2
# remap_schema=schema1:schema2
# remap_tablespace=user1:user2
# full=yes
 
/oracle/app/product/11.2.0/dbhome_1/bin/impdp user1/pass1 TABLES=table1,table2 TABLE_EXISTS_ACTION=REPLACE DUMPFILE=BACKUP:db1.full.dmp LOGFILE=BACKUP:imp.$(date -I).log

# docker
https://hub.docker.com/r/wnameless/oracle-xe-11g/

docker run -d -p 49160:22 -p 49161:1521 -v /storage/dpdump:/u01/app/oracle/admin/XE/dpdump wnameless/oracle-xe-11g
sshpass -p admin ssh root@localhost -p 49160
 
docker stop $(docker ps -q --filter ancestor=wnameless/oracle-xe-11g)

Webgui
https://db.example.com:1158/em/console/logon/logon

Compile
https://oracle-base.com/articles/misc/recompiling-invalid-schema-objects

# tns listener
netstat -tulpen | grep 1521
lsnrctl start

# autostart
https://blogs.oracle.com/opal/how-i-enable-autostarting-of-oracle-database-for-demonstrations-and-development