Difference between DDL, DML and DCL commands Posted by MOHAMMED ABDUL HAMEED on 11:40 PM

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Environment Variable for Oracle Database Posted by MOHAMMED ABDUL HAMEED on 12:28 AM

How to check if ORACLE_HOME is set already?


To check specific environment variable set:
$ echo $ORACLE_HOME

To check all the environment variables set:

$ env

On Windows Systems:

To check specific environment variable set:

C:\> set ORACLE_HOME

OR

C:\echo %ORACLE_HOME%

To check all the environment variables set:

C:\> set

Or

C:\> env

Other way, to check the ORACLE_HOME, is as follows.
Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLE

i.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

How to check using sqlplus command:
To find the ORACLE_HOME path in Oracle Database

How to set the ORACLE_HOME environment variable?
On Unix/Linux Systems:

Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profile

ORACLE_HOME=$ORACLE_BASE/product/10.2.0
export ORACLE_HOME

On Windows Systems:

My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)

After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.

Another way to physically set the variables as follow at the DOS prompt:

C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0
C:\> echo %ORACLE_HOME%

Linux 

export ORACLE_SID=COREDB
export ORACLE_HOME=/u01/home/app/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH:

Tablespace in Oracle Posted by MOHAMMED ABDUL HAMEED on 12:23 AM

A Tablespace is a container for segments (tables, indexes, etc). A database consists of one or more tablespaces, each made up of one or more data files. Tables and indexes are created within a particular tablespace.

When a New database is created, it will have the following tablespaces :

SYSTEM (the data dictionary)
SYSAUX (optional database components)
TEMP (temporary tablespace)
UNDOTBS1 (undo tablespace, contains pre image data)
USERS (default users tablespace created)

Syntax :

Create tablespace
Datafile '/u01/home/prd/cmm.dbf' size 100m 
autoextend on 
next 100m maxsize unlimited;

Check Free/Used space per tablespace :

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Sample output:

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- -----------------
    UNDOTBS1                               65    17.8125         27         73
     EXAMPLE                               100     22.625         23         75
    USERS                                         5     1.0625         21         79
    TEMP                                         20          2             10         90
   SYSAUX                               625.125     304.5        48      52
   SYSTEM                                  700     9.0625          1         99

Oracle Data Pump (expdp and impdp) in Oracle Database 11g Posted by MOHAMMED ABDUL HAMEED on 5:09 PM

Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

Table Exports/Imports

expdp system@databasename tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp system@databasename tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

Schema Exports/Imports

expdp system@databasename schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp system@databasename schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

expdp system@databasename full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

impdp system@databasename
  full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=impdpDB11G.log

To Create a Logical Directory : 

CREATE DIRECTORY datapump AS 'C:\oradata\datapump';
GRANT EXP_FULL_DATABASE  to scott;

GRANT READ, WRITE ON DIRECTORY datapump to scott;

DB_RECOVERY_FILE_DEST - Alter parameters ORACLE Posted by MOHAMMED ABDUL HAMEED on 11:19 PM

DB_RECOVERY_FILE_DEST specifies the default location for the flash recovery area backups. The flash recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups.

Specifying this parameter without also specifying the DB_RECOVERY_FILE_DEST_SIZE initialization parameter is not allowed

Below are the steps to Change  DB_RECOVERY_FILE_DEST & DB_RECOVERY_FILE_DEST_SIZE Parameter:

[oracle@]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 16 09:30:39 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

-------------------------------- ----------- ------------------------------

db_recovery_file_dest                string      /opt/oracle/test01/flash_recovery_area

db_recovery_file_dest_size           big integer 10G

Command for changing db_recovery_file_dest

SQL> alter system set db_recovery_file_dest='/opt/oracle/test01/dbs/arch' scope=both;

System altered.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /opt/oracle/test01/dbs/arch

db_recovery_file_dest_size           big integer 10G

Command for changing db_recovery_file_dest_size

SQL>  alter system set db_recovery_file_dest_size=20G scope=both;

