Updated 3/29/13
OK, hope the examples of the PL/SQL helped, but I think it’s time to make this datapump exploration into something useful. How about coding a set of imports, exports, SQL and putting it all in a table to be executed by PL/SQL and running it all with one procedure?
This is a long post so you’ll have to drill deep to find the crude oil here.
Let’s make up a fake company and scenario to be used here. Our company Nodding Donkey Oil has engineers using laptops to record information at various sites. These sites are so remote that they have no connectivity and many times have to do complex queries on a subset of the centralized data in the field. Let’s say we have about 500 of these laptop carrying engineers all using Oracle XE. When they do get back to civilization, they connect to their centralized database via datapump and dump their data into a “cache” database where the data then gets pushed to their central database store. The laptop then goes into the back of the truck until they are called up again. When they do get another call they will rebuild this laptop data for a particular job order at a particular Nodding Donkey pump site.
By using Oracle XE, we’ve cut out database cost, because it’s free with some limitations. It’s the same database we use the most so we have the knowledge base. It has a max memory limit of 1G, and a max storage capacity of 11G for the data. It doesn’t run the datapump in parallel, but it’s still fast enough for 11G of data. It doesn’t have some of the specialized database functionality like the Enterprise Edition, but for free it works fine for our purpose.
Our datapump engine will be initiated with a particular job number and the data sync process will occur and be managed all from a set of tables and packages we’ll reveal below.
Let’s design the table structure: A job table that keeps our sets of master jobs we may want to execute, a task table that has each and every task and the order of the execution. Parameter tables for FULL, SCHEMA, and SQL that are really just further distinctions of each task that may be executed. Thinking of these as parameter tables makes the most sense. Each record in FULL, SCHEMA, and SQL tables have a one to one relationship with one of the TASKS. This could be changed to one Job table and one Task table with all the values together, but this choice makes it easier to understand and easy to modify later. It also makes data entry a bit of a pain since you have to carry the task id down to the SCHEMA, FULL and SQL tables. A data entry app will can make this easier, or you could use the sequence generator only at the FULL, SCHEMA and SQL tables and add the TASK entry as a trigger, making changes to it later. All these options work, may want to pick the best one for you. You could add the two records (TASK and SCHEMA for example) at the same time and could even do this with a trigger and default TASK values from the Lower level table. Or create a app that does this for you.
DP_LOGGING and REMAP_TABLESPACE are fairly self explanatory. A logging table and a table to remap the tablespaces. For the quick solution you can set a default tablespace in the code to catch all that is not in the remap tablespace table. So for large database on non-XE cases, you might fill in the REMAP_TABLESPACE table, but for XE the default works fine. The remaining table, IMP_USER_ROLE_CR_PREF is used to handle the case where you import via remote link and the local users, roles, etc are not set up. If you don’t have a user it can be added and you move on; however if you have a database link you can do a bit more. Used with this table there is a utility pkg_dp_util.fn_create_permissions_script that will help in creating permissions across databases. The script is created locally and is to be run on the remote database via SQL*Plus and it’s resulting output will populate the values locally. BE SURE TO LOOK THROUGH THIS before you run it. Also if you don’t want users added to your target database on import, be sure to populate this table (IMP_USER_ROLE_CR_PREF) and set the preference to N)o ahead of the import, or comment out that part of the code.
Let’s talk about some examples all the code is at the end of this blog. Note the packages and tables here may be slightly different than the other blogs on https://oraclesage.wordpress.com on this subject. All this codes works as a system and if you are following along with the examples, it’s left up to you to populate the tables the way you want. There has been a lot of cutting and pasting here, so if something doesn’t work for you or you don’t understand the goal let me know. There are many ways of solving this problem and I’ve done this exercise mostly for my education and future reference. Some things are a little inconsistent and I may improve on this if I can find the time.
I’ve left off some columns, like the “–stamp” columns, and effectivities the table entries. Note I didn’t add any filter for the effectivities for the code below, but it could be easily added.
I’m assuming at this point you have the code below installed and all tuned up.
Let’s build a job adding steps as we go, we’ll start with Job ID 1:
INSERT INTO oradba.dp_job (DP_JOB_ID,JOB_NAME,JOB_DESCRIPTION)
VALUES(1,'Nodding Donkey ','Export, import new schema, import table, import list of tables, execute sql');
In datapump we can start a job and let it complete in the background, and start another. Or we can wait until it completes. So if we build dependent tasks we need to have their predecessors be monitored to completion before running the next step. But if we can run these independently, we get a big time savings by running these in the background as we start another. This assumes we have a little extra performance we can squeeze out of our platform. If you’re on an overloaded server this might not help.
So we have a basic job that runs a full export, and that is just a safety net, so let’s set Monitor_to_completion to Y. We want our initial full export to complete before we continue.
Let’s add a user drop, user add, SCHEMA import, Table import, then import a set of tables defined by the table we just brought over. Add some permissions with SQL, and another full export to backup what we just did. And now we have a table driven set of data imported with datapump. We can run this again on another machine by just copying the schema ORADBA listed below to a new machine.
Here’s an example of the table entries, you have the Job entry above.
Task Entries – what we are doing and in what order:
-- Full export - and wait till it is complete
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(2,1,'Full Local Export','Initial Export','FULL','EXPORT','Y',10);
-- drop a user (sql)
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(89,1,'Drop User INV','INV','SQL','SQL_EXEC','Y',15);
-- drop another user
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(79,1,'Drop User DWGS','DWGS','SQL','SQL_EXEC','Y',20);
-- Add a user
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(80,1,'Add User DWGS',NULL,'SQL','SQL_EXEC','Y',30);
-- Import a schema
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(66,1,'Imp Inv Schema',NULL,'SCHEMA','IMPORT','Y',40);
-- drop a table
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(84,1,'Drop table Old',NULL,'SQL','SQL_EXEC','Y',43);
-- Import a table
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(67,1,'Imp List of Tables Table',NULL,'TABLE','IMPORT','Y',45);
-- import a set of tables from the list in the preceding table
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(74,1,'Import List of tables','Read the table and import the list','SCHEMA','IMPORT','N',50);
-- grant a role - not waiting for completion
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(81,1,'Grant Resource',NULL,'SQL','SQL_EXEC','N',60);
-- perform a final export, but don't wait for it to complete - job is done once submitted
INSERT INTO oradba.dp_task (DP_TASK_ID,DP_JOB_ID,TASK_NAME,TASK_DESCRIPTION,TASK_TYPE,TASK_OPERATION,MONITOR_TO_COMPLETION,EXECUTION_ORDER)
VALUES(83,1,'Full Export Local','Final Export','FULL','EXPORT','N',70);
Full Records, not much here, but it can be expanded to include other parameters – full exports and imports
INSERT INTO oradba.dp_full (DP_TASK_ID,REMOTE_LINK)
VALUES(83,NULL);
INSERT INTO oradba.dp_full (DP_TASK_ID,REMOTE_LINK)
VALUES(2,NULL);
Schema Records, no tablespace changes so those columns left off as well:
-- full schema INV from db link CO
INSERT INTO oradba.dp_schema (DP_TASK_ID,SCHEMA_NAME,REMOTE_LINK,DEGREE,TABLE_NAME,TABLE_SUBQUERY,EXPR_LIST)
VALUES(66,'INV','CO',1,NULL,NULL,NULL);
-- table from user DWGS named master table
INSERT INTO oradba.dp_schema (DP_TASK_ID,SCHEMA_NAME,REMOTE_LINK,DEGREE,TABLE_NAME,TABLE_SUBQUERY,EXPR_LIST)
VALUES(67,'DWGS','CO',1,'TABLE_MASTER',NULL,NULL);
-- all the tables listed in the table dwgs.master_table
INSERT INTO oradba.dp_schema (DP_TASK_ID,SCHEMA_NAME,REMOTE_LINK,DEGREE,TABLE_NAME,TABLE_SUBQUERY,EXPR_LIST)
VALUES(74,'DWGS','CO',1,NULL,NULL,'in (SELECT a.table_name FROM DWGS.TABLE_MASTER a where job_name like ''SITE_A431'' )');
SQL Records:
-- standard sql with no ending ";" or "/"
INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN)
VALUES(80,'CREATE USER DWGS
IDENTIFIED BY DWGS
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP','N');
INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN)
VALUES(79,'drop user DWGS cascade','N');
INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN)
VALUES(89,'drop user inv cascade','Y');
INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN)
VALUES(84,'drop table DWGS.TABLE_MASTER','N');
INSERT INTO oradba.dp_sql (DP_TASK_ID,SQL_STMT,IGNORE_ERROR_YN)
VALUES(81,'grant resource to DWGS','N');
Note the column above MONITOR_TO_COMPLETION = ‘N’. If we change this column to ‘Y’, the program will execute to completion and not return control back the executing source until the full export is complete.
Now if we run the job in an anonymous block, and then check the dump directory specified, we see it is still running. You can also monitor the activity from the dp_logging table.
...
oradba.pkg_dp_main.p_start_job
(p_job_id=>1,
p_completion_code=>p_completion_code,
p_complete_status=>p_complete_status);
...
So now you have a project about 75% complete and if anyone is interested in this project, I’ll do some more work on this if not, I’ll move on. At least this can serve as an example of a data driven data pump process that anyone can code.
Some issues with this code:
This is more of a Proof of concept, needs more validation and error checking along the way, could combine some of these procedures to simplify the code, some inconsistent coding – would like to see more consistent parameters throughout, lots of code not added, and more. Having listed some of the issues, it does work and could be extended to make a nicer application that could be very useful. Keeping the logic in the tables makes it easy to maintain once the application is complete. Changing the way data is moved would be easy to maintain and very flexible. I’m sure someone else has already done this. Wouldn’t be a long stretch to use this for sub-setting development databases from production and automating the load.
I encourage you to leave comments if this is interesting, and I’ll add to it if there is any interest.
As sys grant execute on utl_file and utl_tcp to oradba.
Here is the rest of the code:
Sequences :
CREATE SEQUENCE oradba.seq_dp_full
INCREMENT BY 1
START WITH 1000
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCYCLE
ORDER
NOCACHE
/
CREATE SEQUENCE oradba.seq_dp_job
INCREMENT BY 1
START WITH 1000
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCYCLE
ORDER
NOCACHE
/
CREATE SEQUENCE oradba.seq_dp_logging
INCREMENT BY 1
START WITH 1000
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCYCLE
NOORDER
NOCACHE
/
CREATE SEQUENCE oradba.seq_dp_schema
INCREMENT BY 1
START WITH 1000
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCYCLE
ORDER
NOCACHE
/
CREATE SEQUENCE oradba.seq_dp_task
INCREMENT BY 1
START WITH 1000
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCYCLE
ORDER
NOCACHE
/
CREATE SEQUENCE oradba.seq_imp_user_role_cr_pref
INCREMENT BY 1
START WITH 1000
MINVALUE 1
MAXVALUE 9999999999999999999999999999
NOCYCLE
ORDER
NOCACHE
/
Tables:
CREATE TABLE oradba.dp_full
(
dp_task_id NUMBER(15, 0) NOT NULL,
remote_link VARCHAR2(30 BYTE),
insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
insert_user VARCHAR2(30 BYTE) NOT NULL,
update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
update_user VARCHAR2(30 BYTE) NOT NULL,
CONSTRAINT dp_full_pk PRIMARY KEY(dp_task_id) USING INDEX
)
SEGMENT CREATION IMMEDIATE
NOPARALLEL
LOGGING
MONITORING
/
CREATE OR REPLACE TRIGGER oradba.dp_full_bu
BEFORE UPDATE
ON oradba.dp_full
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
CREATE OR REPLACE TRIGGER oradba.dp_full_bi
BEFORE INSERT
ON oradba.dp_full
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.insert_ts := SYSDATE;
:new.insert_user := USER;
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
COMMENT ON TABLE oradba.dp_full IS 'Defines full export
3/12/13 dcox'
/
COMMENT ON COLUMN oradba.dp_full.dp_task_id IS 'FK to Task Table - Unique numerial id for this table as well'
/
COMMENT ON COLUMN oradba.dp_full.insert_ts IS 'Insert timestamp'
/
COMMENT ON COLUMN oradba.dp_full.insert_user IS 'Insert userstamp'
/
COMMENT ON COLUMN oradba.dp_full.remote_link IS 'Remote Link when pulling full export/import'
/
COMMENT ON COLUMN oradba.dp_full.update_ts IS 'Update Timestamp'
/
COMMENT ON COLUMN oradba.dp_full.update_user IS 'Update Userstamp'
/
CREATE TABLE oradba.dp_job
(
dp_job_id NUMBER(15, 0) NOT NULL,
job_name VARCHAR2(20 BYTE) NOT NULL,
job_description VARCHAR2(300 BYTE),
eff_date_start TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
eff_date_stop TIMESTAMP(6) WITH LOCAL TIME ZONE,
insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
insert_user VARCHAR2(30 BYTE) NOT NULL,
update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
update_user VARCHAR2(30 BYTE) NOT NULL,
CONSTRAINT dp_job_pk PRIMARY KEY(dp_job_id) USING INDEX
)
SEGMENT CREATION IMMEDIATE
NOPARALLEL
LOGGING
MONITORING
/
ALTER TABLE oradba.dp_job
ADD CONSTRAINT dp_job_uk1 UNIQUE (job_name)
USING INDEX
/
ALTER TABLE oradba.dp_job
ADD CONSTRAINT db_job_ck1 CHECK (eff_date_start < NVL(eff_date_stop,eff_date_start+1)
)
DISABLE NOVALIDATE
/
CREATE OR REPLACE TRIGGER oradba.dp_job_bu
BEFORE UPDATE
ON oradba.dp_job
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
CREATE OR REPLACE TRIGGER oradba.dp_job_bi
BEFORE INSERT
ON oradba.dp_job
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
SELECT seq_dp_job.NEXTVAL INTO :new.dp_job_id FROM DUAL;
:new.insert_ts := SYSDATE;
:new.insert_user := USER;
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
COMMENT ON TABLE oradba.dp_job IS 'This is the datapump (DP) master job list (one job for many tasks).
3/12/13 dcox'
/
COMMENT ON COLUMN oradba.dp_job.dp_job_id IS 'Job ID'
/
COMMENT ON COLUMN oradba.dp_job.eff_date_start IS 'Effective Start Date'
/
COMMENT ON COLUMN oradba.dp_job.eff_date_stop IS 'Effective Stop Date'
/
COMMENT ON COLUMN oradba.dp_job.insert_ts IS 'Insert Timestamp'
/
COMMENT ON COLUMN oradba.dp_job.insert_user IS 'Insert Userstamp'
/
COMMENT ON COLUMN oradba.dp_job.job_description IS 'Job Description - detailed description'
/
COMMENT ON COLUMN oradba.dp_job.job_name IS 'Job Name - unique name for this job'
/
COMMENT ON COLUMN oradba.dp_job.update_ts IS 'Update Timestamp'
/
COMMENT ON COLUMN oradba.dp_job.update_user IS 'Update Userstamp'
/
CREATE TABLE oradba.dp_logging
(
dp_logging_id NUMBER(15, 0) NOT NULL,
code_returned NUMBER(30, 0) NOT NULL,
MESSAGE_TEXT VARCHAR2(4000 BYTE) NOT NULL,
line_no NUMBER(*, 0) NOT NULL,
object_schema VARCHAR2(30 BYTE) NOT NULL,
object_name VARCHAR2(65 BYTE) NOT NULL,
job_id NUMBER(15, 0),
task_id NUMBER(15, 0),
insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
insert_user VARCHAR2(30 BYTE) NOT NULL,
update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
update_user VARCHAR2(30 BYTE) NOT NULL,
CONSTRAINT db_logging_pk PRIMARY KEY(dp_logging_id) USING INDEX
)
SEGMENT CREATION IMMEDIATE
NOPARALLEL
LOGGING
MONITORING
/
CREATE OR REPLACE TRIGGER oradba.dp_logging_bi
BEFORE INSERT
ON oradba.dp_logging
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
SELECT oradba.seq_dp_logging.NEXTVAL INTO :new.dp_logging_id FROM DUAL;
:new.insert_ts := SYSDATE;
:new.insert_user := USER;
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
CREATE OR REPLACE TRIGGER oradba.dp_logging_bu
BEFORE UPDATE
ON oradba.dp_logging
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
COMMENT ON TABLE oradba.dp_logging IS 'Logging table mainly for errors and success, but could also contain informational logging.
3/12/13 dcox'
/
COMMENT ON COLUMN oradba.dp_logging.code_returned IS '0 is success, 1 is informational, all others are errors'
/
COMMENT ON COLUMN oradba.dp_logging.dp_logging_id IS 'Unique Numerical Identifier'
/
COMMENT ON COLUMN oradba.dp_logging.insert_ts IS 'Insert Timestamp'
/
COMMENT ON COLUMN oradba.dp_logging.insert_user IS 'Insert Userstamp'
/
COMMENT ON COLUMN oradba.dp_logging.job_id IS 'Job ID'
/
COMMENT ON COLUMN oradba.dp_logging.line_no IS 'Line number where error or issue occurred'
/
COMMENT ON COLUMN oradba.dp_logging.MESSAGE_TEXT IS 'Message Text'
/
COMMENT ON COLUMN oradba.dp_logging.object_name IS 'Object Name where message was logged. Could be package.procedure or package.function.'
/
COMMENT ON COLUMN oradba.dp_logging.object_schema IS 'Object_schema where message was logged'
/
COMMENT ON COLUMN oradba.dp_logging.task_id IS 'Task ID'
/
COMMENT ON COLUMN oradba.dp_logging.update_ts IS 'Update Timestamp'
/
COMMENT ON COLUMN oradba.dp_logging.update_user IS 'Update Userstamp'
/
CREATE TABLE oradba.dp_schema
(
dp_task_id NUMBER(15, 0) NOT NULL,
schema_name VARCHAR2(30 BYTE) NOT NULL,
insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
insert_user VARCHAR2(30 BYTE) NOT NULL,
update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
update_user VARCHAR2(30 BYTE) NOT NULL,
remote_link VARCHAR2(30 BYTE),
degree NUMBER(*, 0) NOT NULL,
tablespace_from VARCHAR2(30 BYTE),
tablespace_to VARCHAR2(30 BYTE),
table_name VARCHAR2(30 BYTE),
table_subquery VARCHAR2(4000 BYTE),
expr_list VARCHAR2(4000 BYTE),
dumpfile_name VARCHAR2(1000 BYTE),
CONSTRAINT dp_schema_pk PRIMARY KEY(dp_task_id, schema_name) USING INDEX
)
SEGMENT CREATION IMMEDIATE
NOPARALLEL
LOGGING
MONITORING
/
CREATE OR REPLACE TRIGGER oradba.dp_schema_bu
BEFORE UPDATE
ON oradba.dp_schema
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
CREATE OR REPLACE TRIGGER oradba.dp_schema_bi
BEFORE INSERT
ON oradba.dp_schema
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.insert_ts := SYSDATE;
:new.insert_user := USER;
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
COMMENT ON TABLE oradba.dp_schema IS 'Task definition for Schema to be moved
3/12/13 dcox'
/
COMMENT ON COLUMN oradba.dp_schema.degree IS 'Degree of Parallism'
/
COMMENT ON COLUMN oradba.dp_schema.dp_task_id IS 'fk to Task ID, also uk for this table'
/
COMMENT ON COLUMN oradba.dp_schema.dumpfile_name IS 'Dumpfile_name for local import files'
/
COMMENT ON COLUMN oradba.dp_schema.expr_list IS 'Expression - could be a query to other table lists - see blog for example'
/
COMMENT ON COLUMN oradba.dp_schema.insert_ts IS 'Insert Timestamp'
/
COMMENT ON COLUMN oradba.dp_schema.insert_user IS 'Insert Userstamp'
/
COMMENT ON COLUMN oradba.dp_schema.remote_link IS 'Remote Database Link'
/
COMMENT ON COLUMN oradba.dp_schema.schema_name IS 'Schema to be imp/exp target'
/
COMMENT ON COLUMN oradba.dp_schema.table_name IS 'Table name - blank when it is for a schema'
/
COMMENT ON COLUMN oradba.dp_schema.table_subquery IS 'Table subquery (where clause)'
/
COMMENT ON COLUMN oradba.dp_schema.tablespace_from IS 'Import only - Tablespace from'
/
COMMENT ON COLUMN oradba.dp_schema.tablespace_to IS 'Import only - Tablespace to'
/
COMMENT ON COLUMN oradba.dp_schema.update_ts IS 'Update Timestamp'
/
COMMENT ON COLUMN oradba.dp_schema.update_user IS 'Update Userstamp'
/
CREATE TABLE oradba.dp_sql
(
dp_task_id NUMBER(15, 0) NOT NULL,
sql_stmt VARCHAR2(4000 BYTE),
insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
insert_user VARCHAR2(30 BYTE) NOT NULL,
update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
update_user VARCHAR2(30 BYTE) NOT NULL,
ignore_error_yn VARCHAR2(1 BYTE) NOT NULL,
CONSTRAINT dp_sql_pk PRIMARY KEY(dp_task_id) USING INDEX
)
SEGMENT CREATION IMMEDIATE
NOPARALLEL
LOGGING
MONITORING
/
CREATE OR REPLACE TRIGGER oradba.dp_sql_bu
BEFORE UPDATE
ON oradba.dp_sql
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
CREATE OR REPLACE TRIGGER oradba.dp_sql_bi
BEFORE INSERT
ON oradba.dp_sql
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.insert_ts := SYSDATE;
:new.insert_user := USER;
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
COMMENT ON TABLE oradba.dp_sql IS 'Defines sql statements without return values
3/12/13 dcox'
/
COMMENT ON COLUMN oradba.dp_sql.dp_task_id IS 'Unique numerical Identifier'
/
COMMENT ON COLUMN oradba.dp_sql.ignore_error_yn IS 'Ignore any execution errors for this sql'
/
COMMENT ON COLUMN oradba.dp_sql.insert_ts IS 'Insert timestamp'
/
COMMENT ON COLUMN oradba.dp_sql.insert_user IS 'Insert userstamp'
/
COMMENT ON COLUMN oradba.dp_sql.sql_stmt IS 'SQL Statement'
/
COMMENT ON COLUMN oradba.dp_sql.update_ts IS 'Update Timestamp'
/
COMMENT ON COLUMN oradba.dp_sql.update_user IS 'Update Userstamp'
/
CREATE TABLE oradba.dp_task
(
dp_task_id NUMBER(15, 0) NOT NULL,
dp_job_id NUMBER(15, 0) NOT NULL,
task_name VARCHAR2(30 BYTE) NOT NULL,
task_description VARCHAR2(300 BYTE),
task_type VARCHAR2(30 BYTE) NOT NULL,
eff_date_start TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
eff_date_stop TIMESTAMP(6) WITH LOCAL TIME ZONE,
task_operation VARCHAR2(30 BYTE) NOT NULL,
insert_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
insert_user VARCHAR2(30 BYTE) NOT NULL,
update_ts TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
update_user VARCHAR2(30 BYTE) NOT NULL,
monitor_to_completion VARCHAR2(1 BYTE) NOT NULL,
execution_order NUMBER(*, 0) NOT NULL,
CONSTRAINT dp_task_pk PRIMARY KEY(dp_task_id) USING INDEX
)
SEGMENT CREATION IMMEDIATE
NOPARALLEL
LOGGING
MONITORING
/
ALTER TABLE oradba.dp_task
ADD CONSTRAINT dp_task_uk1 UNIQUE (dp_job_id, task_name)
USING INDEX
/
ALTER TABLE oradba.dp_task
ADD CONSTRAINT dp_task_ck3 CHECK (task_type IN ('FULL','SCHEMA','TABLE','LIST','QUERY','SQL')
)
/
ALTER TABLE oradba.dp_task
ADD CONSTRAINT dp_task_ck2 CHECK (task_operation IN ('IMPORT','EXPORT','SQL_FILE','SQL_EXEC')
)
/
ALTER TABLE oradba.dp_task
ADD CONSTRAINT dp_task_ck1 CHECK (eff_date_start < NVL(eff_date_stop,eff_date_start+1)
)
/
CREATE OR REPLACE TRIGGER oradba.dp_task_bu
BEFORE UPDATE
ON oradba.dp_task
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
CREATE OR REPLACE TRIGGER oradba.dp_task_bi
BEFORE INSERT
ON oradba.dp_task
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
IF :new.dp_task_id IS NULL
THEN
SELECT seq_dp_task.NEXTVAL INTO :new.dp_task_id FROM DUAL;
END IF;
:new.insert_ts := SYSDATE;
:new.insert_user := USER;
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
COMMENT ON TABLE oradba.dp_task IS 'Lists of tasks and the order in which they should be executed.
3/12/13 dcox'
/
COMMENT ON COLUMN oradba.dp_task.dp_job_id IS 'FK to Job table'
/
COMMENT ON COLUMN oradba.dp_task.dp_task_id IS 'Unique numerical identifier'
/
COMMENT ON COLUMN oradba.dp_task.eff_date_start IS 'Effective Start Date'
/
COMMENT ON COLUMN oradba.dp_task.eff_date_stop IS 'Effective Stop Date'
/
COMMENT ON COLUMN oradba.dp_task.execution_order IS 'Lower numbers are first, then second by task_id'
/
COMMENT ON COLUMN oradba.dp_task.insert_ts IS 'Insert Timestamp'
/
COMMENT ON COLUMN oradba.dp_task.insert_user IS 'Insert Userstamp'
/
COMMENT ON COLUMN oradba.dp_task.monitor_to_completion IS 'Y)es or N)o to monitor to completion.'
/
COMMENT ON COLUMN oradba.dp_task.task_description IS 'Task Description - Long detailed descripton'
/
COMMENT ON COLUMN oradba.dp_task.task_name IS 'Task Name'
/
COMMENT ON COLUMN oradba.dp_task.task_operation IS 'TAsk Operation - IMPORT, EXPORT, SQL_FILE, SQL_EXEC'
/
COMMENT ON COLUMN oradba.dp_task.task_type IS 'Task Type FULL, SCHEMA, TABLE, LIST OF TABLES, SQL EXEC'
/
COMMENT ON COLUMN oradba.dp_task.update_ts IS 'Update Timestamp'
/
COMMENT ON COLUMN oradba.dp_task.update_user IS 'Update Userstamp'
/
CREATE TABLE oradba.imp_user_role_cr_pref
(
imp_user_role_cr_pref_id NUMBER(15, 0) NOT NULL,
permission_name VARCHAR2(30 BYTE) NOT NULL,
user_or_role VARCHAR2(4 BYTE) NOT NULL,
create_preference_yn VARCHAR2(1 BYTE) NOT NULL,
insert_ts TIMESTAMP(0) WITH TIME ZONE NOT NULL,
insert_user VARCHAR2(30 BYTE) NOT NULL,
update_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
update_user VARCHAR2(30 BYTE) NOT NULL
)
SEGMENT CREATION IMMEDIATE
NOPARALLEL
LOGGING
MONITORING
/
ALTER TABLE oradba.imp_user_role_cr_pref
ADD CONSTRAINT imp_user_role_or_pref_uk1 UNIQUE (permission_name)
USING INDEX
/
ALTER TABLE oradba.imp_user_role_cr_pref
ADD CONSTRAINT imp_user_role_cr_pref_ck2 CHECK (
create_preference_yn IN ('Y','N')
)
/
ALTER TABLE oradba.imp_user_role_cr_pref
ADD CONSTRAINT imp_user_role_cr_pref_ck1 CHECK (user_or_role IN ('USER','ROLE')
)
/
CREATE OR REPLACE TRIGGER oradba.imp_user_role_cr_pref_bi
BEFORE INSERT
ON oradba.imp_user_role_cr_pref
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
SELECT seq_imp_user_role_cr_pref.NEXTVAL INTO :new.imp_user_role_cr_pref_id FROM DUAL;
:new.insert_ts := SYSDATE;
:new.insert_user := USER;
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
CREATE OR REPLACE TRIGGER oradba.imp_user_role_cr_pref_bu
BEFORE UPDATE
ON oradba.imp_user_role_cr_pref
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
COMMENT ON TABLE oradba.imp_user_role_cr_pref IS
'This table determines preferences on whether a user/role has been created (logged here if added) or is desired to be created (create_preference_yn set to Y/N).
Only create when there is a Y preference here.
3/12/13 dcox
'
/
COMMENT ON COLUMN oradba.imp_user_role_cr_pref.create_preference_yn IS 'Y)es create user (default), N)o don''t create this user'
/
COMMENT ON COLUMN oradba.imp_user_role_cr_pref.imp_user_role_cr_pref_id IS 'Unique numerical identifier'
/
COMMENT ON COLUMN oradba.imp_user_role_cr_pref.insert_ts IS 'Insert Timestamp'
/
COMMENT ON COLUMN oradba.imp_user_role_cr_pref.insert_user IS 'Insert Userstamp'
/
COMMENT ON COLUMN oradba.imp_user_role_cr_pref.permission_name IS 'User or Role name'
/
COMMENT ON COLUMN oradba.imp_user_role_cr_pref.update_ts IS 'Update Timestamp'
/
COMMENT ON COLUMN oradba.imp_user_role_cr_pref.update_user IS 'Update Userstamp'
/
COMMENT ON COLUMN oradba.imp_user_role_cr_pref.user_or_role IS 'User or Role'
/
CREATE TABLE oradba.remap_tablespace
(
local_tablespace VARCHAR2(30 BYTE) NOT NULL,
remote_tablespace VARCHAR2(30 BYTE) NOT NULL,
insert_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
insert_user VARCHAR2(30 BYTE) NOT NULL,
update_ts TIMESTAMP(6) WITH TIME ZONE NOT NULL,
update_user VARCHAR2(30 BYTE) NOT NULL
)
SEGMENT CREATION IMMEDIATE
NOPARALLEL
LOGGING
MONITORING
/
ALTER TABLE oradba.remap_tablespace
ADD CONSTRAINT rempa_tablespace_uk1 UNIQUE (local_tablespace, remote_tablespace)
USING INDEX
/
CREATE OR REPLACE TRIGGER oradba.remap_tablespace_bi
BEFORE INSERT
ON oradba.remap_tablespace
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.insert_ts := SYSDATE;
:new.insert_user := USER;
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
CREATE OR REPLACE TRIGGER oradba.remap_tablespace_bu
BEFORE UPDATE
ON oradba.remap_tablespace
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
BEGIN
:new.update_ts := SYSDATE;
:new.update_user := USER;
END;
/
COMMENT ON TABLE oradba.remap_tablespace IS 'Cross Reference to remap remote tablespaces to local tablespaces.
3/12/13 dcox'
/
COMMENT ON COLUMN oradba.remap_tablespace.insert_ts IS 'Insert Timestamp'
/
COMMENT ON COLUMN oradba.remap_tablespace.insert_user IS 'Insert Userstamp'
/
COMMENT ON COLUMN oradba.remap_tablespace.local_tablespace IS 'Local_Tablespace'
/
COMMENT ON COLUMN oradba.remap_tablespace.remote_tablespace IS 'Remote Tablespace'
/
COMMENT ON COLUMN oradba.remap_tablespace.update_ts IS 'Update Timestamp'
/
COMMENT ON COLUMN oradba.remap_tablespace.update_user IS 'Update Userstamp'
/
ALTER TABLE oradba.dp_full
ADD CONSTRAINT dp_full_fk1 FOREIGN KEY (dp_task_id)
REFERENCES oradba.dp_task (dp_task_id)
/
ALTER TABLE oradba.dp_schema
ADD CONSTRAINT dp_schema_fk1 FOREIGN KEY (dp_task_id)
REFERENCES oradba.dp_task (dp_task_id)
/
ALTER TABLE oradba.dp_sql
ADD CONSTRAINT dp_sql_fk1 FOREIGN KEY (dp_task_id)
REFERENCES oradba.dp_task (dp_task_id)
/
ALTER TABLE oradba.dp_task
ADD CONSTRAINT dp_task_fk1 FOREIGN KEY (dp_job_id)
REFERENCES oradba.dp_job (dp_job_id)
/
-- End of DDL script for Foreign Key(s)
-- Added 3/18/13 dcox
ALTER TABLE ORADBA.DP_JOB
MODIFY (
EFF_DATE_START DEFAULT sysdate
)
/
ALTER TABLE ORADBA.DP_SQL
MODIFY (
IGNORE_ERROR_YN DEFAULT 'N'
)
/
ALTER TABLE ORADBA.IMP_USER_ROLE_CR_PREF
MODIFY (
CREATE_PREFERENCE_YN DEFAULT 'N'
)
/
ALTER TABLE ORADBA.DP_TASK
MODIFY (
EFF_DATE_START DEFAULT sysdate,
MONITOR_TO_COMPLETION DEFAULT 'Y',
EXECUTION_ORDER DEFAULT 1000
)
/
ALTER TABLE ORADBA.DP_SCHEMA
MODIFY (
DEGREE DEFAULT 1
)
/
PL/SQL:
CREATE OR REPLACE PACKAGE oradba.pkg_dp_exp
IS
/*
Purpose: DP Automation Export Functions and Procedures go here
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/28/2013 Initial Build
*/
PROCEDURE p_export_full(p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- JOB ID
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
);
PROCEDURE p_export_schema(p_schema_name IN VARCHAR2, -- schema name
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_remote_link IN VARCHAR2 DEFAULT NULL, -- remote database link
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
);
PROCEDURE p_export_table(p_schema_name IN VARCHAR2, -- schema name
p_table_name IN VARCHAR2, -- table name
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_remote_link IN VARCHAR2 DEFAULT NULL, -- remote database link
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_dumpfile_name IN OUT VARCHAR2, -- dumpfile name
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
);
END; -- Package spec
/
CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_exp
IS
/*
Purpose: DP Automation Export Functions and Procedures go here
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/28/2013 Initial Build
*/
-------------------------------------
PROCEDURE p_export_full(p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
)
IS
/*
Purpose: Perform a full database export - starts it - no monitoring
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/6/2013 Initial Build
dcox 3/1/2013 Adding to blog version and extending
Note: grant execute on sys.dbms_lock to <owner of this package>;
*/
v_sid VARCHAR2(200) := oradba.pkg_dp_util.fn_get_sid; -- sid for this databaes
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE;
v_current_scn v$database.current_scn%TYPE; -- current scn
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
v_degree INTEGER := 1; -- degree of parallelism
v_start_time DATE; -- start time
v_end_time DATE; -- end time
v_elapsed_days NUMBER; -- elapsed days and fraction of days
v_elapsed_hours NUMBER; -- total hours (used for intermediate calc with minutes also)
v_elapsed_minutes NUMBER; -- minus (minus hours)
v_line_no INTEGER := 0; -- debug line no
v_job_state VARCHAR2(32767); -- Job state of export
v_job_status ku$_status1010; -- See sys type ku$_Status1010
vc_sleep_time INTEGER := 10; -- seconds to sleep between testing for status
v_sqlcode NUMBER; -- sqlcode
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
v_return NUMBER; -- function return code
vc_local_fn VARCHAR2(30) := 'p_export_full'; -- local function or procedure name
BEGIN
v_line_no := 100; -- debug line no
-- create log and dump file names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.dmp';
ELSE
v_logfile_name :=
'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
v_dumpfile_name :=
'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.dmp';
END IF;
v_line_no := 150; -- debug line no
-- Open the job
BEGIN
v_line_no := 160; -- debug line no
p_job_name := 'EXP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
v_line_no := 170; -- debug line no
DBMS_OUTPUT.put_line(SUBSTR('Value of p_job_name=' || TO_CHAR(p_job_name), 1, 255));
v_handle :=
DBMS_DATAPUMP.open(operation => 'EXPORT',
job_mode => 'FULL',
job_name => p_job_name,
version => v_compatible,
compression => DBMS_DATAPUMP.ku$_compress_metadata);
p_job_handle := v_handle; -- return value for handle
-- Set the degree
DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open db_task_id:' || RTRIM(p_task_id, 10), 1, 255));
RAISE;
END;
v_line_no := 200; -- debug line no
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
-- Set the degree
DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
v_line_no := 300; -- debug line no
-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 400; -- debug line no
-- Add a datafile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => 'dp' || '_%U_' || v_dumpfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
v_line_no := 500; -- debug line no
-- consistent = Y equivalent
SELECT current_scn INTO v_current_scn FROM v$database;
DBMS_DATAPUMP.set_parameter(handle => v_handle, name => 'FLASHBACK_SCN', VALUE => v_current_scn);
v_line_no := 600; -- debug line no
-- Get the start time
v_start_time := SYSDATE;
-- Add a start time to the log file
DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
v_line_no := 700; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
DBMS_DATAPUMP.detach(handle => v_handle);
v_line_no := 800; -- debug line no
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
p_message_text => SQLERRM(v_sqlcode),
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
DBMS_DATAPUMP.detach(handle => v_handle);
-- stop job if it fails
BEGIN
DBMS_DATAPUMP.stop_job(handle => v_handle,
immediate => 0,
keep_master => NULL,
delay => 0);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
RAISE;
END p_export_full; -- procedure p_export_full
---------------------
PROCEDURE p_export_schema(p_schema_name IN VARCHAR2, -- schema name
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_remote_link IN VARCHAR2 DEFAULT NULL, -- remote database link
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
)
IS
/*
Purpose: Export schema
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/15/2013 Initial Build
dcox 3/1/2013 Modified from blog example
*/
v_sid VARCHAR2(200) := oradba.pkg_dp_util.fn_get_sid; -- sid for this databaes
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
v_start_time DATE; -- start time for log file
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
v_degree INTEGER; -- degree of parallism
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
vc_job_mode CONSTANT VARCHAR2(200) := 'SCHEMA'; -- Job mode
v_filter_name VARCHAR2(30); -- filter name
v_filter_value NUMBER; -- filter value
vc_local_fn CONSTANT VARCHAR2(30) := 'p_export_schema'; -- local function or procedure
v_return NUMBER; -- return error code
BEGIN
v_line_no := 100; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Started' || ' ' || $$plsql_unit || '.' || vc_local_fn,
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
-- Create the log and dumpfile names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.dmp';
ELSE
v_logfile_name :=
'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
v_dumpfile_name :=
'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.dmp';
END IF;
-- Open the job
BEGIN
p_job_name := 'EXP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
v_handle :=
DBMS_DATAPUMP.open(operation => 'EXPORT',
job_mode => vc_job_mode,
remote_link => p_remote_link,
job_name => p_job_name,
version => v_compatible);
p_job_handle := v_handle;
-- Set the degree
DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
RAISE;
END;
v_line_no := 200; -- debug line no
-- Set the degree
IF p_degree IS NULL OR p_degree < 0 OR p_degree > 32
THEN
v_degree := 1;
ELSE
v_degree := p_degree;
END IF;
DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 400; -- debug line no
-- Add a datafile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => 'dp' || '_%U_' || v_dumpfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
v_line_no := 500; -- debug line no
-- Filter for schema
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
v_line_no := 600; -- debug line no
-- Get the start time
v_start_time := SYSDATE;
v_line_no := 700; -- debug line no
-- Add a start time to the log file
DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
v_line_no := 710; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
DBMS_DATAPUMP.detach(handle => v_handle);
v_line_no := 800; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Completed' || ' ' || $$plsql_unit || '.' || vc_local_fn,
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
p_message_text => SQLERRM(v_sqlcode),
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RAISE;
END p_export_schema; -- Procedure p_export_schema
--------------------------------------
PROCEDURE p_export_table(p_schema_name IN VARCHAR2, -- schema name
p_table_name IN VARCHAR2, -- table name
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_remote_link IN VARCHAR2 DEFAULT NULL, -- remote database link
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_dumpfile_name IN OUT VARCHAR2, -- dumpfile name
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
)
IS
/*
Purpose: Export a table
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/4/2012 Initial Build
*/
v_sid VARCHAR2(200) := oradba.pkg_dp_util.fn_get_sid; -- sid for this databaes
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
v_start_time DATE; -- start time for log file
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
v_degree INTEGER := 1; -- degree of parallelism
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
vc_job_mode CONSTANT VARCHAR2(30) := 'TABLE'; -- Job mode
vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
v_return NUMBER; -- function return code
vc_local_fn CONSTANT VARCHAR2(30) := 'p_export_table'; -- local function or procedure name
BEGIN
v_line_no := 100; -- debug line no
-- Create the log and dumpfile names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
v_dumpfile_name := 'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.dmp';
ELSE
v_logfile_name :=
'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
v_dumpfile_name :=
'expdp_' || v_sid || '_%U_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.dmp';
END IF;
-- Populate dumpfilename with composed file_name - unless name is provided then set in variable
IF p_dumpfile_name IS NULL
THEN
p_dumpfile_name := v_dumpfile_name;
ELSE
v_dumpfile_name := p_dumpfile_name;
END IF;
v_line_no := 200; -- debug line no
-- Open the job
BEGIN
p_job_name := 'EXP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
v_handle :=
DBMS_DATAPUMP.open(operation => 'EXPORT',
job_mode => vc_job_mode,
remote_link => p_remote_link,
job_name => p_job_name,
version => v_compatible);
p_job_handle := v_handle;
-- Set the degree
DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
RAISE;
END;
v_line_no := 300; -- debug line no
-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 400; -- debug line no
-- Add a datafile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => p_dumpfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
v_line_no := 500; -- debug line no
-- Filter for schema
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
v_line_no := 600; -- debug line no
-- Filter for table
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');
v_line_no := 710; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
v_line_no := 720; -- debug line no
DBMS_DATAPUMP.detach(handle => v_handle);
v_line_no := 800; -- debug line no
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
-- Log error
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
p_message_text => SQLERRM(v_sqlcode),
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RAISE;
END p_export_table; -- Procedure p_export_table
END pkg_dp_exp; -- package pkg_dp_exp
/
CREATE OR REPLACE PACKAGE oradba.pkg_dp_imp
IS
/*
Purpose: Import procedures and functions
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/4/2013 Initial Build
*/
PROCEDURE p_imp_schema(p_task_id IN NUMBER, -- task id to be processed
p_schema_name IN VARCHAR2, -- schema to load
p_dumpfile_name IN VARCHAR2, -- dumpfile_name to load
p_job_name IN OUT VARCHAR2, -- job name for this import
p_job_handle IN OUT NUMBER -- Job handle for job created
);
PROCEDURE p_imp_schema_vialink(p_task_id IN NUMBER, -- task id to be processed
p_schema_name IN VARCHAR2, -- schema to load
p_remote_link IN VARCHAR2, -- DB Link to source database
p_job_name IN OUT VARCHAR2, -- job name for this import
p_job_handle IN OUT NUMBER -- Job handle for job created
);
PROCEDURE p_imp_table_vialink(p_schema_name IN VARCHAR2, -- schema for table
p_table_name IN VARCHAR2, -- table
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_remote_link IN VARCHAR2 DEFAULT NULL, -- remote database link
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
);
PROCEDURE p_imp_table(p_schema_name IN VARCHAR2, -- schema for talbe
p_table_name IN VARCHAR2, -- table to load
p_dumpfile_name IN VARCHAR2, -- dumpfile_name to load
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
);
PROCEDURE p_imp_tab_list_vialink(p_schema_name IN VARCHAR2, -- schema for table
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_expr_list IN VARCHAR2, -- expression list
p_remote_link IN VARCHAR2 DEFAULT NULL, -- remote database link
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
);
END pkg_dp_imp; -- Package spec pkg_dp_imp
/
CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_imp
IS
/*
Purpose: Import procedures and functions
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/4/2013 Initial Build
*/
PROCEDURE p_imp_schema(p_task_id IN NUMBER, -- task id to be processed
p_schema_name IN VARCHAR2, -- schema to load
p_dumpfile_name IN VARCHAR2, -- dumpfile_name to load
p_job_name IN OUT VARCHAR2, -- job name for this import
p_job_handle IN OUT NUMBER -- Job handle for job created
)
IS
/*
Purpose: Import a schema
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/20/2013 Initial Build
dcox 3/4/2013 Modified for DP example
*/
v_sid VARCHAR2(200) := oradba.pkg_dp_util.fn_get_sid; -- sid for this databaes
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
v_start_time DATE; -- start time for log file
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
/*not used here, can turn on later if needed needs more code below
v_degree INTEGER := 1; -- degree of parallelism */
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode
v_degree INTEGER := 1;
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
vc_job_mode CONSTANT VARCHAR2(30) := 'SCHEMA'; -- Job mode
vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
v_return NUMBER; -- function return variable
vc_local_fn CONSTANT VARCHAR2(30) := 'p_import_schema';
BEGIN
v_line_no := 100; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Schema Task Import Started. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
-- Create the log and dumpfile names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
ELSE
v_logfile_name :=
vc_operation
|| '_'
|| v_sid
|| '_'
|| TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI')
|| '_'
|| v_compatible
|| '_T'
|| p_task_id
|| '.log';
END IF;
v_line_no := 200; -- debug line no
-- Open the job
BEGIN
p_job_name := 'IMP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
v_handle :=
DBMS_DATAPUMP.open(operation => vc_operation,
job_mode => vc_job_mode,
job_name => p_job_name,
version => v_compatible);
p_job_handle := v_handle; -- Set the return value
-- Set the degree
DBMS_DATAPUMP.set_parallel(handle => v_handle, degree => v_degree);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
RAISE;
END;
v_line_no := 300; -- debug line no
-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 400; -- debug line no
-- Add a datafile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => p_dumpfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
v_line_no := 500; -- debug line no
-- Filter for schema
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
v_line_no := 710; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
v_line_no := 720; -- debug line no
DBMS_DATAPUMP.detach(handle => v_handle);
v_line_no := 800; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Schema Task Import Completed. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
EXCEPTION
WHEN OTHERS
THEN
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RAISE;
END p_imp_schema; -- Procedurep_imp_schema
---------------------
PROCEDURE p_imp_schema_vialink(p_task_id IN NUMBER, -- task id to be processed
p_schema_name IN VARCHAR2, -- schema to load
p_remote_link IN VARCHAR2, -- DB Link to source database
p_job_name IN OUT VARCHAR2, -- job name for this import
p_job_handle IN OUT NUMBER -- Job handle for job created
)
IS
/*
Purpose: Import a schema
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/20/2013 Initial Build
dcox 3/4/2013 Modified to be use in dp example
*/
v_sid VARCHAR2(200) := oradba.pkg_dp_util.fn_get_sid; -- sid for this databaes
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
v_start_time DATE; -- start time for log file
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
v_degree INTEGER := 1; -- degree of parallelism
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
vc_job_mode CONSTANT VARCHAR2(30) := 'SCHEMA'; -- Job mode
vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
v_message VARCHAR2(4000); -- error message to be raised
v_return NUMBER; -- Return error or success code
vc_local_fn CONSTANT VARCHAR2(30) := 'p_imp_schema_vialink';
BEGIN
v_line_no := 100; -- debug line no
DBMS_OUTPUT.put_line(SUBSTR(TO_CHAR('Schema Task with DB Link Started. '), 1, 255));
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Schema Task Import with DB Link Started. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
DBMS_OUTPUT.put_line(SUBSTR('1 v_return=' || TO_CHAR(v_return) || ' for ' || vc_local_fn, 1, 255));
-- verify all accouts are created before bringing over schema
v_return := oradba.pkg_dp_util.fn_get_rmt_user_n_role(p_remote_link => p_remote_link, p_schema_name => p_schema_name);
-- Check for errors
IF v_return < 0
THEN
v_message := 'Function oradba.pkg_dp_util.fn_get_rmt_user_n_role failed to get/create the missing users and roles';
raise_application_error(-20001, v_message);
END IF;
-- Create the log and dumpfile names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := vc_operation || '_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
ELSE
v_logfile_name :=
vc_operation
|| '_'
|| v_sid
|| '_'
|| TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI')
|| '_'
|| v_compatible
|| '_T'
|| p_task_id
|| '.log';
END IF;
v_line_no := 200; -- debug line no
-- Open the job
BEGIN
p_job_name := 'IMP_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
v_handle :=
DBMS_DATAPUMP.open(operation => vc_operation,
job_mode => vc_job_mode,
remote_link => p_remote_link,
job_name => p_job_name,
version => v_compatible);
p_job_handle := v_handle;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
RAISE;
END;
v_line_no := 300; -- debug line no
-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 500; -- debug line no
-- Filter for schema
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
v_line_no := 600; -- debug line no
-- Remap tablespace if needed - using function
v_return := oradba.pkg_dp_util.fn_tbs_remaps_for_link(p_remote_link => p_remote_link, p_handle => v_handle, p_schema_name => p_schema_name);
-- check for errors
IF v_return < 0
THEN
v_message :=
'Tablespace Remaps (oradba.pkg_dp_util.fn_tbs_remaps_for_link) failed for remote link: '
|| p_remote_link
|| ' Job Name:'
|| p_job_name;
raise_application_error(-20001, v_message);
END IF;
-- Get the start time
v_start_time := SYSDATE;
-- Add a start time to the log file
DBMS_DATAPUMP.log_entry(handle => v_handle, MESSAGE => 'Job Start at ' || TO_CHAR(v_start_time, 'DD-Mon-RR HH24:MI:SS'), log_file_only => 0);
v_line_no := 710; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
v_line_no := 720; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Schema Task Import with DB Link Completed. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
DBMS_OUTPUT.put_line(SUBSTR('2 v_return=' || TO_CHAR(v_return) || ' for ' || vc_local_fn, 1, 255));
v_line_no := 900; -- debug line no
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
p_message_text => SQLERRM(v_sqlcode),
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
-- detach just in case this was not reached in the code
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RAISE;
END p_imp_schema_vialink; -- procedure p_imp_schema_vialink
---------------------
PROCEDURE p_imp_table_vialink(p_schema_name IN VARCHAR2, -- schema for table
p_table_name IN VARCHAR2, -- table
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_remote_link IN VARCHAR2 DEFAULT NULL, -- remote database link
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
)
IS
/*
Purpose: Import a table - This version automatically remaps the tablespaces
based on either a default value or a mapping in a table (see fn_tbs_remaps_for_link for details)
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/20/2012 Initial Build
*/
v_sid VARCHAR2(200) := oradba.pkg_dp_util.fn_get_sid; -- sid for this databaes
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
v_start_time DATE; -- start time for log file
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
v_degree INTEGER := 1; -- degree of parallelism
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
vc_job_mode CONSTANT VARCHAR2(30) := 'TABLE'; -- Job mode
vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
v_message VARCHAR2(4000); -- error message to be raised
v_return NUMBER; -- function return error code
vc_local_fn CONSTANT VARCHAR2(30) := 'p_imp_table_vialink'; -- local function or procedure name
BEGIN
v_line_no := 100; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Schema Task Import Table w/link Started. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
-- Check parameters - p_schema_name
IF p_schema_name IS NULL
THEN
v_message := 'p_schema_name is null, this is needed to make this work.';
raise_application_error(-20001, v_message);
END IF;
-- Check parameters - p_table_name
IF p_table_name IS NULL
THEN
v_message := 'p_table_name is null, this is needed to make this work.';
raise_application_error(-20001, v_message);
END IF;
-- Check parameters - p_remote_link
IF p_remote_link IS NULL
THEN
v_message := 'p_remote_link is null, this is needed to make this work.';
raise_application_error(-20001, v_message);
END IF;
-- verify all accouts are created before bringing over schema/table
v_return :=
oradba.pkg_dp_util.fn_get_rmt_user_n_role(p_remote_link => p_remote_link, p_schema_name => p_schema_name, p_table_name => p_table_name);
-- Check for errors
IF v_return < 0
THEN
v_message := 'Function oradba.pkg_dp_util.fn_get_rmt_user_n_role failed to get/create the missing users and roles';
raise_application_error(-20001, v_message);
END IF;
-- Create the log and dumpfile names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
ELSE
v_logfile_name :=
'impdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
END IF;
v_line_no := 200; -- debug line no
-- Open the job
p_job_name := 'impdp_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
DBMS_OUTPUT.put_line(SUBSTR('Value of vc_operation=' || TO_CHAR(vc_operation), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of vc_job_mode=' || TO_CHAR(vc_job_mode), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of p_remote_link=' || TO_CHAR(p_remote_link), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of p_job_name=' || TO_CHAR(p_job_name), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of v_compatible=' || TO_CHAR(v_compatible), 1, 255));
v_line_no := 210; -- debug line no
BEGIN
v_handle :=
DBMS_DATAPUMP.open(operation => vc_operation,
job_mode => vc_job_mode,
remote_link => p_remote_link,
job_name => p_job_name,
version => v_compatible);
v_line_no := 220; -- debug line no
-- for output variables
p_job_handle := v_handle;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
RAISE;
END;
v_line_no := 300; -- debug line no
-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 500; -- debug line no
-- Filter for schema
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
v_line_no := 600; -- debug line no
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');
v_line_no := 650; -- debug line no
-- Remap tablespace if needed - using function
v_return := oradba.pkg_dp_util.fn_tbs_remaps_for_link(p_remote_link => p_remote_link, p_handle => v_handle, p_schema_name => p_schema_name);
-- check for errors
IF v_return < 0
THEN
v_message := 'Tablespace Remaps failed for remote link: ' || p_remote_link || ' Job Name:' || p_job_name;
raise_application_error(-20001, v_message);
END IF;
v_line_no := 710; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
v_line_no := 720; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Schema Task Import Table w/link Completed. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
DBMS_OUTPUT.put_line(SUBSTR('Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
p_message_text => SQLERRM(v_sqlcode),
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RAISE;
END p_imp_table_vialink; -- procedure p_imp_table_vialink
--------------------------------------
PROCEDURE p_imp_table(p_schema_name IN VARCHAR2, -- schema for talbe
p_table_name IN VARCHAR2, -- table to load
p_dumpfile_name IN VARCHAR2, -- dumpfile_name to load
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
)
IS
/*
Purpose: Import a table
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/20/2012 Initial Build
*/
v_sid VARCHAR2(200) := oradba.pkg_dp_util.fn_get_sid; -- sid for this databaes
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
v_start_time DATE; -- start time for log file
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
v_degree INTEGER := 1; -- degree of parallelism
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
vc_job_mode CONSTANT VARCHAR2(30) := 'TABLE'; -- Job mode
vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
v_return NUMBER; -- return error code
vc_local_fn CONSTANT VARCHAR(30) := 'p_imp_table'; -- local function or procedure name
BEGIN
v_line_no := 100; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Schema Task Import Table Started. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
-- Create the log and dumpfile names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := 'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
ELSE
v_logfile_name :=
'expdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
END IF;
v_line_no := 200; -- debug line no
-- Open the job
p_job_name := 'impdp_' || vc_job_mode || '_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
-- Open the job
BEGIN
v_handle :=
DBMS_DATAPUMP.open(operation => vc_operation,
job_mode => vc_job_mode,
job_name => p_job_name,
version => v_compatible);
-- for output variables
p_job_handle := v_handle;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
RAISE;
END;
v_line_no := 300; -- debug line no
-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 400; -- debug line no
-- Add a datafile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => p_dumpfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_dump_file);
v_line_no := 500; -- debug line no
-- Filter for schema
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
v_line_no := 600; -- debug line no
-- Filter for table
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_LIST', VALUE => '''' || p_table_name || '''');
v_line_no := 710; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
v_line_no := 720; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Schema Task Import Table Completed. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
p_message_text => SQLERRM(v_sqlcode),
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RAISE;
END p_imp_table; -- Procedure p_imp_table
---------------------
PROCEDURE p_imp_tab_list_vialink(p_schema_name IN VARCHAR2, -- schema for table
p_task_id IN oradba.dp_full.dp_task_id%TYPE, -- Task ID
p_expr_list IN VARCHAR2, -- expression list
p_remote_link IN VARCHAR2 DEFAULT NULL, -- remote database link
p_degree IN INTEGER DEFAULT 1, -- degree of parallelism
p_job_name IN OUT VARCHAR2, -- Job name assigned to this datapump job
p_job_handle IN OUT NUMBER -- Job handle for job created
)
IS
/*
Purpose: Import a list of tables from a "table list" - This version automatically remaps the tablespaces
based on either a default value or a mapping in a table (see fn_tbs_remaps_for_link for details)
A table List will include a list of tables to be imported and will be created as an expression here
Unlike some of the other procedures in this package, this one assumes that the target users are created.
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/20/2012 Initial Build
dcox 3/6/2012 Copied from other examples
*/
v_sid VARCHAR2(200) := oradba.pkg_dp_util.fn_get_sid; -- sid for this databaes
v_handle NUMBER; -- job handle
v_current_time DATE := SYSDATE; -- consistent timestamp for files, job_name etc.
v_start_time DATE; -- start time for log file
v_logfile_name VARCHAR2(200); -- logfile name
v_dumpfile_name VARCHAR2(200); -- logfile name
v_default_dir VARCHAR(30) := 'DATA_PUMP_DIR'; -- directory
v_degree INTEGER := 1; -- degree of parallelism
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode
v_compatible VARCHAR2(40) := 'COMPATIBLE'; -- default is 'COMPATIBLE'
vc_job_mode CONSTANT VARCHAR2(30) := 'TABLE'; -- Job mode
vc_operation CONSTANT VARCHAR2(30) := 'IMPORT'; -- operation
v_message VARCHAR2(4000); -- error message to be raised
v_return NUMBER; -- function return error code
vc_local_fn CONSTANT VARCHAR2(30) := 'p_imp_tab_list_vialink'; -- local function or procedure name
BEGIN
v_line_no := 100; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Task Import Table w/EXPR_LIST Started. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
-- Check parameters - p_schema_name
IF p_schema_name IS NULL
THEN
v_message := 'p_schema_name is null, this is needed to make this work.';
raise_application_error(-20001, v_message);
END IF;
-- Check parameters - p_remote_link
IF p_remote_link IS NULL
THEN
v_message := 'p_remote_link is null, this is needed to make this work.';
raise_application_error(-20001, v_message);
END IF;
-- Create the log and dumpfile names
IF v_compatible = 'COMPATIBLE'
THEN
v_logfile_name := 'impdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_T' || p_task_id || '.log';
ELSE
v_logfile_name :=
'impdp_' || v_sid || '_' || TO_CHAR(v_current_time, 'YYYY_MMDD_HH24MI') || '_' || v_compatible || '_T' || p_task_id || '.log';
END IF;
v_line_no := 200; -- debug line no
-- verify all accouts are created before bringing over schema
v_return := oradba.pkg_dp_util.fn_get_rmt_user_n_role(p_remote_link => p_remote_link, p_schema_name => p_schema_name);
-- Check for errors
IF v_return < 0
THEN
v_message := 'Function oradba.pkg_dp_util.fn_get_rmt_user_n_role failed to get/create the missing users and roles';
raise_application_error(-20001, v_message);
END IF;
-- Open the job
p_job_name := 'impdp_' || TO_CHAR(v_current_time, 'RR_MMDD_HH24MI_') || RTRIM(p_task_id, 10); -- add last 10 digits of the task id
DBMS_OUTPUT.put_line(SUBSTR('Value of vc_operation=' || TO_CHAR(vc_operation), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of vc_job_mode=' || TO_CHAR(vc_job_mode), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of p_remote_link=' || TO_CHAR(p_remote_link), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of p_job_name=' || TO_CHAR(p_job_name), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of v_compatible=' || TO_CHAR(v_compatible), 1, 255));
v_line_no := 210; -- debug line no
BEGIN
v_handle :=
DBMS_DATAPUMP.open(operation => vc_operation,
job_mode => vc_job_mode,
remote_link => p_remote_link,
job_name => p_job_name,
version => v_compatible);
v_line_no := 220; -- debug line no
-- for output variables
p_job_handle := v_handle;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Failure in dbms_datapump.open', 1, 255));
RAISE;
END;
v_line_no := 300; -- debug line no
-- Add a logfile
DBMS_DATAPUMP.add_file(handle => v_handle,
filename => v_logfile_name,
directory => v_default_dir,
filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
v_line_no := 500; -- debug line no
-- Filter for schema
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'SCHEMA_LIST', VALUE => '''' || p_schema_name || '''');
v_line_no := 600; -- debug line no
DBMS_DATAPUMP.metadata_filter(handle => v_handle, name => 'NAME_EXPR', VALUE => p_expr_list);
v_line_no := 650; -- debug line no
-- Remap tablespace if needed - using function
v_return := oradba.pkg_dp_util.fn_tbs_remaps_for_link(p_remote_link => p_remote_link, p_handle => v_handle, p_schema_name => p_schema_name);
-- check for errors
IF v_return < 0
THEN
v_message := 'Tablespace Remaps failed for remote link: ' || p_remote_link || ' Job Name:' || p_job_name;
raise_application_error(-20001, v_message);
END IF;
v_line_no := 710; -- debug line no
-- Start the job
DBMS_DATAPUMP.start_job(handle => v_handle);
v_line_no := 720; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Task Import Table w/EXPR_LIST Completed. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
DBMS_OUTPUT.put_line(SUBSTR('Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
p_message_text => SQLERRM(v_sqlcode),
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RAISE;
END p_imp_tab_list_vialink; -- procedure p_imp_tab_list_vialink
END pkg_dp_imp; -- package body pkg_dp_imp
/
CREATE OR REPLACE PACKAGE oradba.pkg_dp_main
IS
/*
Purpose:
pkg_dp_main: Control of DP automation jobs and tasks go here
pkg_dp_imp: Import funs, procs, etc.
pkg_dp_exp: Export funs, procs, etc.
pkg_dp_util: Utility funs, procs, etc. / Helper funs, procs, etc.
pkg_dp_monitor: Monitoring Tools funs, procs, etc.
pkg_dp_sql: Sql Execution funs, procs, etc.
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/28/2013 Initial Build
*/
PROCEDURE p_start_job(p_job_id IN oradba.dp_job.dp_job_id%TYPE, -- Job to be started
p_completion_code IN OUT INTEGER, -- numerical code to be primary evaluation return variable
p_complete_status IN OUT VARCHAR2 -- Description of status being returned numerically (don't use this for evaluation of success)
);
END; -- Package spec
/
CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_main
IS
/*
Purpose:
pkg_dp_main: Control of DP automation jobs and tasks go here
pkg_dp_imp: Import funs, procs, etc.
pkg_dp_exp: Export funs, procs, etc.
pkg_dp_util: Utility funs, procs, etc. / Helper funs, procs, etc.
pkg_dp_monitor: Monitoring Tools funs, procs, etc.
pkg_dp_sql: Sql Execution funs, procs, etc.
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/28/2013 Initial Build
*/
PROCEDURE p_start_job(p_job_id IN oradba.dp_job.dp_job_id%TYPE, -- Job to be started
p_completion_code IN OUT INTEGER, -- numerical code to be primary evaluation return variable
p_complete_status IN OUT VARCHAR2 -- Description of status being returned numerically (don't use this for evaluation of success)
)
IS
/*
Purpose: This procedure starts jobs, gets the tasks to be executed, initiates tasks
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/28/2013 Initial Build
*/
-- get the job info
CURSOR c_get_job(cv_dp_job_id IN oradba.dp_job.dp_job_id%TYPE)
IS
SELECT *
FROM oradba.dp_job
WHERE dp_job_id = cv_dp_job_id;
vrec_job c_get_job%ROWTYPE; -- job record
-- get the task info
CURSOR c_get_tasks(cv_dp_job_id IN oradba.dp_task.dp_job_id%TYPE)
IS
SELECT *
FROM oradba.dp_task
WHERE dp_job_id = cv_dp_job_id
ORDER BY execution_order;
-- get the full info
CURSOR c_get_full(cv_dp_task_id IN oradba.dp_full.dp_task_id%TYPE)
IS
SELECT *
FROM oradba.dp_full
WHERE dp_task_id = cv_dp_task_id;
vrec_full c_get_full%ROWTYPE; -- record type for full
-- get the full info
CURSOR c_get_schema(cv_dp_task_id IN oradba.dp_full.dp_task_id%TYPE)
IS
SELECT *
FROM oradba.dp_schema
WHERE dp_task_id = cv_dp_task_id;
vrec_schema c_get_schema%ROWTYPE;
v_monitor_interval CONSTANT INTEGER := 1; -- default monitor interval in seconds
v_job_name VARCHAR2(30); -- job name for process created
v_job_handle NUMBER; -- job handle for process created
v_current_schema VARCHAR2(30); -- current schema being executed
v_percent_complete INTEGER; -- current percent complete
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode return value
v_message VARCHAR2(32767); -- error message
vc_local_fn CONSTANT VARCHAR2(30) := 'p_start_job'; -- local procedure or function name
v_return NUMBER; -- return value for functions
v_task_id oradba.dp_task.dp_task_id%TYPE; -- type id
v_job_state VARCHAR2(32767); -- job state of datapump job
BEGIN
v_line_no := 10100; -- debug line no
-- Get job info
OPEN c_get_job(p_job_id);
FETCH c_get_job INTO vrec_job;
CLOSE c_get_job;
-- check for successful find
IF vrec_job.dp_job_id IS NULL
THEN
v_message := 'Could not find job id: ' || vrec_job.dp_job_id;
raise_application_error(-20001, v_message);
END IF;
v_return :=
oradba.pkg_dp_util.fn_logging(
p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Job ' || vrec_schema.dp_task_id || ' ' || NVL(vrec_job.job_description, vrec_job.job_name) || ' started.',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_job_id => vrec_job.dp_job_id);
v_line_no := 10200; -- debug line no
-- Get Task Info
FOR ntasks IN c_get_tasks(p_job_id)
LOOP
v_line_no := 10300; -- debug line no
v_task_id := ntasks.dp_task_id; -- task id for error collection only
DBMS_OUTPUT.put_line(SUBSTR('Value of v_task_id=' || TO_CHAR(v_task_id), 1, 255));
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Task ' || NVL(ntasks.task_description, ntasks.task_name) || ' started. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_job_id => vrec_job.dp_job_id,
p_task_id => ntasks.dp_task_id);
v_line_no := 10350; -- debug line no
-- Execute tasks with correct package
-- Check for Export or Import
IF ntasks.task_operation = 'EXPORT'
THEN
v_line_no := 10400; -- debug line no
IF ntasks.task_type = 'FULL'
THEN
v_line_no := 10410; -- debug line no
OPEN c_get_full(ntasks.dp_task_id);
FETCH c_get_full INTO vrec_full;
CLOSE c_get_full;
oradba.pkg_dp_exp.p_export_full(ntasks.dp_task_id, v_job_name, v_job_handle);
ELSIF ntasks.task_type = 'SCHEMA'
THEN
v_line_no := 10420; -- debug line no
OPEN c_get_schema(ntasks.dp_task_id);
FETCH c_get_schema INTO vrec_schema;
CLOSE c_get_schema;
v_line_no := 10430; -- debug line no
-- Run the schema export
oradba.pkg_dp_exp.p_export_schema(vrec_schema.schema_name, -- p_schema_name
ntasks.dp_task_id, -- p_task_id
vrec_schema.remote_link, -- p_remote_link
vrec_schema.degree, -- degree parallel
v_job_name, -- job name returned
v_job_handle); -- job handle returned
END IF; -- end of check for type of export - schema - full
-- Check for import
ELSIF ntasks.task_operation = 'IMPORT'
THEN
v_line_no := 10440; -- debug line no
-- Check for schema
IF ntasks.task_type IN ('SCHEMA', 'TABLE')
THEN
v_line_no := 10450; -- debug line no
OPEN c_get_schema(ntasks.dp_task_id);
FETCH c_get_schema INTO vrec_schema;
CLOSE c_get_schema;
-- check for schema only
IF vrec_schema.table_name IS NULL AND vrec_schema.table_subquery IS NULL AND vrec_schema.expr_list IS NULL
THEN
-- is over DB Link
IF vrec_schema.remote_link IS NOT NULL
THEN
v_line_no := 10470; -- debug line no
oradba.pkg_dp_imp.p_imp_schema_vialink(p_task_id => vrec_schema.dp_task_id, -- task id
p_schema_name => vrec_schema.schema_name, -- schema name
p_remote_link => vrec_schema.remote_link, -- remote database link
p_job_name => v_job_name, -- job name for this import
p_job_handle => v_job_handle); -- job handle
-- Else use version with dumpfile name
-- not yet coded - dumpfile version goes here xxxxxxxxxxxxxxxx
END IF; -- end check for remote link or local file
ELSIF vrec_schema.table_name IS NOT NULL
THEN
-- is over DB Link
IF vrec_schema.remote_link IS NOT NULL
THEN
v_line_no := 10480; -- debug line no
oradba.pkg_dp_imp.p_imp_table_vialink(p_schema_name => vrec_schema.schema_name, -- schema name
p_table_name => vrec_schema.table_name, -- table name
p_task_id => vrec_schema.dp_task_id, -- task id
p_remote_link => vrec_schema.remote_link, -- remote database link
p_degree => vrec_schema.degree, -- degree parallel
p_job_name => v_job_name, -- job name for this import
p_job_handle => v_job_handle); -- job handle
-- Else use version with dumpfile name
-- not yet coded - dumpfile version goes here xxxxxxxxxxxxxxxx
END IF; -- end check for remote link or local file
ELSIF vrec_schema.expr_list IS NOT NULL
THEN
-- is over DB Link
IF vrec_schema.remote_link IS NOT NULL
THEN
v_line_no := 10490; -- debug line no
oradba.pkg_dp_imp.p_imp_tab_list_vialink(p_schema_name => vrec_schema.schema_name, -- schema name
p_task_id => vrec_schema.dp_task_id, -- task id
p_expr_list => vrec_schema.expr_list, -- expression list
p_remote_link => vrec_schema.remote_link, -- remote database link
p_job_name => v_job_name, -- job name for this import
p_job_handle => v_job_handle); -- job handle
-- Else use version with dumpfile name
-- not yet coded - dumpfile version goes here xxxxxxxxxxxxxxxx
END IF; -- end check for remote link or local file
END IF; -- check for subquery and table_expression
END IF; -- end check for schema import, table import, table list
v_line_no := 10500; -- debug line no
ELSIF ntasks.task_operation = 'SQL_EXEC'
THEN
v_line_no := 10600; -- debug line no
-- execute sql script
v_return := oradba.pkg_dp_sql.fn_execute_sql(p_task_id => v_task_id, -- task id
p_message => v_message -- message
);
IF v_return < 0
THEN
raise_application_error(-20001, v_return || ' ' || v_message);
END IF;
ELSE
v_line_no := 10700; -- debug line no
-- task Operation type not coded
v_message := 'This task operation type "' || ntasks.task_operation || '"is not coded.';
raise_application_error(-20001, v_message);
END IF; -- End check for export or import
v_line_no := 10800; -- debug line no
-- do we monitor?
IF ntasks.monitor_to_completion = 'Y' AND ntasks.task_operation != 'SQL_EXEC'
THEN
v_line_no := 10900; -- debug line no
BEGIN
DBMS_DATAPUMP.wait_for_job(v_job_handle, --handle
v_job_state --job_state -
);
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
v_return :=
oradba.pkg_dp_util.fn_logging(
p_code => v_sqlcode,
p_message_text => 'Job:'
|| vrec_job.dp_job_id
|| ' failed with job state:'
|| v_job_state
|| ' on task ID:'
|| vrec_schema.dp_task_id,
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_job_id => vrec_job.dp_job_id,
p_task_id => v_task_id);
RAISE;
END;
/* Alternate code
LOOP
v_line_no := 10800; -- debug line no
-- monitor task
v_percent_complete := pkg_dp_monitors.fn_get_pct_complete(p_job_name => v_job_name, p_job_owner => v_current_schema);
DBMS_OUTPUT.put_line(SUBSTR('Value of v_percent_complete=' || TO_CHAR(v_percent_complete), 1, 255));
IF v_percent_complete < 0 OR v_percent_complete >= 100
THEN
EXIT;
END IF;
DBMS_LOCK.sleep(seconds => v_monitor_interval);
END LOOP;*/
v_line_no := 10900; -- debug line no
-- Log completion
END IF;
v_line_no := 11000; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(
p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Task ' || ntasks.dp_task_id || ' ' || NVL(ntasks.task_description, ntasks.task_name) || ' Completed. ',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_job_id => vrec_job.dp_job_id,
p_task_id => ntasks.dp_task_id);
END LOOP; -- end of tasks loop
-- return status
v_line_no := 11100; -- debug line no
v_return :=
oradba.pkg_dp_util.fn_logging(
p_code => oradba.pkg_dp_util.logging_informational,
p_message_text => 'Job '
|| NVL(vrec_job.job_description, vrec_job.job_name)
|| ' completed. Note if this was submitted asynchronously it may still be running - see monitoring.',
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_job_id => vrec_job.dp_job_id);
-- Completion status
p_completion_code := 0;
p_complete_status := 'Success';
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
p_completion_code := v_sqlcode; -- return code
p_complete_status := SQLERRM(v_sqlcode);
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => p_completion_code,
p_message_text => p_complete_status,
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_job_id => vrec_job.dp_job_id,
p_task_id => v_task_id);
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('main - Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
RAISE;
END p_start_job; -- Procedure p_start_job
END;
/
CREATE OR REPLACE PACKAGE oradba.pkg_dp_monitors
IS
/*
Purpose: Monitoring Examples
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/20/2012 Initial Build
*/
------------------------------------
PROCEDURE p_job_status(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE, -- job_owner from dba_datapump_job
p_bool_job_status IN OUT BOOLEAN, -- true when Jobs status is true
p_operation IN OUT VARCHAR2, -- operation
p_job_mode IN OUT VARCHAR2, -- job_mode
p_bytes_processed IN OUT NUMBER, -- bytes_processed
p_total_bytes IN OUT NUMBER, -- total_bytes
p_percent_done IN OUT NUMBER, -- percent_done
p_degree IN OUT NUMBER, -- degree
p_error_count IN OUT NUMBER, -- error_count
p_state IN OUT VARCHAR2, -- state
p_phase IN OUT NUMBER, -- phase
p_restart_count IN OUT NUMBER, -- restart_count
p_worker_status_list IN OUT ku$_workerstatuslist1120, -- worker status list
p_file_list IN OUT ku$_dumpfileset1010 -- file list
);
PROCEDURE p_job_description(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE, -- job_owner from dba_datapump_job
p_bool_job_description IN OUT BOOLEAN, -- true when Jobs status is true
p_operation IN OUT VARCHAR2, -- operation
p_guid IN OUT VARCHAR, -- guid
p_job_mode IN OUT VARCHAR2, -- job_mode
p_remote_link IN OUT VARCHAR2, -- remote_link
p_platform IN OUT VARCHAR2, -- platform
p_exp_platform IN OUT VARCHAR2, -- exp_platform
p_global_name IN OUT VARCHAR2, -- global_name
p_exp_global_name IN OUT VARCHAR2, -- exp_global_name
p_instance IN OUT VARCHAR2, -- instance
p_db_version IN OUT VARCHAR2, -- db_version
p_exp_db_version IN OUT VARCHAR2, -- exp_db_version
p_scn IN OUT NUMBER, -- scn
p_creator_privs IN OUT VARCHAR2, -- creator_privs
p_start_time IN OUT DATE, -- start_time
p_exp_start_time IN OUT DATE, -- exp_start_time
p_term_reason IN OUT NUMBER, -- term_reason
p_max_degree IN OUT NUMBER, -- max_degree
p_log_file IN OUT VARCHAR2, -- log_file
p_sql_file IN OUT VARCHAR2, -- sql_file
p_parameters IN OUT ku$_paramvalues1010 -- Parameters
);
PROCEDURE p_wip_or_error(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE, -- job_owner from dba_datapump_job
p_action_desired IN NUMBER, -- pass the constant bit ( KU$_STATUS_WIP or KU$_STATUS_JOB_ERROR) currently 1 and 8
p_bool_wip_or_error IN OUT BOOLEAN, -- true when Jobs status is true
p_log_entries IN OUT ku$_logentry -- Error or WIP results for log entries
);
FUNCTION fn_get_pct_complete(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE -- job_owner from dba_datapump_job
)
RETURN INTEGER;
END; -- Package spec
/
CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_monitors
IS
/*
Purpose: Monitoring Examples
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/20/2012 Initial Build
*/
PROCEDURE p_job_status(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE, -- job_owner from dba_datapump_job
p_bool_job_status IN OUT BOOLEAN, -- true when job status has data
p_operation IN OUT VARCHAR2, -- operation
p_job_mode IN OUT VARCHAR2, -- job_mode
p_bytes_processed IN OUT NUMBER, -- bytes_processed
p_total_bytes IN OUT NUMBER, -- total_bytes
p_percent_done IN OUT NUMBER, -- percent_done
p_degree IN OUT NUMBER, -- degree
p_error_count IN OUT NUMBER, -- error_count
p_state IN OUT VARCHAR2, -- state
p_phase IN OUT NUMBER, -- phase
p_restart_count IN OUT NUMBER, -- restart_count
p_worker_status_list IN OUT ku$_workerstatuslist1120, -- worker status list
p_file_list IN OUT ku$_dumpfileset1010 -- file list
)
IS
/*
Purpose: attaches and returns the job status structure
Parses the bit
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/20/2013 Initial Build
*/
v_line_no INTEGER := 0; -- debug line no
v_handle NUMBER; -- job handle
v_status ku$_status; -- job status
v_sqlcode NUMBER; -- sqlcode
vc_indefinate_wait_timeout CONSTANT INTEGER := -1; -- specify an indefinate wait
v_job_state VARCHAR2(40); -- Job state from job_status procedure
-- vc_timeout constant integer := 5; -- specify a xx seconds(s) timeout to wait for status ( Uncomment and use if necessary)
BEGIN
v_line_no := 100; -- debug line no
-- Attach to a datapump session
v_handle := DBMS_DATAPUMP.attach(job_name => p_job_name, job_owner => p_job_owner);
v_line_no := 200; -- debug line no
-- Monitor status
DBMS_DATAPUMP.get_status(handle => v_handle,
mask => DBMS_DATAPUMP.ku$_status_job_status,
timeout => vc_indefinate_wait_timeout,
job_state => v_job_state,
status => v_status);
-- Decode the bit masks
-- job status
IF BITAND(v_status.mask, DBMS_DATAPUMP.ku$_status_job_status) != 0
THEN
p_bool_job_status := TRUE;
ELSE
p_bool_job_status := FALSE;
END IF;
v_line_no := 300; -- debug line no
-- Detach from handle
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
IF p_bool_job_status
THEN
-- Return data
p_operation := v_status.job_status.operation; -- operation
p_job_mode := v_status.job_status.job_mode; -- job_mode
p_bytes_processed := v_status.job_status.bytes_processed; -- bytes_processed
p_total_bytes := v_status.job_status.total_bytes; -- total_bytes
p_percent_done := v_status.job_status.percent_done; -- percent_done
p_degree := v_status.job_status.degree; -- degree
p_error_count := v_status.job_status.error_count; -- error_count
p_state := v_status.job_status.state; -- state
p_phase := v_status.job_status.phase; -- phase
p_restart_count := v_status.job_status.restart_count; -- restart_count
-- Get Worker Status List
p_worker_status_list := v_status.job_status.worker_status_list; -- worker status list
-- Get dump Files List
p_file_list := v_status.job_status.files; -- files list
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
--DBMS_OUTPUT.put_line(SUBSTR('Value of v_sqlcode=' || TO_CHAR(v_sqlcode), 1, 255));
--DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
RAISE;
END p_job_status; -- end procedure p_job_status
--------------------------
PROCEDURE p_job_description(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE, -- job_owner from dba_datapump_job
p_bool_job_description IN OUT BOOLEAN, -- true when Jobs status is true
p_operation IN OUT VARCHAR2, -- operation
p_guid IN OUT VARCHAR, -- guid
p_job_mode IN OUT VARCHAR2, -- job_mode
p_remote_link IN OUT VARCHAR2, -- remote_link
p_platform IN OUT VARCHAR2, -- platform
p_exp_platform IN OUT VARCHAR2, -- exp_platform
p_global_name IN OUT VARCHAR2, -- global_name
p_exp_global_name IN OUT VARCHAR2, -- exp_global_name
p_instance IN OUT VARCHAR2, -- instance
p_db_version IN OUT VARCHAR2, -- db_version
p_exp_db_version IN OUT VARCHAR2, -- exp_db_version
p_scn IN OUT NUMBER, -- scn
p_creator_privs IN OUT VARCHAR2, -- creator_privs
p_start_time IN OUT DATE, -- start_time
p_exp_start_time IN OUT DATE, -- exp_start_time
p_term_reason IN OUT NUMBER, -- term_reason
p_max_degree IN OUT NUMBER, -- max_degree
p_log_file IN OUT VARCHAR2, -- log_file
p_sql_file IN OUT VARCHAR2, -- sql_file
p_parameters IN OUT ku$_paramvalues1010 -- Parameters
)
IS
/*
Purpose: attaches and returns the status structure
Parses the bit
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/20/2013 Initial Build
*/
v_line_no INTEGER := 0; -- debug line no
v_handle NUMBER; -- job handle
v_status ku$_status; -- job status
v_sqlcode NUMBER; -- sqlcode
vc_indefinate_wait_timeout CONSTANT INTEGER := -1; -- specify an indefinate wait
v_job_state VARCHAR2(40); -- Job state from job_status procedure
-- vc_timeout constant integer := 5; -- specify a xx seconds(s) timeout to wait for status ( Uncomment and use if necessary)
BEGIN
v_line_no := 100; -- debug line no
-- Attach to a datapump session
v_handle := DBMS_DATAPUMP.attach(job_name => p_job_name, job_owner => p_job_owner);
v_line_no := 200; -- debug line no
-- Monitor status
DBMS_DATAPUMP.get_status(handle => v_handle,
mask => DBMS_DATAPUMP.ku$_status_job_desc,
timeout => vc_indefinate_wait_timeout,
job_state => v_job_state,
status => v_status);
-- Decode the bit masks
-- job status
IF BITAND(v_status.mask, DBMS_DATAPUMP.ku$_status_job_desc) != 0
THEN
p_bool_job_description := TRUE;
ELSE
p_bool_job_description := FALSE;
END IF;
v_line_no := 400; -- debug line no
IF p_bool_job_description
THEN
v_line_no := 401; -- debug line no
-- Return data
p_operation := v_status.job_description.operation; -- operation
p_guid := v_status.job_description.guid; -- guid
p_job_mode := v_status.job_description.job_mode; -- job_mode
p_remote_link := v_status.job_description.remote_link; -- remote_link
p_platform := v_status.job_description.platform; -- platform
p_exp_platform := v_status.job_description.exp_platform; -- exp_platform
p_global_name := v_status.job_description.global_name; -- global_name
p_exp_global_name := v_status.job_description.exp_global_name; -- exp_global_name
p_instance := v_status.job_description.instance; -- instance
p_db_version := v_status.job_description.db_version; -- db_version
p_exp_db_version := v_status.job_description.exp_db_version; -- exp_db_version
p_scn := v_status.job_description.scn; -- scn
p_creator_privs := v_status.job_description.creator_privs; -- creator_privs
p_start_time := v_status.job_description.start_time; -- start_time
p_exp_start_time := v_status.job_description.exp_start_time; -- exp_start_time
p_term_reason := v_status.job_description.term_reason; -- term_reason
p_max_degree := v_status.job_description.max_degree; -- max_degree
p_log_file := v_status.job_description.log_file; -- log_file
p_sql_file := v_status.job_description.sql_file; -- sql_file
v_line_no := 500; -- debug line no
-- Get parameters
p_parameters := v_status.job_description.params; -- Parameter list
END IF;
-- Detach from handle
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
v_line_no := 510; -- debug line no
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
RAISE;
END p_job_description; -- end procedure p_job_description
------------------------------------
PROCEDURE p_wip_or_error(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE, -- job_owner from dba_datapump_job
p_action_desired IN NUMBER, -- pass the constant bit ( KU$_STATUS_WIP or KU$_STATUS_JOB_ERROR) currently 1 and 8
p_bool_wip_or_error IN OUT BOOLEAN, -- true when Jobs status is true
p_log_entries IN OUT ku$_logentry -- Error or WIP results for log entries
)
IS
/*
Purpose: attaches and returns the status structure
Parses the bit
Uses the same structure to display info
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/8/2013 Initial Build
*/
v_line_no INTEGER := 0; -- debug line no
v_handle NUMBER; -- job handle
v_index NUMBER; -- array index
v_status ku$_status; -- job status
v_sqlcode NUMBER; -- sqlcode
vc_indefinate_wait_timeout CONSTANT INTEGER := -1; -- specify an indefinate wait
v_job_state VARCHAR2(40); -- Job state from job_status procedure
v_action VARCHAR2(10); -- WIP or ERROR
v_data ku$_logentry; -- data to be returned
-- vc_timeout constant integer := 5; -- specify a xx seconds(s) timeout to wait for status ( Uncomment and use if necessary)
BEGIN
v_line_no := 100; -- debug line no
-- Attach to a datapump session
v_handle := DBMS_DATAPUMP.attach(job_name => p_job_name, job_owner => p_job_owner);
v_line_no := 200; -- debug line no
-- Monitor status
DBMS_DATAPUMP.get_status(handle => v_handle,
mask => p_action_desired,
timeout => vc_indefinate_wait_timeout,
job_state => v_job_state,
status => v_status);
-- Decode the bit mask
IF BITAND(p_action_desired, DBMS_DATAPUMP.ku$_status_wip) != 0
THEN
v_action := 'WIP';
IF BITAND(v_status.mask, DBMS_DATAPUMP.ku$_status_wip) != 0
THEN
p_bool_wip_or_error := TRUE;
ELSE
p_bool_wip_or_error := FALSE;
END IF;
v_data := v_status.wip;
ELSE
v_action := 'ERROR';
IF BITAND(v_status.mask, DBMS_DATAPUMP.ku$_status_job_error) != 0
THEN
p_bool_wip_or_error := TRUE;
ELSE
p_bool_wip_or_error := FALSE;
END IF;
v_data := v_status.error;
END IF;
-- read if data is not null
IF p_bool_wip_or_error AND v_data IS NOT NULL
THEN
-- set log parameter for output
p_log_entries := v_data;
/* ----- Optional - to display via DBMS_OUTPUT ------
-- Return data
v_index := v_data.FIRST;
WHILE v_index IS NOT NULL
LOOP
DBMS_OUTPUT.put_line(SUBSTR('Value of v_index=' || TO_CHAR(v_index), 1, 255));
DBMS_OUTPUT.put_line(v_data(v_index).loglinenumber);
DBMS_OUTPUT.put_line(v_data(v_index).errornumber);
DBMS_OUTPUT.put_line(v_data(v_index).logtext);
DBMS_OUTPUT.put_line('--------------------');
v_index := v_data.NEXT(v_index);
END LOOP;*/
END IF;
-- Detach from handle
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
BEGIN
DBMS_DATAPUMP.detach(handle => v_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
RAISE;
END p_wip_or_error; -- end proceedure p_job_status
---------------------------
FUNCTION fn_get_pct_complete(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE -- job_owner from dba_datapump_job
)
RETURN INTEGER
IS
/*
Purpose: Returns the percent complete or -1 if there is an error or no data
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/1/2013 Initial Build
*/
-- dummy values except v_percent_done
v_bool_job_status BOOLEAN; -- true when job status has data
v_operation VARCHAR2(200); -- operation
v_job_mode VARCHAR2(200); -- job_mode
v_bytes_processed NUMBER; -- bytes_processed
v_total_bytes NUMBER; -- total_bytes
v_percent_done NUMBER; -- percent_done
v_degree NUMBER; -- degree
v_error_count NUMBER; -- error_count
v_state VARCHAR2(200); -- state
v_phase NUMBER; -- phase
v_restart_count NUMBER; -- restart_count
v_worker_status_list ku$_workerstatuslist1120; -- worker status list
v_file_list ku$_dumpfileset1010; -- file list
BEGIN
p_job_status(p_job_name,
p_job_owner,
v_bool_job_status,
v_operation,
v_job_mode,
v_bytes_processed,
v_total_bytes,
v_percent_done,
v_degree,
v_error_count,
v_state,
v_phase,
v_restart_count,
v_worker_status_list,
v_file_list);
IF v_percent_done NOT BETWEEN 0 AND 100
THEN
raise_application_error(-20001, 'Error');
END IF;
RETURN v_percent_done;
EXCEPTION
WHEN OTHERS
THEN
RETURN -1;
END;
END;
/
CREATE OR REPLACE PACKAGE oradba.pkg_dp_sql
IS
/*
Purpose: This package has functions and procedures to execute a set of sql statements
that don't return any values. For example, inserts, updates, DDL (create user, drop user, add permission).
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/9/2013 Initial Build
*/
FUNCTION fn_execute_sql(p_task_id IN oradba.dp_sql.dp_task_id%TYPE, -- task to be executed
p_message IN OUT VARCHAR2 -- return message
)
RETURN NUMBER;
END pkg_dp_sql; -- pkg spec pkg_dp_sql
/
CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_sql
IS
/*
Purpose: This package has functions and procedures to execute a set of sql statements
that don't return any values. For example, inserts, updates, DDL (create user, drop user, add permission).
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/9/2013 Initial Build
*/
FUNCTION fn_execute_sql(p_task_id IN oradba.dp_sql.dp_task_id%TYPE, -- task to be executed
p_message IN OUT VARCHAR2 -- return message
)
RETURN NUMBER
IS
/*
Purpose: This function executes the sql called for the task ID submitted
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/11/2013 Initial Build
*/
-- get the sql to execute
CURSOR c_get_sql(cv_task_id IN oradba.dp_sql.dp_task_id%TYPE)
IS
SELECT *
FROM oradba.dp_sql s
WHERE s.dp_task_id = cv_task_id;
v_sqlcode NUMBER; -- sql error code
vrec_sql c_get_sql%ROWTYPE; -- record variable
v_return NUMBER; -- function return code
vc_local_fn CONSTANT VARCHAR2(30) := 'fn_execute_sql'; -- local function or procedure name
v_line_no INTEGER := 0; -- debug line no
BEGIN
v_line_no := 100; -- debug line no
-- get the sql to execute
OPEN c_get_sql(p_task_id);
FETCH c_get_sql INTO vrec_sql;
CLOSE c_get_sql;
BEGIN
--execute sql command
EXECUTE IMMEDIATE vrec_sql.sql_stmt;
-- commit any changes
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
IF vrec_sql.ignore_error_yn = 'Y'
THEN
NULL;
ELSE
RAISE;
END IF;
END;
p_message := 'Success';
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
p_message := SQLERRM(v_sqlcode);
ROLLBACK;
-- Log error
v_return :=
oradba.pkg_dp_util.fn_logging(p_code => v_sqlcode,
p_message_text => SQLERRM(v_sqlcode),
p_object_schema => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
p_object_name => $$plsql_unit || '.' || vc_local_fn,
p_line_no => v_line_no,
p_task_id => p_task_id);
RETURN v_sqlcode;
END;
END pkg_dp_sql; -- pkg body pkg_dp_sql
/
CREATE OR REPLACE PACKAGE oradba.pkg_dp_util
IS
/*
Purpose: Data Pump Automation Utilities go in this package
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/28/2013 Initial Build
*/
-- default tablespace
vcg_default_tablespace CONSTANT oradba.remap_tablespace.local_tablespace%TYPE := 'USERS'; -- package global default for the default tablespace
-- Logging codes for Success and information
logging_success CONSTANT INTEGER := 0;
logging_informational CONSTANT INTEGER := 1;
FUNCTION fn_get_sid
RETURN VARCHAR2;
FUNCTION fn_stop_job(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE -- job_owner from dba_datapump_job
)
RETURN NUMBER;
FUNCTION fn_check_add_user(p_permission_object IN VARCHAR2, -- user or role to check and possibly add
p_remote_link IN VARCHAR2)
RETURN NUMBER;
FUNCTION fn_get_rmt_user_n_role(p_remote_link IN VARCHAR2, -- remote database link
p_schema_name IN VARCHAR2, -- schema name to be imported
p_table_name IN VARCHAR2 DEFAULT NULL -- table name to be imported
)
RETURN NUMBER;
FUNCTION fn_remap_tablespaces(p_remote_tablespace IN VARCHAR2, -- The "FROM" tablespace at the remote database
p_bool_always_remap IN BOOLEAN DEFAULT FALSE, -- always remap and don't use the local name if it exists, but still use default if not found on remap
p_local_tablespace IN OUT VARCHAR2 -- The "TO" tablespace here on the local database
)
RETURN NUMBER;
FUNCTION fn_tbs_remaps_for_link(p_remote_link IN VARCHAR2, -- remote link - database data to be imported via link
p_handle IN NUMBER, -- handle opened for import
p_schema_name IN VARCHAR2, -- schema to be imported
p_table_name IN VARCHAR2 DEFAULT NULL -- table or object to be imported
)
RETURN NUMBER;
FUNCTION fn_logging(p_code IN oradba.dp_logging.code_returned%TYPE, -- 0 - Success, 1 - General information , < 0 error codes
p_message_text IN oradba.dp_logging.MESSAGE_TEXT%TYPE, -- message text
p_object_schema IN oradba.dp_logging.object_schema%TYPE, -- Object schema
p_object_name IN oradba.dp_logging.object_name%TYPE, -- Object name
p_line_no IN INTEGER, -- line number where error occurred
p_job_id IN oradba.dp_logging.job_id%TYPE DEFAULT NULL, -- Job ID
p_task_id IN oradba.dp_logging.task_id%TYPE DEFAULT NULL, -- Task ID
p_skip_commit_yn IN VARCHAR2 DEFAULT 'N' -- If for some reason the commit needs to be skipped here in this function then this should be 'Y'
)
RETURN NUMBER;
FUNCTION fn_clean_stopped_job(p_job_owner IN VARCHAR2, -- job owner
p_job_name IN VARCHAR2 -- job name
)
RETURN NUMBER;
FUNCTION fn_create_userperm_script(p_username IN VARCHAR2, -- create script for specific user
p_remote_link IN VARCHAR2 -- remote link to remote database
)
RETURN NUMBER;
FUNCTION fn_create_permissions_script(p_remote_link IN VARCHAR2) -- remote link to remote database
RETURN NUMBER;
END pkg_dp_util; -- Package spec pkg_dp_util
/
CREATE OR REPLACE PACKAGE BODY oradba.pkg_dp_util
IS
/*
Purpose: Data Pump Automation Utilities go in this package
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/28/2013 Initial Build
*/
--------------------------------------------
FUNCTION fn_get_sid
RETURN VARCHAR2
IS
/*
Purpose: Get's the SID for the database
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 10/19/2012 Initial Build
*/
v_sid VARCHAR2(1000); -- sid for the database
BEGIN
SELECT name INTO v_sid FROM v$database;
RETURN v_sid;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END fn_get_sid; -- Function fn_get_sid
----------------------------
FUNCTION fn_stop_job(p_job_name IN dba_datapump_jobs.job_name%TYPE, -- Job Name - from dba_datapump_job
p_job_owner IN dba_datapump_jobs.owner_name%TYPE -- job_owner from dba_datapump_job
)
RETURN NUMBER
IS
/*
Purpose: This function stops a job with job_name and job_owner
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/1/2013 Initial Build
*/
v_line_no INTEGER := 0; -- debug line no
v_handle NUMBER; -- handle for datapump job
BEGIN
v_line_no := 100; -- debug line no
-- Attach to a datapump session
v_handle := DBMS_DATAPUMP.attach(job_name => p_job_name, job_owner => p_job_owner);
-- Add a entry to the log file
DBMS_DATAPUMP.log_entry(handle => v_handle,
MESSAGE => 'Manuallying Stopping Job at ' || TO_CHAR(SYSDATE, 'DD-Mon-RR HH24:MI:SS'),
log_file_only => 0);
DBMS_DATAPUMP.stop_job(handle => v_handle,
immediate => 1,
keep_master => 0,
delay => 30);
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
RETURN SQLCODE;
END;
----------------------------
FUNCTION fn_get_rmt_user_n_role(p_remote_link IN VARCHAR2, -- remote database link
p_schema_name IN VARCHAR2, -- schema name to be imported
p_table_name IN VARCHAR2 DEFAULT NULL -- table name to be imported
)
RETURN NUMBER
IS
/*
Purpose: Get users or roles granted on remote database objects to be imported
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/21/2013 Initial Build
*/
v_sqlcode NUMBER; -- sqlcode error
v_sql VARCHAR2(32767); -- sql code to be executed
v_return NUMBER; -- return code
v_grantee VARCHAR2(30); -- user or role
v_message VARCHAR2(32767); -- custom message to be reported for sql error
TYPE t_cur_grantees IS REF CURSOR; --Create type as ref cursor
vrefcur_grantees t_cur_grantees; -- create reference cursor variable
BEGIN
-- Get missing users and roles
v_sql :=
'SELECT DISTINCT grantee
FROM dba_tab_privs@'
|| p_remote_link
|| ' WHERE owner = '''
|| p_schema_name
|| ''' AND grantee NOT IN (''PUBLIC'') and table_name like nvl('''
|| p_table_name
|| ''',''%'') union SELECT '''
|| p_schema_name
|| ''' from dual union select distinct granted_role from dba_role_privs@'
|| p_remote_link
|| ' where grantee = '''
|| p_schema_name
|| '''';
-- Open up cursor to remote server
OPEN vrefcur_grantees FOR v_sql;
-- Loop throught grantees
LOOP
-- fetch grantee's from remote server
v_grantee := NULL; -- re-initialize
FETCH vrefcur_grantees INTO v_grantee;
-- Close cursor to remote server and exit loop when no data found
IF v_grantee IS NULL
THEN
CLOSE vrefcur_grantees;
EXIT; -- exit loop
END IF; -- end check for grantee
-- resolve missing user
v_return := fn_check_add_user(p_permission_object => v_grantee, p_remote_link => p_remote_link);
-- Check for error
IF v_return < 0
THEN
v_message := 'Effort to add ' || v_grantee || ' failed.';
raise_application_error(-20001, v_message);
END IF; -- end check for error
COMMIT;
END LOOP; -- end loop through grantees
RETURN 0; -- Success
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
DBMS_OUTPUT.put_line(SUBSTR('fn_get_rmt_user_n_role sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
RETURN v_sqlcode;
END fn_get_rmt_user_n_role; -- function fn_get_rmt_user_n_role
-----------------------
FUNCTION fn_check_add_user(p_permission_object IN VARCHAR2, -- user or role to check and possibly add
p_remote_link IN VARCHAR2)
RETURN NUMBER
IS
/*
Purpose: Makes sure user/role either exists or are added to ensure
import succeeds the way intended. Some permissions may not get applied if the user or role
does not exist.
1) Is user or Role
2) Does user or role exist in database
3) Does user or role exist in lookup table - if exists check permission to add
4) Add missing user or role when allowed - by permissions in lookup table
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/25/2013 Initial Build
*/
v_sqlcode NUMBER; -- sqlcode
-- write select statement
v_select VARCHAR2(32767)
:= ' select username, ''USER'' from dba_users@'
|| p_remote_link
|| '
where username = '''
|| p_permission_object
|| ''' union select role, ''ROLE'' from dba_roles@'
|| p_remote_link
|| '
where role = '''
|| p_permission_object
|| '''';
TYPE t_cur_permission_obj IS REF CURSOR; --Create type as ref cursor
vrefcur_get_type_perm t_cur_permission_obj; -- create reference cursor variable
v_permission_name VARCHAR2(30); -- Value for permission object return from select
v_permission_type VARCHAR2(30); -- "Role" or "User" return from select
-- Check for existance of a username
CURSOR c_get_user_ct(cv_username IN dba_users.username%TYPE)
IS
SELECT COUNT(*)
FROM dba_users
WHERE username = cv_username;
-- Check for existance of a role
CURSOR c_get_role_ct(cv_role IN dba_roles.role%TYPE)
IS
SELECT COUNT(*)
FROM dba_roles
WHERE role = cv_role;
v_object_exists INTEGER := 0; -- if it is not 0 then the object exists
-- cursor - Preferences for user or role creation
CURSOR c_local_perm_obj(cv_permission_name IN imp_user_role_cr_pref.permission_name%TYPE)
IS
SELECT *
FROM oradba.imp_user_role_cr_pref p
WHERE p.permission_name = cv_permission_name;
vrec_local_perm_obj c_local_perm_obj%ROWTYPE; -- Record Variable for local permission cursor
v_message VARCHAR2(32767); -- application error message
v_password VARCHAR2(200); -- random password generated
v_sql VARCHAR2(32767); -- sql to be executed
BEGIN
--dbms_output.put_line(substr('Value of v_select='||to_char(v_select),1,200));
--dbms_output.put_line(substr('Value of v_select='||to_char(v_select),201,200));
--- 1) Is User or Role
OPEN vrefcur_get_type_perm FOR v_select;
FETCH vrefcur_get_type_perm
INTO v_permission_name, v_permission_type;
CLOSE vrefcur_get_type_perm;
--dbms_output.put_line(substr('Value of v_permission_name='||to_char(v_permission_name),1,255));
--dbms_output.put_line(substr('Value of v_permission_type='||to_char(v_permission_type),1,255));
--- 2) Does user or role exist in database
IF v_permission_type = 'USER'
THEN
-- get count for the username
OPEN c_get_user_ct(v_permission_name);
FETCH c_get_user_ct INTO v_object_exists;
CLOSE c_get_user_ct;
ELSE
-- Get count for the role
OPEN c_get_role_ct(v_permission_name);
FETCH c_get_role_ct INTO v_object_exists;
CLOSE c_get_role_ct;
END IF; --end type of permission - check for existance
--- 3) Does user or role exist in lookup table - if exists then don't add, add when count = 0
-- is this object in the current object preference table
OPEN c_local_perm_obj(v_permission_name);
FETCH c_local_perm_obj INTO vrec_local_perm_obj;
CLOSE c_local_perm_obj;
-- Looking for same name, but different type
IF vrec_local_perm_obj.permission_name IS NOT NULL AND vrec_local_perm_obj.user_or_role != v_permission_type
THEN
v_message :=
'Permission object: '
|| vrec_local_perm_obj.permission_name
|| ' is a '
|| vrec_local_perm_obj.user_or_role
|| ' locally, but is a '
|| v_permission_type
|| ' remotely.';
raise_application_error(-20001, v_message);
END IF; -- end check for same name different roles
-- add to local table if not there
IF vrec_local_perm_obj.permission_name IS NULL
THEN
-- look at existing DB and add with pref Y if not existing, and N if it exists
IF v_object_exists = 0
THEN
-- Add to current priv preference table
v_sql :=
'INSERT INTO oradba.imp_user_role_cr_pref (PERMISSION_NAME,USER_OR_ROLE)
VALUES('''
|| v_permission_name
|| ''','''
|| v_permission_type
|| ''')';
ELSE
-- Add to current priv preference table - with preference = 'N' since it is already existing
v_sql :=
'INSERT INTO oradba.imp_user_role_cr_pref(permission_name, user_or_role, create_preference_yn)
VALUES ('''
|| v_permission_name
|| ''','''
|| v_permission_type
|| ''',''N'')';
END IF;
EXECUTE IMMEDIATE v_sql;
COMMIT;
-- Reread the permissions from the preference table (just added)
OPEN c_local_perm_obj(v_permission_name);
FETCH c_local_perm_obj INTO vrec_local_perm_obj;
CLOSE c_local_perm_obj;
END IF; -- end add to local table for permission names not already there
-- if does not exist in database then add
IF v_object_exists = 0
THEN
--- 4) Add missing user or role when allowed - by permissions in lookup table
-- if allowed to create then create
IF vrec_local_perm_obj.create_preference_yn = 'Y'
THEN
-- create permission in database
IF v_permission_type = 'USER'
THEN
-- Create user
v_password := DBMS_RANDOM.string('p', 30); -- Random password
v_password := REPLACE(v_password, '"', ''); -- Bounding by " seems to help with special characters, but then no " in passwd - remove them
-- write the statement
v_sql := 'create user ' || v_permission_name || ' identified by "' || v_password || '"';
-- execute stmt and create user
EXECUTE IMMEDIATE v_sql;
-- add permissions on default TBS (local default db)
v_sql := 'ALTER USER ' || v_permission_name || ' QUOTA UNLIMITED ON ' || vcg_default_tablespace;
EXECUTE IMMEDIATE v_sql;
-- Create role
ELSIF v_permission_type = 'ROLE'
THEN
-- add role
-- write the statement
v_sql := 'create role ' || v_permission_name;
-- execute stmt and create role
EXECUTE IMMEDIATE v_sql;
ELSE
NULL;
END IF; -- end of creation for user and role
END IF; -- if allowed to create then create
END IF; -- end for check if objects exists in current db (step 3)
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
DBMS_OUTPUT.put_line(SUBSTR('vn_check_add_user Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
RETURN v_sqlcode;
END fn_check_add_user;
------------------------
FUNCTION fn_remap_tablespaces(p_remote_tablespace IN VARCHAR2, -- The "FROM" tablespace at the remote database
p_bool_always_remap IN BOOLEAN DEFAULT FALSE, -- always remap and don't use the local name if it exists, but still use default if not found on remap
p_local_tablespace IN OUT VARCHAR2 -- The "TO" tablespace here on the local database
)
RETURN NUMBER
IS
/*
Purpose: Does a lookup to determine a cross reference for tablespace changes
Uses the default tablespace name when no match is found.
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/21/2013 Initial Build
*/
-- Lookup remap value
CURSOR c_get_tablespace_remaps(cv_remote_tablespace IN oradba.remap_tablespace.remote_tablespace%TYPE)
IS
SELECT a.local_tablespace to_tbs
FROM oradba.remap_tablespace a
WHERE remote_tablespace = cv_remote_tablespace;
-- Check for local match
CURSOR c_get_existing_tablespaces(cv_remote_tablespace IN oradba.remap_tablespace.remote_tablespace%TYPE)
IS
SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name = cv_remote_tablespace;
v_line_no INTEGER := 0; -- debug line no
v_sqlcode NUMBER; -- sqlcode error
BEGIN
v_line_no := 100; -- debug line no
-- look up local tablespace - skip if this is true and use remap values instead
IF NVL(p_bool_always_remap, FALSE) = FALSE
THEN
v_line_no := 200; -- debug line no
OPEN c_get_existing_tablespaces(p_remote_tablespace);
FETCH c_get_existing_tablespaces INTO p_local_tablespace;
CLOSE c_get_existing_tablespaces;
END IF;
-- When not found then look for a remap value
IF p_local_tablespace IS NULL
THEN
-- Look up a match for the local tablespace
OPEN c_get_tablespace_remaps(p_remote_tablespace);
FETCH c_get_tablespace_remaps INTO p_local_tablespace;
CLOSE c_get_tablespace_remaps;
END IF;
-- set to default if still null
IF p_local_tablespace IS NULL
THEN
p_local_tablespace := vcg_default_tablespace;
END IF;
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
RETURN v_sqlcode;
END fn_remap_tablespaces; -- Function fn_remap_tablespaces
-------------------------
FUNCTION fn_tbs_remaps_for_link(p_remote_link IN VARCHAR2, -- remote link - database data to be imported via link
p_handle IN NUMBER, -- handle opened for import
p_schema_name IN VARCHAR2, -- schema to be imported
p_table_name IN VARCHAR2 DEFAULT NULL -- table or object to be imported
)
RETURN NUMBER
IS
/*
Purpose: This function will add the remap lines for tablespaces associated
with a given schema to be loaded over a database link
For example, if the remote tablespace is called DATA, and the only
tablespace locally is USERS. Users can be defined as the default tablespace.
OR
Multiple tablespaces can be mapped in the local table on how the tablespaces should be remapped
(table name: REMAP_TABLESPACE)
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/21/2013 Initial Build
*/
TYPE t_cur_tablespaces IS REF CURSOR; --Create type as ref cursor
vrefcur_tablespace t_cur_tablespaces; -- create reference cursor variable
v_remote_tablespace oradba.remap_tablespace.remote_tablespace%TYPE; -- remote tablespace
v_local_tablespace oradba.remap_tablespace.local_tablespace%TYPE; -- local tablespace
v_select VARCHAR2(32767);
v_return NUMBER; -- return value from function
v_message VARCHAR2(32767); -- error message
v_sqlcode NUMBER; -- sqlcode error
BEGIN
-- write select statement
v_select := 'select distinct tablespace_name
from dba_segments@' || p_remote_link || '
where owner = ''' || p_schema_name || '''';
-- Loop through tablespaces and write remap statements
IF p_table_name IS NOT NULL
THEN
v_select := v_select || ' and segment_name = (''' || p_table_name || ''',''%'')';
END IF;
-- open ref cursor
OPEN vrefcur_tablespace FOR v_select;
LOOP
FETCH vrefcur_tablespace INTO v_remote_tablespace;
EXIT WHEN vrefcur_tablespace%NOTFOUND;
-- Get remap value
v_return :=
fn_remap_tablespaces(p_remote_tablespace => v_remote_tablespace, p_bool_always_remap => FALSE, p_local_tablespace => v_local_tablespace);
IF v_return < 0
THEN
v_message := 'Failure in fn_remap_tablespaces, cannot remap remote tablespace:' || v_remote_tablespace;
raise_application_error(-20001, v_message);
END IF;
DBMS_DATAPUMP.metadata_remap(p_handle,
'REMAP_TABLESPACE',
v_remote_tablespace,
v_local_tablespace);
END LOOP;
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
RETURN v_sqlcode;
END fn_tbs_remaps_for_link; --function fn_tbs_remaps_for_link
-------------------------------
FUNCTION fn_logging(p_code IN oradba.dp_logging.code_returned%TYPE, -- 0 - Success, 1 - General information , < 0 error codes
p_message_text IN oradba.dp_logging.MESSAGE_TEXT%TYPE, -- message text
p_object_schema IN oradba.dp_logging.object_schema%TYPE, -- Object schema
p_object_name IN oradba.dp_logging.object_name%TYPE, -- Object name
p_line_no IN INTEGER, -- line number where error occurred
p_job_id IN oradba.dp_logging.job_id%TYPE DEFAULT NULL, -- Job ID
p_task_id IN oradba.dp_logging.task_id%TYPE DEFAULT NULL, -- Task ID
p_skip_commit_yn IN VARCHAR2 DEFAULT 'N' -- If for some reason the commit needs to be skipped here in this function then this should be 'Y'
)
RETURN NUMBER
IS
/*
Purpose: Log an error, success or informational message
Note
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/4/2013 Initial Build
*/
v_sql VARCHAR2(4000) := 'INSERT INTO oradba.dp_logging(code_returned,
MESSAGE_TEXT,
object_schema,
object_name,
line_no,
job_id,
task_id)
VALUES (:code_returned,
:MESSAGE_TEXT,
:object_schema,
:object_name,
:line_no,
:job_id,
:task_id)'; -- sql text to write to log table
v_sqlcode NUMBER; -- error code
BEGIN
EXECUTE IMMEDIATE v_sql
USING p_code, -- code_returned
p_message_text, -- MESSAGE_TEXT
p_object_schema, -- object_schema
p_object_name, -- object_name
NVL(p_line_no, 0), -- line no
p_job_id, -- job_id
p_task_id -- task_id
;
-- commit to table
IF p_skip_commit_yn IS NULL OR p_skip_commit_yn != 'Y'
THEN
COMMIT;
END IF;
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
ROLLBACK;
RETURN v_sqlcode;
END fn_logging; -- function fn_logging
------------------------
FUNCTION fn_clean_stopped_job(p_job_owner IN VARCHAR2, -- job owner
p_job_name IN VARCHAR2 -- job name
)
RETURN NUMBER
IS
/*
Purpose: Clean stopped jobs
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/5/2013 Initial Build
*/
-- datapump jobs
CURSOR c_datapump_jobs(cv_owner_name IN dba_datapump_jobs.owner_name%TYPE, cv_job_name IN dba_datapump_jobs.job_name%TYPE)
IS
SELECT *
FROM dba_datapump_jobs a
WHERE owner_name = cv_owner_name AND job_name = cv_job_name AND state NOT IN ('EXECUTING') AND state IN ('NOT RUNNING');
vrec_dp_jobs c_datapump_jobs%ROWTYPE; -- record for datapump jobs result
v_sql VARCHAR2(32767); -- sql to be executed
BEGIN
-- check for the value and don't delete and purge if it is running
OPEN c_datapump_jobs(p_job_owner, p_job_name);
FETCH c_datapump_jobs INTO vrec_dp_jobs;
CLOSE c_datapump_jobs;
-- verifying that it is not executing by using the record returned
IF vrec_dp_jobs.job_name IS NOT NULL
THEN
v_sql := 'Delete TABLE ' || vrec_dp_jobs.owner_name || '.' || vrec_dp_jobs.job_name;
EXECUTE IMMEDIATE v_sql;
v_sql := 'PURGE TABLE ' || vrec_dp_jobs.owner_name || '.' || vrec_dp_jobs.job_name;
EXECUTE IMMEDIATE v_sql;
END IF;
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
RETURN SQLCODE;
END fn_clean_stopped_job; -- function fn_clean_stopped_job
---------------------
FUNCTION fn_create_permissions_script(p_remote_link IN VARCHAR2) -- remote link to remote database
RETURN NUMBER
IS
/*
Purpose: Create a permissions script that can be executed on a remote db to pull over user grants, roles, profiles, verify_function's, etc
Directions: Run this script, get the script generated and run it on the target system, get the output and then run it here on this system to apply
the correct permissions, etc.
This is just like fn_create_userperm_script, but for a list of users from the table that are allowed to be created.
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 2/27/2013 Initial Build
*/
-- Get any needed "verify_functions"
CURSOR c_get_verify_function
IS
SELECT 'select DBMS_METADATA.get_ddl(''FUNCTION'', object_name, owner) ddl from dual; ' ddl
FROM dba_objects
WHERE owner = 'SYS'
AND object_name IN (SELECT LIMIT
FROM dba_profiles
WHERE profile != 'DEFAULT'
AND resource_name = 'PASSWORD_VERIFY_FUNCTION'
AND profile IN (SELECT profile
FROM dba_users
WHERE username IN (SELECT permission_name
FROM oradba.imp_user_role_cr_pref p
WHERE p.create_preference_yn = 'Y')));
-- Create any needed Profiles
CURSOR c_get_profiles
IS
SELECT 'select DBMS_METADATA.get_ddl(''PROFILE'', ''' || profile || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (SELECT permission_name
FROM oradba.imp_user_role_cr_pref p
WHERE p.create_preference_yn = 'Y')
AND profile NOT IN (SELECT profile FROM dba_profiles);
-- Get the basic user ddl
CURSOR c_user_basic_info
IS
SELECT 'select DBMS_METADATA.get_ddl(''USER'', ''' || username || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (SELECT permission_name
FROM oradba.imp_user_role_cr_pref p
WHERE p.create_preference_yn = 'Y');
-- Get tablespace quota
CURSOR c_tablespace_quota
IS
SELECT 'select DBMS_METADATA.get_granted_ddl(''TABLESPACE_QUOTA'', '''
|| username
|| ''') ddl from dual'
|| ' where exists (select username '
|| ' from dba_ts_quotas where max_bytes != -1 and username = '''
|| username
|| '''); '
ddl
FROM dba_users
WHERE username IN (SELECT permission_name
FROM oradba.imp_user_role_cr_pref p
WHERE p.create_preference_yn = 'Y');
-- Get role grants for users
CURSOR c_get_role_grants
IS
SELECT 'select DBMS_METADATA.get_granted_ddl(''ROLE_GRANT'', ''' || username || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (SELECT permission_name
FROM oradba.imp_user_role_cr_pref p
WHERE p.create_preference_yn = 'Y');
-- Get system grants
CURSOR c_get_system_grants
IS
SELECT 'select DBMS_METADATA.get_granted_ddl(''SYSTEM_GRANT'', ''' || username || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (SELECT permission_name
FROM oradba.imp_user_role_cr_pref p
WHERE p.create_preference_yn = 'Y');
-- Get Objects grants
CURSOR c_get_object_grants
IS
SELECT 'select DBMS_METADATA.get_granted_ddl(''OBJECT_GRANT'', ''' || username || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (SELECT permission_name
FROM oradba.imp_user_role_cr_pref p
WHERE p.create_preference_yn = 'Y');
-- Get directory path
CURSOR c_get_dir_path(cv_dir_name IN dba_directories.directory_name%TYPE)
IS
SELECT directory_path
FROM dba_directories d
WHERE directory_name = cv_dir_name;
v_file_handle UTL_FILE.file_type; -- file handle
v_directory VARCHAR2(32767) := 'DATA_PUMP_DIR'; -- directory name
v_file_name VARCHAR2(32767) := 'metadata_script_' || p_remote_link || '_' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI'); -- filename
v_buffer VARCHAR2(32767); -- buffere to write to file
v_sqlcode NUMBER; -- sqlcode for error
vc_comment_chars CONSTANT VARCHAR2(10) := '-- '; -- comment characters
vc_local_fn CONSTANT VARCHAR2(30) := 'fn_create_profile_script'; -- local function name
v_line_no INTEGER := 0; -- debug line number
v_dump_dir_path VARCHAR2(32767); -- spool directory path
BEGIN
v_line_no := 100; -- debug line number
-- open up file to write
v_file_handle := UTL_FILE.fopen(location => v_directory, filename => v_file_name || '.sql', open_mode => 'w');
-- start creating scripts and writing to buffer
-- Compose header in comments
-- Basic title
-- Creation Date Time
-- Created with xx procedure
v_buffer :=
vc_comment_chars
|| ' Metadata script to pull data for users that will be (or have been) imported into this local database from '
|| p_remote_link
|| UTL_TCP.crlf;
v_buffer :=
v_buffer || vc_comment_chars || ' Creation started at: ' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI_SS') || SESSIONTIMEZONE || UTL_TCP.crlf;
v_buffer := v_buffer || vc_comment_chars || ' Created using: ' || $$plsql_unit || '.' || vc_local_fn || UTL_TCP.crlf;
-- compose basic sql parameters
v_buffer := v_buffer || 'Set HEAD OFF' || UTL_TCP.crlf;
v_buffer := v_buffer || 'Set ECHO OFF' || UTL_TCP.crlf;
v_buffer := v_buffer || 'Set long 1000000' || UTL_TCP.crlf; -- watch memory on this one
v_buffer := v_buffer || 'Set PAGES 0' || UTL_TCP.crlf;
v_buffer := v_buffer || 'Set lines 160' || UTL_TCP.crlf;
v_buffer := v_buffer || 'Set feedback off' || UTL_TCP.crlf;
-- compose error handling for sqlplus
v_buffer := v_buffer || 'WHENEVER SQLERROR CONTINUE ' || UTL_TCP.crlf; -- may want a different termination task here?? SQL.SQLCODE | WARNING | CONTINUE | Exit Error ??
-- get directory
OPEN c_get_dir_path(v_directory);
FETCH c_get_dir_path INTO v_dump_dir_path;
CLOSE c_get_dir_path;
-- compose spool log file for execution
v_buffer := v_buffer || 'spool ' || v_dump_dir_path || v_file_name || '_output.sql' || UTL_TCP.crlf; -- might want to send this to a ./output subdirectory or a complete file path
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
-- Main script body start
-- spool to output for final execution file
v_buffer := v_buffer || 'select ''spool permission_add.output;'' from dual;' || UTL_TCP.crlf;
-- Make the data nicer to execute in sqlplus --- Adds in the ';' terminator
v_buffer := v_buffer || 'begin
DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''SQLTERMINATOR'', TRUE);
DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''PRETTY'', TRUE);
end;
/';
v_line_no := 500; -- debug line number
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 550; -- debug line number
-- Get verify function(s)
FOR nverify_fns IN c_get_verify_function
LOOP
v_buffer := v_buffer || nverify_fns.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 600; -- debug line number
-- Get Profile(s)
FOR nprofiles IN c_get_profiles
LOOP
v_buffer := v_buffer || nprofiles.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
-- Get basic user(s) info
FOR nusers IN c_user_basic_info
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 700; -- debug line number
-- Get tablespace Quota(s)
FOR nusers IN c_tablespace_quota
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 800; -- debug line number
-- Get role grant(s)
FOR nusers IN c_get_role_grants
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
-- Get System Grants
FOR nusers IN c_get_system_grants
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 900; -- debug line number
-- Get Object Grants
FOR nusers IN c_get_object_grants
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 1000; -- debug line number
-- main script body end
-- composition close comments
-- close title
-- close date time
v_buffer := v_buffer || vc_comment_chars || ' Metadata scrip generation completed.' || UTL_TCP.crlf;
v_buffer := v_buffer || vc_comment_chars || ' Completed at: ' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI_SS') || SESSIONTIMEZONE || UTL_TCP.crlf;
v_buffer := v_buffer || vc_comment_chars || ' Created using: ' || $$plsql_unit || '.' || vc_local_fn || UTL_TCP.crlf;
-- stop spool to output for final execution file
v_buffer := v_buffer || 'select ''spool off;'' from dual;' || UTL_TCP.crlf;
-- Closeout parameters
v_buffer := v_buffer || 'spool off;' || UTL_TCP.crlf;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 1500; -- debug line number
UTL_FILE.fclose(v_file_handle);
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
BEGIN
UTL_FILE.fclose(v_file_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
RETURN v_sqlcode;
END fn_create_permissions_script; -- function fn_create_permissions_script
--------------------
FUNCTION fn_create_userperm_script(p_username IN VARCHAR2, -- create script for specific user
p_remote_link IN VARCHAR2 -- remote link to remote database
)
RETURN NUMBER
IS
/*
Purpose: Create a permissions script that can be executed on a remote db to pull over user grants, roles, profiles, verify_function's, etc for a specific user
Directions: Run this script, get the script generated and run it on the target system, get the output and then run it here on this system to apply
the correct permissions, etc.
This is just like fn_create_permissions_script, but for a single user
MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 3/9/2013 Initial Build
*/
-- Get any needed "verify_functions"
CURSOR c_get_verify_function
IS
SELECT 'select DBMS_METADATA.get_ddl(''FUNCTION'', object_name, owner) ddl from dual; ' ddl
FROM dba_objects
WHERE owner = 'SYS'
AND object_name IN (SELECT LIMIT
FROM dba_profiles
WHERE profile != 'DEFAULT'
AND resource_name = 'PASSWORD_VERIFY_FUNCTION'
AND profile IN (SELECT profile
FROM dba_users
WHERE username IN (p_username)));
-- Create any needed Profiles
CURSOR c_get_profiles
IS
SELECT 'select DBMS_METADATA.get_ddl(''PROFILE'', ''' || profile || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (p_username) AND profile NOT IN (SELECT profile FROM dba_profiles);
-- Get the basic user ddl
CURSOR c_user_basic_info
IS
SELECT 'select DBMS_METADATA.get_ddl(''USER'', ''' || username || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (p_username);
-- Get tablespace quota
CURSOR c_tablespace_quota
IS
SELECT 'select DBMS_METADATA.get_granted_ddl(''TABLESPACE_QUOTA'', '''
|| username
|| ''') ddl from dual'
|| ' where exists (select username '
|| ' from dba_ts_quotas where max_bytes != -1 and username = '''
|| username
|| '''); '
ddl
FROM dba_users
WHERE username IN (p_username);
-- Get role grants for users
CURSOR c_get_role_grants
IS
SELECT 'select DBMS_METADATA.get_granted_ddl(''ROLE_GRANT'', ''' || username || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (p_username);
-- Get system grants
CURSOR c_get_system_grants
IS
SELECT 'select DBMS_METADATA.get_granted_ddl(''SYSTEM_GRANT'', ''' || username || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (p_username);
-- Get Objects grants
CURSOR c_get_object_grants
IS
SELECT 'select DBMS_METADATA.get_granted_ddl(''OBJECT_GRANT'', ''' || username || ''') ddl from dual;' ddl
FROM dba_users
WHERE username IN (p_username);
-- Get directory path
CURSOR c_get_dir_path(cv_dir_name IN dba_directories.directory_name%TYPE)
IS
SELECT directory_path
FROM dba_directories d
WHERE directory_name = cv_dir_name;
v_file_handle UTL_FILE.file_type; -- file handle
v_directory VARCHAR2(32767) := 'DATA_PUMP_DIR'; -- directory name
v_file_name VARCHAR2(32767) := 'metadata_script_' || p_remote_link || '_' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI'); -- filename
v_buffer VARCHAR2(32767); -- buffere to write to file
v_sqlcode NUMBER; -- sqlcode for error
vc_comment_chars CONSTANT VARCHAR2(10) := '-- '; -- comment characters
vc_local_fn CONSTANT VARCHAR2(30) := 'fn_create_profile_script'; -- local function name
v_line_no INTEGER := 0; -- debug line number
v_dump_dir_path VARCHAR2(32767); -- spool directory path
BEGIN
v_line_no := 100; -- debug line number
-- open up file to write
v_file_handle := UTL_FILE.fopen(location => v_directory, filename => v_file_name || '.sql', open_mode => 'w');
-- start creating scripts and writing to buffer
-- Compose header in comments
-- Basic title
-- Creation Date Time
-- Created with xx procedure
v_buffer :=
vc_comment_chars
|| ' Metadata script to pull data for users that will be (or have been) imported into this local database from '
|| p_remote_link
|| UTL_TCP.crlf;
v_buffer :=
v_buffer || vc_comment_chars || ' Creation started at: ' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI_SS') || SESSIONTIMEZONE || UTL_TCP.crlf;
v_buffer := v_buffer || vc_comment_chars || ' Created using: ' || $$plsql_unit || '.' || vc_local_fn || UTL_TCP.crlf;
-- compose basic sql parameters
v_buffer := v_buffer || 'Set HEAD OFF' || UTL_TCP.crlf;
v_buffer := v_buffer || 'Set ECHO OFF' || UTL_TCP.crlf;
v_buffer := v_buffer || 'Set long 1000000' || UTL_TCP.crlf; -- watch memory on this one
v_buffer := v_buffer || 'Set PAGES 0' || UTL_TCP.crlf;
v_buffer := v_buffer || 'Set lines 160' || UTL_TCP.crlf;
v_buffer := v_buffer || 'Set feedback off' || UTL_TCP.crlf;
-- compose error handling for sqlplus
v_buffer := v_buffer || 'WHENEVER SQLERROR CONTINUE ' || UTL_TCP.crlf; -- may want a different termination task here?? SQL.SQLCODE | WARNING | CONTINUE | Exit Error ??
-- get directory
OPEN c_get_dir_path(v_directory);
FETCH c_get_dir_path INTO v_dump_dir_path;
CLOSE c_get_dir_path;
-- compose spool log file for execution
v_buffer := v_buffer || 'spool ' || v_dump_dir_path || v_file_name || '_output.sql' || UTL_TCP.crlf; -- might want to send this to a ./output subdirectory or a complete file path
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
-- Main script body start
-- spool to output for final execution file
v_buffer := v_buffer || 'select ''spool permission_add.output;'' from dual;' || UTL_TCP.crlf;
-- Make the data nicer to execute in sqlplus --- Adds in the ';' terminator
v_buffer := v_buffer || 'begin
DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''SQLTERMINATOR'', TRUE);
DBMS_METADATA.set_transform_param(DBMS_METADATA.session_transform, ''PRETTY'', TRUE);
end;
/';
v_line_no := 500; -- debug line number
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 550; -- debug line number
-- Get verify function(s)
FOR nverify_fns IN c_get_verify_function
LOOP
v_buffer := v_buffer || nverify_fns.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 600; -- debug line number
-- Get Profile(s)
FOR nprofiles IN c_get_profiles
LOOP
v_buffer := v_buffer || nprofiles.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
-- Get basic user(s) info
FOR nusers IN c_user_basic_info
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 700; -- debug line number
-- Get tablespace Quota(s)
FOR nusers IN c_tablespace_quota
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 800; -- debug line number
-- Get role grant(s)
FOR nusers IN c_get_role_grants
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
-- Get System Grants
FOR nusers IN c_get_system_grants
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 900; -- debug line number
-- Get Object Grants
FOR nusers IN c_get_object_grants
LOOP
v_buffer := v_buffer || nusers.ddl || UTL_TCP.crlf;
END LOOP;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 1000; -- debug line number
-- main script body end
-- composition close comments
-- close title
-- close date time
v_buffer := v_buffer || vc_comment_chars || ' Metadata scrip generation completed.' || UTL_TCP.crlf;
v_buffer := v_buffer || vc_comment_chars || ' Completed at: ' || TO_CHAR(SYSDATE, 'YYYY_MMDD_HH24MI_SS') || SESSIONTIMEZONE || UTL_TCP.crlf;
v_buffer := v_buffer || vc_comment_chars || ' Created using: ' || $$plsql_unit || '.' || vc_local_fn || UTL_TCP.crlf;
-- stop spool to output for final execution file
v_buffer := v_buffer || 'select ''spool off;'' from dual;' || UTL_TCP.crlf;
-- Closeout parameters
v_buffer := v_buffer || 'spool off;' || UTL_TCP.crlf;
-- Write to buffer
UTL_FILE.put_line(file => v_file_handle, buffer => v_buffer, autoflush => TRUE);
v_buffer := NULL;
v_line_no := 1500; -- debug line number
UTL_FILE.fclose(v_file_handle);
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
v_sqlcode := SQLCODE;
DBMS_OUTPUT.put_line(SUBSTR('Value of v_line_no=' || TO_CHAR(v_line_no), 1, 255));
DBMS_OUTPUT.put_line(SUBSTR('Value of sqlerrm(v_sqlcode)=' || TO_CHAR(SQLERRM(v_sqlcode)), 1, 255));
BEGIN
UTL_FILE.fclose(v_file_handle);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
RETURN v_sqlcode;
END fn_create_userperm_script; -- function fn_create_userperm_script
END pkg_dp_util; -- package body pkg_dp_util
/
I encourage you to leave comments if this is interesting, and I’ll add to it if there is any interest.
Like this:
Like Loading...
