Tag Archives: estimate space utilization

Script for Segment Advisory for partition tables in Oracle

Script for Segment Advisory for partition tables in Oracle

Script is used for reclaim activity which object has free space. Need to reclaim space to Operating system.

Copy paste the following script in command shell windows for segment advisory or reclaim the space from the object like table or index.
It asked for Object_type, Owner name and object name which you need to check for space reclaim activity.
Following script is consider for Partition table, it will provide you partition name with table name for help in reclaim acitivities.


--Segment Advisory Script:

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF

DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK2';
l_object_type VARCHAR2(32767) := UPPER('&Object_type');
l_attr1 VARCHAR2(32767) := UPPER('&Owner_name');
l_attr2 VARCHAR2(32767) := UPPER('&object_name');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;

DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);

DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => l_task_name);
FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
o.attr3,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Attr3 : ' || cur_rec.attr3);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;

DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/

Advertisements

Datapump expdp impdp backup in Oracle

Datapump expdp impdp backup in Oracle

Datapump is introduced in Oracle 10g. It is also used to take logical backup of the database.
Working of Datapump: Every datapump job created a table in schema who is running job. This is called Master table.
Master table is keeping track of job and help to make datapump job resumable. When job finished table is dropped.
Note: If table is already present in database with same name then job is not started.

Datapump is Server based job, it created output on Server pointing to the Directory object of Oracle.
For Start any job first we need to create the Directory for the location.

Steps for Datapump started

1. Create the Directory

Create directory dbpump as 'E:\dbbackup';

2. Grant the user to have read and write permission on directory

Grant read,write on directory dbpump to Scott,HR;

3. Make directory at Operating System level:

mkdir E:\dbbackup

4. Example of EXPDP Backup as follows:

EXPDP system/manager directory=dbpump dumpfile=full.dmp logfile=full.log parallel=4 job_name=full_system

Note: A table is created as name JOB_NAME paraemter value full_system for EXPDP job. Its dropped automatic when job completed.

5. Stop or start expdp job

EXPDP system/manager directory=dbpump dumpfile=full.dmp logfile=full.log parallel=4 job_name=full_system
-- Ctrl+c for export prompt
Ctrl+C
--Stop the current job
export> stop_job
-- come out from export prompt
export> Exit
-- Start the job
export>start_job

6. If you exit from the export prompt then need to attach the job again

expdp system/manager ATTACH=full_system
export> START_JOB

7. Perform Schema export

Option 1: Run with that schema user like HR
expdp HR/HR directory=dbpump dumpfile=hr.dmp logfile=hr.log parallel=4 job_name=hr_dump

Option 2: Run with DBA user
expdp sys directory=dbpump dumpfile=hr.dmp logfile=hr.log parallel=4 job_name=hr_dump schemas=HR

8. Import the one schema into another schema remap it

impdp system/manager directory=dbpump dumpfile=hr.dmp logfile=imphr.log job_name=HR_TO_SCOTT remap_schema=HR:SCOTT

9. Export a table

expdp HR/HR directory=dbpump dumpfile=table.dmp logfile=table.log parallel=4 job_name=table_job tables=hr.dept

10. Estimate the space utilization without executing the EXPDP job

expdp system/manager directory=dbpump extimate_only=Y logfile=full.log

11. Monitoring query for datapump job

select owner_name, job_name ,operation, job_mode ,state , degree ,attached_sessions from dba_datapump_jobs;

select a.owner,a.job_name,b.osuser from dba_datapump_sessions a, v$session b where b.saddr = a.saddr;