Category: Uncategorized

Expdp/impdp estimate the dump size without real export.

 
expdp \'/ as sysdba\' directory=DATAPUMP_DIR full=y ESTIMATE_ONLY=y 

Expdp/impdp directory, SCN, timestamp and consistent

As you know expdp is a logical backup not the physical one. It works in same version also you can expdp/impdp from lower to higher version. Oracle is very restricted expdp/impdp from higher to lower version.

There are few important things you should keep in mind.

1. Create directory, if already not, and ensure relevant user got grant to read, write the directory. This directory will be used to store the dump and log file.

 
CREATE OR REPLACE DIRECTORY DIR_NAME AS ‘/ora01/db/oracle/’;
GRANT READ, WRITE,EXECUTE ON DIRECTORY DIR_NAME TO scott; 

2. Whenever you need to export online database/schema ensure you will use CONSISTENT=Y (in 11.2). If need you can use SCN or time using option FLASHBACK_TIME.

Check SCN/TIMESTAMP:

OR use below syntax. It will ensure your expdp will be consistent.
Note: It has been used many time successfully.

 
expdp \"/ as sysdba\" FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\" 

Warning !!! BE CAREFUL! – THIS DROPS EVERYTHING UNDER THE LOGON/OWNER THAT RUNS IT

 Akismet Anti-Spam
 Activate | Delete
 Used by millions, Akismet is quite possibly the best way in the world to protect your blog from spam. It keeps your site protected even while you sleep. To get started: activate the Akismet plugin and then go to your Akismet Settings page to set up your API key.
 Version 4.1.1 | By Automattic | View details
 Select All In One SEO Pack    
 All In One SEO Pack
 Activate | Delete
 Out-of-the-box SEO for your WordPress blog. Features like XML Sitemaps, SEO for custom post types, SEO for blogs or business sites, SEO for ecommerce sites, and much more. More than 50 million downloads since 2007.
 Version 2.12 | By Michael Torbert | View details
 Select All-in-One WP Migration    
 All-in-One WP Migration
 Activate | Delete
 Migration tool for all your blog data. Import or Export your blog content with a single click.
 Version 6.91 | By ServMask | View details
 Select BackUp WD    
 BackUp WD
 Activate | Delete
 Backup WD is an easy-to-use, fully functional backup plugin that allows to backup your website.
 SET SERVEROUTPUT ON SIZE 1000000
 BEGIN
 FOR cur_rec IN (SELECT object_name, object_type
 FROM user_objects
 WHERE object_type IN ('TABLE','SYNONYM','VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE', 'TYPE')) LOOP
 BEGIN
 IF cur_rec.object_type = 'TABLE' THEN
 EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
 ELSE
 EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
 END IF;
 EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
 END;
 END LOOP;
 END;
 /

 
PURGE RECYCLEBIN;
 SET ERRORLOGGING OFF 

$ORACLE_HOME/rdbms/admin

Given tablespace used, auto and max in Mb

 
set lines 200
set pagesize 150
break on report
compute sum of mbytes on report
col file_name format a70
select
file_id,
file_name,
bytes/1024/1024 CURRENT_USED_MB,
autoextensible,
maxbytes/1024/1024 CAN_GROW_TO_MAX_MB
from
dba_data_files
where
tablespace_name=upper('&Tbs_Name'); 

Sample output:

 
 SET SERVEROUTPUT ON
declare
total_size_b number;
tfree_size_b number;
tused_size_b number;
begin
dbms_output.enable(100000);
select
Sum(bytes) into total_size_b
from
dba_data_files;
select
Sum(bytes) into tfree_size_b
from
dba_free_space;
select
Sum(bytes) into tused_size_b
from
dba_segments;
dbms_output.put_line('Total:' || TO_CHAR(Round(total_size_b/1048576, 2), '999,999.00') || ' MB');
dbms_output.put_line('Free: ' || TO_CHAR(Round(tfree_size_b/1048576, 2), '999,999.00') || ' MB');
dbms_output.put_line('Used: ' || TO_CHAR(Round(tused_size_b/1048576, 2), '999,999.00') || ' MB');
end;
/
SET SERVEROUTPUT OFF  

Sample output: