In the previous blog, I discussed about how to extract the create user syntax along with all system priviliges, granted roles and tablespace grants.In this post I will be talking about extracting just the roles from the source database.
Here is how I just extract the roles out
This command can be run locally on the DB server to just extract the roles
expdp \'/ as sysdba\' full=y INCLUDE=role dumpfile=role.dmp nologfile=yes
A small variation of the above command, which can be run from the target database.
For it to work,I usually create a database link from the target to the source database .I usually connect to system user.
expdp \'/ as sysdba\' NETWORK_LINK=<source db link name> full=y INCLUDE=role dumpfile=role.dmp nologfile=yes
The below command extracts the role defnition into a file called role.sql.
impdp \'/ as sysdba\' dumpfile=role.dmp sqlfile=role.sql nologfile=yes
Please use your favourite text editor to open up and see contents of role.sql.
Monday, February 28, 2011
Thursday, February 24, 2011
Magic of Oracle Datapump Technology 1 -Schema Creation
Started using Datapump when I was asked to write a faster refresh method for a reporting database which was being refreshed every night using an old conventional export/import.
I learnt a lot and loved the way it gave more control over what to choose and to ignore.
Over the years this has become my tool of choice to refresh databases where RMAN is not an option.
Here is a technique I frequently use to refresh the whole database .For some reason I always want to avoid doing a full database export and prefer to just pick the schemas and roles .
For this reason, I always pre-create the empty schemas and roles on the target database(database into which I perform the import).This method is preferable especially if you have multiple schemas with cross grants to each other.
Example is say you have 2 schemas scott and test and both of them have granted selected privs on their own tables to the other schema.
(select on scott.t1 to test and select on test.t1 to scott)
I assume that the user of this blog has basic knowledge of setting up the env variables like DATA_PUMP_DIR before using the expdp/impdp utilities.
1.Precreate the schema with all the system priviliges, role grants and tablespace quotas
At the source database the below command is run to export the user creation for 2 schemas namely- scott and test.
expdp \'/ as sysdba\' schemas=scott,test dumpfile=schema_create.dmp include=USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA
logfile=expdp_schema_create.log
At the target database, just import the above dumpfile using the corresponding impdp command
impdp \'/ as sysdba\' dumpfile=schema_create.dmp nologfile=yes
The nologfile option just does not create any logfile.in the above case , i choose not to create any logfile.
Sometimes just to verify what went into the dumpfile, i love to use the sqlfile option in impdp.
impdp \'/ as sysdba\' dumpfile=schema_create.dmp sqlfile=schema_create.sql nologfile=yes
The above command does no database operation but just faithfully logs what went into the dump file into a sqlfile.The above file can be directly run on the database by logging in as a DBA user into sql plus and run as
@ schema_create.sql.
****
WORKING EXAMPLE
*******
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> create directory testdir as '/tmp';
Directory created.
SQL> create user scott identified by tiger;
User created.
SQL> grant create session,create table to scott;
Grant succeeded.
SQL> create role r1;
Role created.
SQL> grant r1 to scott;
Grant succeeded.
SQL> alter user scott default tablespace users temporary tablespace temp quota unlimited on users;
User altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
homelinux<oracle>/home/oracle> cd /tmp
homelinux<oracle>/tmp>
homelinux<oracle>/tmp> export DATA_PUMP_DIR="TESTDIR"
homelinux<oracle>/tmp>expdp \'/ as sysdba\' schemas=scott dumpfile=schema_create.dmp include=USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA
logfile=expdp_schema_create.log
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 24 February, 2011 10:29:25
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": '/******** AS SYSDBA' schemas=scott dumpfile=schema_create.dmp include=USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/tmp/schema_create.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:32
homelinux<oracle>/tmp> impdp \'/ as sysdba\' dumpfile=schema_create.dmp sqlfile=abc.sql nologfile=yes
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 24 February, 2011 10:31:07
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": '/******** AS SYSDBA' dumpfile=schema_create.dmp sqlfile=abc.sql nologfile=yes
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 10:31:10
homelinux<oracle>/tmp> cat abc.sql
-- CONNECT SYS
-- new object type path is: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT CREATE TABLE TO "SCOTT";
GRANT CREATE SESSION TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
GRANT "R1" TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "SCOTT" DEFAULT ROLE ALL;
-- new object type path is: SCHEMA_EXPORT/TABLESPACE_QUOTA
DECLARE
TEMP_COUNT NUMBER;
SQLSTR VARCHAR2(200);
BEGIN
SQLSTR := 'ALTER USER "SCOTT" QUOTA UNLIMITED ON "USERS"';
EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -30041 THEN
SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
WHERE TABLESPACE_NAME = ''USERS'' AND CONTENTS = ''TEMPORARY''';
EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
IF TEMP_COUNT = 1 THEN RETURN;
ELSE RAISE;
END IF;
ELSE
RAISE;
END IF;
END;
/
homelinux<oracle>/tmp>
I learnt a lot and loved the way it gave more control over what to choose and to ignore.
Over the years this has become my tool of choice to refresh databases where RMAN is not an option.
Here is a technique I frequently use to refresh the whole database .For some reason I always want to avoid doing a full database export and prefer to just pick the schemas and roles .
For this reason, I always pre-create the empty schemas and roles on the target database(database into which I perform the import).This method is preferable especially if you have multiple schemas with cross grants to each other.
Example is say you have 2 schemas scott and test and both of them have granted selected privs on their own tables to the other schema.
(select on scott.t1 to test and select on test.t1 to scott)
I assume that the user of this blog has basic knowledge of setting up the env variables like DATA_PUMP_DIR before using the expdp/impdp utilities.
1.Precreate the schema with all the system priviliges, role grants and tablespace quotas
At the source database the below command is run to export the user creation for 2 schemas namely- scott and test.
expdp \'/ as sysdba\' schemas=scott,test dumpfile=schema_create.dmp include=USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA
logfile=expdp_schema_create.log
At the target database, just import the above dumpfile using the corresponding impdp command
impdp \'/ as sysdba\' dumpfile=schema_create.dmp nologfile=yes
The nologfile option just does not create any logfile.in the above case , i choose not to create any logfile.
Sometimes just to verify what went into the dumpfile, i love to use the sqlfile option in impdp.
impdp \'/ as sysdba\' dumpfile=schema_create.dmp sqlfile=schema_create.sql nologfile=yes
The above command does no database operation but just faithfully logs what went into the dump file into a sqlfile.The above file can be directly run on the database by logging in as a DBA user into sql plus and run as
@ schema_create.sql.
****
WORKING EXAMPLE
*******
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> create directory testdir as '/tmp';
Directory created.
SQL> create user scott identified by tiger;
User created.
SQL> grant create session,create table to scott;
Grant succeeded.
SQL> create role r1;
Role created.
SQL> grant r1 to scott;
Grant succeeded.
SQL> alter user scott default tablespace users temporary tablespace temp quota unlimited on users;
User altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
homelinux<oracle>/home/oracle> cd /tmp
homelinux<oracle>/tmp>
homelinux<oracle>/tmp> export DATA_PUMP_DIR="TESTDIR"
homelinux<oracle>/tmp>expdp \'/ as sysdba\' schemas=scott dumpfile=schema_create.dmp include=USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA
logfile=expdp_schema_create.log
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 24 February, 2011 10:29:25
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": '/******** AS SYSDBA' schemas=scott dumpfile=schema_create.dmp include=USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/tmp/schema_create.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:32
homelinux<oracle>/tmp> impdp \'/ as sysdba\' dumpfile=schema_create.dmp sqlfile=abc.sql nologfile=yes
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 24 February, 2011 10:31:07
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": '/******** AS SYSDBA' dumpfile=schema_create.dmp sqlfile=abc.sql nologfile=yes
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 10:31:10
homelinux<oracle>/tmp> cat abc.sql
-- CONNECT SYS
-- new object type path is: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT CREATE TABLE TO "SCOTT";
GRANT CREATE SESSION TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
GRANT "R1" TO "SCOTT";
-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE
ALTER USER "SCOTT" DEFAULT ROLE ALL;
-- new object type path is: SCHEMA_EXPORT/TABLESPACE_QUOTA
DECLARE
TEMP_COUNT NUMBER;
SQLSTR VARCHAR2(200);
BEGIN
SQLSTR := 'ALTER USER "SCOTT" QUOTA UNLIMITED ON "USERS"';
EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -30041 THEN
SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
WHERE TABLESPACE_NAME = ''USERS'' AND CONTENTS = ''TEMPORARY''';
EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
IF TEMP_COUNT = 1 THEN RETURN;
ELSE RAISE;
END IF;
ELSE
RAISE;
END IF;
END;
/
homelinux<oracle>/tmp>
Subscribe to:
Posts (Atom)