database:oracle [IT Wiki]

Oracle

Maintain

su - oracust1                                                  #Authenticate as "oracust1" user
expdp parfile=exp_customer.par                                 #Starts dumping			
sqlplus /nolog @/scripts/drop_ts.sql CUSTOMER                  #Delete tablespace "CUSTOMER" and all related users
sqlplus /nolog @create_ts_cust.sql CUSTOMER SID                #Create tablespace, paramter 1(CUSTOMER)= Name of the tablespace, parameter 2 (SID) databaseinstance
sqlplus /nolog @/scripts/create_ts_cust.sql CUSTOMER sid1      #Create new tablespace "CUSTOMER" on "sid1"
sqlplus /nolog @/scripts/grant_user.sql USERNAME mypassword    #Create user "USERNAME" with password "mypassword"

Select

As follows some selects:

select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'CUSTOMER' and p.addr (+) = s.paddr;
alter system kill session '<sid>,<serial#>';

Example:

select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'CUSTOMER' and p.addr (+) = s.paddr;      #Determine session numbers of all sessions of a specific user.  
alter system kill session '140,193';                                                                                                 #Kill a single session that was determined with the above statement.

sqlplus / as sysdba                                            #Login as "sysdba" 
select count(*) from customer.queryindexitem;                  #Select tablespace "customer" and field "queryindexitem"
select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME='dba_objects';
select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME like 'DBA_%' and OBJECT_TYPE='TABLE';
select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME like 'V$%';

Result: 357874

TRUNCATE TABLE customer.queryindexitem;                        #Delete all entries from table "queryindexitem" in tablespace "customer"

Table truncated.
select count(*) from customer.queryindexitem;                  #Select number of entries from table "queryindexitem" in tablespace "customer"

Result: 0

Import

Requirement:

Tablespace has to exist!
Check export and import dir

  select * from dba_directories;                                                      #Check declared Oracle directories
  create or replace directory EXP_DIR as '/opt/oracle/ora_data/custx';                #Create or edit export directory
  create or replace directory DATA_PUMP_DIR as '/opt/oracle/ora_data/custx';          #Create or edit data pump directory
  Select username from dba_users;                                                     #To determine der databaseuser/database schema

su - oraxxx           
impdp parfile=imp_customer.par

Start/ Stop Oracle DB

To start a ORACLE DB on linux:

su – ora<xxx> (cust1, cust2, restore, train)
sqlplus / as sysdba
startup;

To speed up start for tests:

startup nomount;

To stop ORACLE DB:

su – ora<xxx>
sqlplus / as sysdba
shutdown immediate;

Listener

orarestore@hostname:~> lsnrctl start LIRESTORE
orarestore@hostname:~> lsnrctl stop LIRESTORE
orarestore@hostname:~> lsnrctl status LIRESTORE

oracust2@roth:~> lsnrctl
LSNRCTL> set current_listener LIRESTORE
Current Listener is LIRESTORE
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx)(PORT=1524)))
The listener supports no services
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx)(PORT=1524)))
The command completed successfully
LSNRCTL> start
Starting /opt/oracle/oracust2/bin/tnslsnr: please wait...

Check Connection

To check the connection to the database, login with the required user:

su - oracle
sqlplus /nolog
connect user/password

quit                                                                                #disconnect user

Checks

select * from dba_directories;                                                      #Check declared Oracle directories
create or replace directory EXP_DIR as '/opt/oracle/ora_data/custx';                #Create or edit export directory
create or replace directory DATA_PUMP_DIR as '/opt/oracle/ora_data/custx';          #Create or edit data pump directory
select username from dba_users;                                                     #To determine der databaseuser/database schema

Connecting SQL*PLUS with a shell script

#!/bin/bash

read_sql_stmt() {
  typeset stmt=$1
  typeset login=$2

  echo "
    set feedback off verify off heading off pagesize 0
    $stmt;
    exit 
  " |  sqlplus -s  $login
}

read_sql_stmt "select username, user_id from dba_users" "system/system_pw" | while read u i 
do
  echo "user $u has userid $i"
done

Setup

alter system set sga_max_size=10G scope = SPFILE;                #Set RAM
alter system set memory_max_target=10G scope = SPFILE;           #Set RAM
alter system set memory_target=10G scope = SPFILE;               #Set RAM
create spfile from pfile;                                        #pfile=readable  spfile=crypted and just readable from Oracle
mount -o remount,size=20g /dev/shm                               #Remount /dev/shm with 20G memory reservation

Check setup:

show parameter memory;
show parameter sga;

Set Parameter to configfile:

/opt/oracle/orarestore/dbs/initxxxxxx.ora                      #If changes were made on this file you have to execute "create spfile from pfile;"

Account

select t_login, t_password from schema.tablename;                                                               #Show all users within schema
update schema.tablename set t_password='48A538310CCA5506BBFF12208F6B5F1B' where t_login='SuperUser';            #Update Password for user "SuperUser" 
commit;                                                                                                         #Activate change

Useful Commands

select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME='dba_objects';
select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME like 'DBA_%' and OBJECT_TYPE='TABLE';
select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME like 'V$%';
select FILE_NAME from DBA_DATA_FILES;                                                                           #Check active tablespaces

sql>set linesize 300                                                                                            #Set linesize (lenght) to 300 (max is 32767)
sql>spool /tmp/testfile                                                                                         #Output sql-statement into a file "/tmp/testfile "
sql>spool off                                                                                                   #Swith off output to file

Table of Contents