System altered.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /opt/oracle/test01/dbs/arch

db_recovery_file_dest_size           big integer 20G

Flash/Fast Recovery Area (FRA) in Oracle Posted by MOHAMMED ABDUL HAMEED on 10:08 PM

Flash Recovery Area can be defined as a single, centralized, unified storage area that keep all the database backup & recovery related files and performs those activities in Oracle databases.

Related views
V$RECOVERY_FILE_DEST
V$FLASH_RECOVERY_AREA_USAGE
V$DBA_OUTSTANDING_ALERTS
V$FLASHBACK_DATABASE_LOGFILE

# Instance Name

select instance_name from v$instance;

# Used GB in FRA

select name

, floor(space_limit / 1024 / 1024/1024) "Size GB"

, ceil(space_used  / 1024 / 1024/1024) "Used GB"

from v$recovery_file_dest

order by name;

# FRA Occupants

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

SELECT NAME,

TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,

TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999') AS SPACE_AVAILABLE,

ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL

FROM V$RECOVERY_FILE_DEST;

select * from v$Recovery_file_dest;

SELECT

ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)

AS PERCENT_FULL

FROM V$RECOVERY_FILE_DEST;

# Location and size of the FRA

show parameter db_recovery_file_dest;

# Size, used, Reclaimable

SELECT

  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,

  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,

  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,

  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED

FROM

  V$RECOVERY_FILE_DEST A,

  V$FLASH_RECOVERY_AREA_USAGE B

GROUP BY

  SPACE_LIMIT,

  SPACE_USED ,

  SPACE_RECLAIMABLE ;

How to schedule tasks on Linux using the 'at' command Posted by MOHAMMED ABDUL HAMEED on 12:44 AM

Scheduling jobs is an essential part of administering Linux servers. We took a look at how to schedule jobs on Linux machine using the cron command earlier. Here’s an alternative to cron –at. The primary difference between the two is that when you schedule a task using cron it execute repeatedly without the need for rescheduling. With at, on the other hand, the scheduling of a task is only for a single execution. Both of these commands have their use, and I would suggest that you get a good understanding of them both.

Let’s look at how to schedule a task to execute only once using the at command. First make sure that the at daemon is running using a command like this:

# ps -ef | grep atd

The at command is pretty clever in that it can take some orders in English if you like. For example, you can schedule jobs using the following syntax as well:

root 8231 1 0 18:10 ? 00:00:00 /usr/sbin/atd


If you don’t see atd running start it with this command:


# /etc/init.d/atd start


Once the daemon has been started successfully you can schedule an at task using the two options -f, for the file to be executed, and -v, for the time at which it should be executed. So if you want to execute the shell script shellscript.sh at 6:30 PM you would run the following command:


# at -f shellscript.sh -v 18:30


Remember that with the at command the script shellscript.sh will execute at 6:30 PM and then the scheduling will disappear. So if this is not what you desire, you are better off using cron.


# at -f shellscript.sh 10pm tomorrow

# at -f shellscript.sh 2:50 tuesday

# at -f shellscript.sh 6:00 july 11

# at -f shellscript.sh 2:00 next week

Posted by MOHAMMED ABDUL HAMEED on 11:50 PM

# FTP SCRIPT TO TRANSFER MULTIPLE FILES #


HOST='XXXXXX'
USER='XXXX'
PASSWD='XXX'
FILE='*.txt'


ftp -v -n -i $HOST <
quote USER $USER
quote PASS $PASSWD
mput FILE $FILE
quit
END_SCRIPT
exit 0

Posted by MOHAMMED ABDUL HAMEED on 11:46 PM

# FTP SCRIPT TO TRANSFER ONE FILE #


HOST='xxxxx'
USER='xxx'
PASSWD='xxxx'
FILE='inst.txt'

ftp -n -v $HOST << END_SCRIPT
quote USER $USER
quote PASS $PASSWD
put inst1.txt
quit
END_SCRIPT
exit 0