Oracle Database Commands and Practical Examples

Sunday 9 October 2022

Multiple _kill session example

Multiple kill session example

SCHEMAS

select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where username='<schema_name>';

Session with username not sys

set pagesize 200

select 'alter system kill session '''|| sid||','||serial#||''' immediate ;' from v$session where type <> 'BACKGROUND' and username <> 'SYS';

SQL ID

SQL>select 'alter system kill session '''||SID||','||SERIAL#||''' immediate;' from v$session where sql_id='1amhfbhjmfn6y';

Expdp - Par file examples

Expdp - Par file examples

Table par file examples

cat expdp_TABLE.par

USERID = '/ as sysdba'

DIRECTORY = <Directory_name>

DUMPFILE = Table_Target_%U.dmp

LOGFILE = expdp_Table.log

tables = <schema_name>.<Table_name>

PARALLEL = 4

FILESIZE = 10G

EXCLUDE=STATISTICS

METRCIS=Y

LOGTIME=ALL

Schema par file examples

cat expdp_Schemas.par

USERID = '/ as sysdba'

DIRECTORY = <dump_directory>

DUMPFILE = <dumpfile_name>%U.dmp

LOGFILE = <logfile_name>.log

schemas = <schema_name>,<schema_name>,<schema_name>

PARALLEL = 4

FILESIZE = 10G

EXCLUDE=STATISTICS

METRCIS=Y

LOGTIME=ALL

Exclude Tables Examples :

directory=<directory_name>

SCHEMAS=<schema_name>.<schema_name>

exclude=TABLE:"IN(select table_name from dba_tables where table_name like '<schema_name>.<table_name>')"

dumpfile=EXPDP_source%U.DMP

logfile=IMPDP_source.log

parallel=30

directory=<directory_name>

content =metadata_only

schemas = <schema_name>,<schema_name>,<schema_name>

dumpfile=metadata_.DMP.%U

logfile=metadata__.log

full=y

col OWNER for a20

col DIRECTORY_NAME for a30

col DIRECTORY_PATH for a70

set lines 200

select * from dba_directories;

Export example

exp \'/ as sysdba\' file=<directory_location>/six_tabs.dmp log=<directory_location>/six_tabs.log tables=<schema_name>.<Table_name>,<schema_name>.<Table_name>,<schema_name>.<Table_name> statistics=none feedback=10000

BUFFER=100000

set lines 200

col JOB_NAME for a20

col OWNER_NAME for a30

col OPERATION for a20

col JOB_MODE for a30

col STATE for a30

SELECT owner_name, job_name, operation, job_mode,state, attached_sessions FROM dba_datapump_jobs ORDER BY 1,2;

select distinct tablespace_name from dba_segments where owner='<schema_name>';

select sum(bytes/1024/1024/1024) from dba_segments where owner in('<schema_name>','<schema_name>','<schema_name>','<schema_name>','<schema_name>','<schema_name>');

select count(*),object_type,status from dba_objects where owner='<schema_name>' group by status,object_type;

SELECT sum(bytes)/1024/1024/1024 as "Size in GB" from dba_segments WHERE owner = UPPER('&schema_name');

Set heading off

SQL> select name from v$database;

SQL> spool /<directory_path>/drop_objects.sql

SQL> select 'drop '||object_type||' '||owner||'."'|| object_name||'"' || DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS purge;',';') from dba_objects where owner='<schema_name>';

SQL> spool off

set heading on

set echo on

set pagesize 0

set feedback on

set verify on

SQL> spool /<directory_path>/drop_objects.log

SQL> @/dvra01/<directory_path>/drop_objects.sql

SQL> spool off

SQL>

import same schema but different tablespace:

cat impdp.par

directory=<directory_name>

dumpfile=export_schemas.dmp

logfile=import_schemas.log

schemas=<Schema_name>,<schema_name>

REMAP_TABLESPACE=<source_tablespace>:<Target_tablespace>

parallel=2

Data only impdp on different tables

impdp directory= dumpfile=.DMP logfile=impdp1.log remap_table=<schema_name_source>.<tab_name_source>:<schema_name_trgt>.<trgt> CONTENT=DATA_ONLY DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

Impdp examples

cat impdp_target.par

directory=<dir_name>

SCHEMAS=<schema_name>

dumpfile=1.%U

logfile=IMPDP.log

parallel=11

transform=disable_archive_logging:Y

nohup expdp \"/ as sysdba\" parfile=fulldb_export.par &

cat impdp_table.par

USERID = '/ as sysdba'

DIRECTORY = <>

DUMPFILE = <>.dmp.%U

LOGFILE = <>_impdp.log

CONTENT = ALL

PARALLEL = 40

tables = <schema_name>.<table_name>,<schema_name>.<table_name>

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:table

exclude=STATISTICS

METRICS=Y

LOGTIME=ALL

Object _count

SQL> SET SERVEROUTPUT ON SIZE 1000000

SQL> --

SQL> DECLARE

