Oracle 11g Logo

How to extract the Oracle table definition (Data Defining Language)

How to extract the Oracle table creation definition (Data Defining Language) from an Oracle database without digging dictionary views?

With the help of GET_DDL() function of DBMS_METADATA metadata package.

Syntax:

select DBMS_METADATA.GET_DDL(‘TABLE’,’TABLE_NAME’) from DUAL;

SET LONG 1000

SET PAGESIZE 0 Continue reading

Oracle 11g Logo

How to fetch last inserted row from any oracle table

This is easily done with the date datatype or timestamp datatype.

Ex:

select empno,ename,to_char(hiredate,’dd mon yyyy’) as hiredate
from emp
where hiredate =( select max(hiredate) from emp );

EMPNO      ENAME      HIREDATE
———- ———- ———–
7876       ADAMS      23 may 1987 Continue reading

Oracle 11g Logo Oracle 11g Logo Oracle 11g Logo

Oracle Enterprise/Standard/Standard-one Edition Installation on CentOS 6.2

Installation of Oracle Enterprise/Standard/Standard-one Edition on CentOS release 6.2 (Final)

//Find memory information by issuing following command:

grep -i memtotal /proc/meminfo
[root@centos ~]# grep -i memtotal /proc/meminfo
MemTotal: 497680 kB
//Find swap memory information by issuing following command:
grep -i swaptotal /proc/meminfo Continue reading

Oracle 11g Logo

Oracle 11gR2 installation failed due to “error in invoking target ‘irman ioracle’ of makefile bms.mk”

Oracle 11gR2 installation failed due to error in invoking target ‘irman ioracle’ of makefile bms.mk

I had ensured all oracle packages were installed properly prior to starting installation & oracle installer did not give any warning in prerequisites check.

so i ensure swap memory is sufficient or not, In my case swap space utmost consumed, so after increasing swap space setup has been started successfully.

Continue reading

Oracle 11g Logo

Script to extract size of Oracle tablespaces & associated datafiles

By issuing below script you will extract the total size of tablespces along with Total space available in MB, Free space available in MB & Free percentage(%)

Syntax:
COLU Free% FORMA A10
COLUMN tablespace_name FORMAT A17
SELECT t.tablespace_name, t.total_space_in_MB, f.free_space_in_MB,
TO_CHAR((f.free_space_in_MB*100/t.total_space_in_MB),'99990.000')
"Free%"

FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 Total_space_in_MB FROM DBA_DATA_FILES GROUP BY tablespace_name) t,
(SELECT tablespace_name, SUM(bytes)/1024/1024 Free_space_in_MB FROM DBA_FREE_SPACE GROUP BY
tablespace_name) f WHERE t.tablespace_name= f.tablespace_name;

OR

 

select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by 1;

 

 

By issuing below script you will extract the size & path of datafiles along with tablespace name, allocated space in MB, used space in MB & free space in MB.

Syntax:
set linesize 200
set pagesize 2000
COLUMN tablespace_name format a10
COLUMN file_name format a45
COLUMN free% format a7
SELECT df.tablespace_name,SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,
round(((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)),1) used_mb,
round(NVL (SUM (dfs.bytes) / 1024 / 1024, 0),2) free_space_mb
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+)
GROUP BY df.tablespace_name, dfs.file_id, df.file_id , df.bytes, df.file_NAME
ORDER BY df.tablespace_name;

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

Script to extract total objects & size of all Schemas

Script to extract total objects & size of all Schema’s:

By issuing below script you will find out all schema’s along with size they have yet consumed, script will find out all users. ( system users + user defined users)
Also you can find out total number of objects contains that schema.

 

Issue following query as sys user:

