Get Time from Oracle DB in SQLPlus

select systimestamp from dual;

Bash Script to run a sql file (Draft) - Part 1

#!/usr/bin/bash

# This script calls the Ad_1.sql script and spools the output with date stamp to a log file
# Update: You MUST add your environment variables one way or another, e.g. look below


export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/OraHome_1
PATH=${PATH}:${ORACLE_HOME}/bin; export PATH
export LIBPATH=${LIBPATH}:${ORACLE_HOME}/lib32:${ORACLE_HOME}/lib


if [ ! -f lock ]; then

sqlplus -s user/password@sid << EOF
column dcol new_value mydate noprint
select sysdate dcol from dual;
spool log_ad_1_&mydate..log
@Ad_1.sql;
spool off
EOF

fi

where:

if [ ! -f lock ]: very useful statement, it checks whether a file named "lock" exists or not, if it doesn't exist, it will not execute the code block within.

sqlplus -s usr/passwod@sid: to run sqlplus commands as if I'm entering the commands manually, the -s flag lets sqlplus accept the standardin input until it finds the word "EOF"


Part 2 will be about adding the above script in crontab and executing it daily.

Putting the Current Date in a Spool File Name in sqlplus

 Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;
spool &mydate.report.txt


-- my report goes here
select last_name from employees;
spool off

How to add a new data file to Oracle Tablespace using sqlplus

First login as the "oracle" user, and issue the command "sqlplus "/ as sysdba"" (I am assuming that you already have configured the 

PATH environment variable correctly) and then type the following:

alter tablespace tablespace_name 

add datafile '/oracle/data/file1.dbf' size 12G autoextend off;

The above command adds a data file named "file1.db" of size 12 GB to the "/oracle/data" path with the autoextend feature turned off

Source: http://www.adp-gmbh.ch/ora/sql/alter_tablespace.html