2 VAL NUMBER;

3 BEGIN

4 DBMS_OUTPUT.ENABLE(1000000);

5 FOR I IN (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER IN ('<schema_name>') AND OBJECT_TYPE = 'TABLE' ORDER BY OBJECT_NAME) LOOP

6 EXECUTE IMMEDIATE 'SELECT count(*) FROM <schema_name>.' || i.OBJECT_NAME INTO val;

7 DBMS_OUTPUT.PUT_LINE(I.OBJECT_NAME || ': ' || VAL );

8 END LOOP;

9 END;

10 /

select SEGMENT_NAME,sum(bytes/1024/1024/1024) GB from dba_segments where OWNER='<>' and SEGMENT_NAME='<>' group by segment_name,segment_type;

TRIGGERS

SQL> select OWNER,TRIGGER_NAME,TABLE_OWNER,STATUS from DBA_TRIGGERS where OWNER='<>' and TRIGGER_NAME IN ('<>,<>,<>');

Table size check

select segment_name,segment_type, sum(bytes/1024/1024/1024) GB

from dba_segments

where segment_name='&Your_Table_Name'

group by segment_name,segment_type;

Nohup expdp and impdp example

nohup expdp \"/ as sysdba\" parfile=fulldb_export.par &

nohup impdp \"/ as sysdba\" parfile=fulldb_export.par &

pbrun -u oracle runshell

$nohup expdp parfile=expdp_DBNAME_SCHEMA.par &

$nohup impdp parfile=impdp_DBNAME_SCHEMA.par &

Oracle Zabbix by ODBC

Oracle Zabbix by ODBC

Oracle Database, version 12c2, 18c, 19c

· Create an Oracle DB user for monitoring:

· Create User on oracle db user for monitoring on target database

· Install Oracle Client on Zabbix mysql database server

· Setup environment variables on bash_profile

· Create tnsnames.ora file with sample connect descriptor

· Add entry on odbc.ini

· Test using i sql connection

· Add host entry & select template options

· Add Macro Entry on Zabbix Console

https://www.zabbix.com/integrations/oracle

STEP 1:

CREATE USER zabbix_mon IDENTIFIED BY <PASSWORD>;

-- Grant access to the zabbix_mon user.

GRANT CONNECT, CREATE SESSION TO zabbix_mon;

GRANT SELECT ON v$instance TO zabbix_mon;

GRANT SELECT ON v$database TO zabbix_mon;

GRANT SELECT ON v$sysmetric TO zabbix_mon;

GRANT SELECT ON v$system_parameter TO zabbix_mon;

GRANT SELECT ON v$session TO zabbix_mon;

GRANT SELECT ON v$recovery_file_dest TO zabbix_mon;

GRANT SELECT ON v$active_session_history TO zabbix_mon;

GRANT SELECT ON v$osstat TO zabbix_mon;

GRANT SELECT ON v$restore_point TO zabbix_mon;

GRANT SELECT ON v$process TO zabbix_mon;

GRANT SELECT ON v$datafile TO zabbix_mon;

GRANT SELECT ON v$pgastat TO zabbix_mon;

GRANT SELECT ON v$sgastat TO zabbix_mon;

GRANT SELECT ON v$log TO zabbix_mon;

GRANT SELECT ON v$archive_dest TO zabbix_mon;

GRANT SELECT ON v$asm_diskgroup TO zabbix_mon;

GRANT SELECT ON sys.dba_data_files TO zabbix_mon;

GRANT SELECT ON DBA_TABLESPACES TO zabbix_mon;

GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO zabbix_mon;

GRANT SELECT ON DBA_USERS TO zabbix_mon;

STEP 2:

Install Oracle Client on Zabbix mysql Repository database server

STEP 3 :

Set Environment variable

export ORACLE_HOME=/usr/lib/oracle/12.2/client64

export PATH=$PATH:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:/usr/lib:$ORACLE_HOME/bin

export TNS_ADMIN=$ORACLE_HOME/network/admin

STEP 4 :

Add tnsnames.ora entry on oracle client

cat =/usr/lib/oracle/12.2/client64/network/admin/tnsnames.ora

Add entry on tnsnames.ora file

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ORCL.EXAMPLE.COM)(PORT = 1523))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

STEP 4 :

Add Value in /cat /odbc.ini file

[root@0209RUPAV006TVS etc]# cat odbc.ini

[db12c]

Driver = Oracle12c

Driver =/usr/lib/oracle/12.2/client64/lib/libsqora.so.12.1

Servername = 10.33.104.254:1523/ ORCL

Database = ORCL

Port = 1523

UserID = zabbix_mon

Password = zabbix_mon

DatabaseCharacterSet=AL16UTF16

STEP 5:

Test on sql console

[root@test etc]# isql -v ORCL

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

SQL> select name from v$database;

+----------+

| NAME |

+----------+

| ORCL |

+----------+

SQLRowCount returns -1

1 rows fetched

STEP 6 :