select obj.owner "Owner", obj_cnt "Objects",
decode(seg_size, NULL, 0, seg_size) "size MB"
from ( select owner, count(*) obj_cnt from dba_objects group by owner) obj,
( select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 3 desc, 2 desc, 1;

Owner                          Objects     size MB
——————————     ———-      ———-
SYS                            30965       990
XDB                            842          128
APEX_030200             2406         78
SYSMAN                     3491         46

. .. …

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have a easy life ahead.

Oracle 11g Logo

Script to extract Up time of Oracle database

By issuing following script you will extract exact uptime of database cum Hostname, Instance Name & Database Start time:

Syntax:
 column hostname format a26
 column “Instance Name” format a16
 column “Started At” format a26
 column “Database Uptime” format a52
 SELECT
 host_name as Hostname,
 instance_name as “Instance Name”,
 to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) as “Started_At”,
 floor(sysdate – startup_time) || ‘ days(s) ‘ ||
 trunc( 24*((sysdate-startup_time) -
 trunc(sysdate-startup_time))) || ‘ hour(s) ‘ ||
 mod(trunc(1440*((sysdate-startup_time) -
 trunc(sysdate-startup_time))), 60) ||’ minute(s) ‘ ||
 mod(trunc(86400*((sysdate-startup_time) -
 trunc(sysdate-startup_time))), 60) ||’ seconds’ as “Database_Uptime”
 FROM
 sys.v_$instance;

HOSTNAME

——–
Instance Name Started_At
——– —————
Database_Uptime
————————————–
localhost.localdomain
DemoDB 13-APR-2013 12:11:40
216 days(s) 22 hour(s) 1 minute(s) 52 seconds

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

Script to figure out total number of log switch occurred

Script to find out total number of archive logs generated for hourly, daily, monthly & yearly.

This will help you to figure out:
– The transnational work load on your database.
– The total number of archive log files being generated.
– To schedule or Re-schedule your RMAN transnational log backup policy.

// Script to find-out hourly log switch count:
set pages 1000
select to_char(COMPLETION_TIME,'dd mon yyyy hh24') as Hour,
thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by to_char(COMPLETION_TIME,'dd mon yyyy hh24'),thread# order by 1;
// Script to find-out daily log switch count:
 SELECT to_char(first_time, 'dd-mon-yyyy') as "Date", 
 count(*) as "Daily log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'dd-mon-yyyy');

OR

SELECT A.*, Round(A.Count*B.AVG/1024/1024) Daily_average_MB
 FROM ( SELECT To_Char(First_Time,'YYYY-MM-DD') Day,
 Count(1) Count,
 Min(RECID) Min,
 Max(RECID) Max
 FROM v$log_history
 GROUP BY To_Char(First_Time,'YYYY-MM-DD')
 ORDER BY 1 DESC ) A,
 ( SELECT Avg(BYTES) AVG,
 Count(1) Count#,
 Max(BYTES) Max_Bytes,
 Min(BYTES) Min_Bytes
 FROM v$log ) B;

// Script to find-out monthly log switch count:

 SELECT to_char(first_time, 'yyyy-mon') as "Year:Month",
 count(*) as "Daily log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'yyyy-mon')
 ORDER BY 1;
// Script to find-out yearly log switch count:
 SELECT to_char(first_time, 'yyyy') as "Year", 
 count(*) as "Yearly log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'yyyy');

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have a easy life ahead.

Oracle 11g Logo

Script to find fragmentation in Oracle tablespaces

By issuing following script you will extract fragmentation in tablespaces cum TABLESPACE NAME, FREE CHUNKS & LARGEST CHUNK of that tablespace:

Syntax:

 SELECT
 tablespace_name, 
 count(*) free_chunks,
 decode(round((max(bytes) / 1024000),2),
 null,0,
 round((max(bytes) / 1024000),2)) largest_chunk,
 nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
 FROM
 sys.dba_free_space 
 group by 
 tablespace_name
 order by 2 desc, 1;

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

ORA-06510 & ORA-06512 warnings during oracle export backup

Since couple of days, I have set cron job for oracle 11g database backup with the export utility. Every export backup, i have encountered “ORA-06510: PL/SQL: unhandled user-defined exception” & “ORA-06512: at SYS.DBMS_EXPORT_EXTENSION” warnings.

After searching web, I came to know that user who has backup oracle database is ordinary user & user having lack of execute privileges that: DBMS_EXPORT_EXTENSION and DBMS_DEFER_IMPORT_INTERNAL. Continue reading

Oracle 11g Logo

ORA-28002 : the password will expire within 5 days – WARNING from recovery catalog database

Today morning, I have encountered “RMAN-04007: WARNING from recovery catalog database” & “ORA-28002: the password will expire within 5 days” warnings while logged in to RMAN prompt for daily database activities as below.

[oracle@dbserver ~]$ rman target / catalog rman/rman123@cata
Recovery Manager: Release 11.2.0.3.0 – Production on Mon Mar 3 14:47:41 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Continue reading

Oracle 11g Logo

Data Pump Export-Import Performance tips

Oracle Data Pump offered lots of benefits & performance gain over original Export/Import. We can tremendously increase data pump export/import performance by considering following several methods:

Export performance tips:

  • parallelism in data pump. 

With the help of PARALLEL parameter ( tuning parameter ), we can achieve dynamic increase & decrease of resource consumption for each job. Worker (Parallel) count should be EXACT no of dump file & twice the Continue reading

Oracle 11g Logo

INCLUDE & EXCLUDE database object’s with Data Pump Export Utility

Most of the time we need to backup single database object instead of full schema & database, in this scenario we can use data pump parameter INCLUDE/EXCLUDE to export or import objects as per our convenience.
These parameters are used to limit the export/import to specific objects.

INCLUDE: Data Pump parameter to be specify objects to be included while export/import.
EXCLUDE: Data Pump parameter to be specify objects to be exclude while export/import. Continue reading

Oracle 11g Logo

Wildcard Search in Oracle Data Pump

Consider following case for wildcard search in Oracle Datapump export.

If your database contains thousands of tables and you wish to backup only selected tables then probably you will use TABLES datapump parameter by supplying multiple schema.table name in comma separated format. This will be feasible if tables count within 10 or 20.

In this case you can use wildcard search ( % – Percent ) in TABLES datapump parameter. Continue reading

Oracle 11g Logo

Export oracle tablespace with Data Pump

We can also export tablespace[s] with the help of TABLESPACES parameter in data pump utility.

In this, we can backup only the tables contains in specified set of tablespaces. Table will be backup with the dependent objects.
Note: In this mode both object metadata and data is backup.

Syntax:
TABLESPACES = tablespace_1, tablespace_2, [,..] Continue reading

Oracle 11g Logo

Export Import oracle schema with data pump

We can export single as well as multiple schema’s/users with the help of SCHEMAS data pump parameter.

Consider following hands-on on exporting schema/user with the help of data pump utility:

Note:
If EXP_FULL_DATABASE role not granted to user who performing export operation, then user can only perform export his schema not others. (default)

 

Here we are exporting scott schema ( Single schema ) from orcl database:

expdp system/manager schemas=scott directory=data_pump_bkup dumpfile=scott_export.dmp logfile=scott_export.log

. . exported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . exported “SCOTT”.”BONUS” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/data_pump_bkup/scott_export.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 11:58:35
Importing above exported schema(scott):
impdp system/manager schemas=scott directory=data_pump_bkup dumpfile=scott_export.dmp logfile=scott_import.log
–x–

 

we can also export multiple schema’s with the help of same SCHEMAS data pump parameter.

In following case, we are exporting scott & test schema’s from orcl database;

expdp system/manager schemas=scott,test directory=data_pump_bkup dumpfile=scott_test_export.dmp logfile=scott_test_export.log

. . exported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . exported “SCOTT”.”BONUS” 0 KB 0 rows
. . exported “TEST”.”DEPT” 0 KB 0 rows
. . exported “TEST”.”EMP” 0 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/data_pump_bkup/scott_test_export.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 12:07:36
Importing above exported schemas(scott & test):
impdp system/manager schemas=scott,test directory=data_pump_bkup dumpfile=scott_test_export.dmp logfile=scott_test_import.log

. . imported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . imported “SCOTT”.”EMP” 8.570 KB 14 rows
. . imported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . imported “SCOTT”.”BONUS” 0 KB 0 rows
. . imported “TEST”.”DEPT” 0 KB 0 rows
. . imported “TEST”.”EMP” 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at 12:09:06
–x–

Likewise we can Export-Import multiple schema’s with the help of data pump utility.

************************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

Export Import oracle tables with Data Pump utility

We can export single as well as multiple tables with the help of TABLES data pump parameter.

Consider following hands-on on exporting tables with the help of data pump utility.

//Consider example on single table export, in this we are exporting table emp from scott schema. Continue reading

Oracle 11g Logo

Oracle full database backup with Data Pump Export Utility

Introduction to Oracle Data Pump Utility:
This utility is used to backup database into operating system files, called as dump file. This file is only imported or accessible by import data pump utility.

Roles required to perform Data Pump Export-Import are EXP_FULL_DATABASE & IMP_FULL_DATABASE, Without these role we can’t do backup & restore database instead of schema level Export-Import. Continue reading