oracle 11g expdp by profile

is it possible on oracle 11g to export users by the profile they are assigned to?
LVL 1
bkreynolds48Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Yes.

The setup:
Create a profile called MYPROF.
Create two users in that profile with a table each.

DROP PROFILE myprof cascade;
CREATE PROFILE myprof LIMIT SESSIONS_PER_USER UNLIMITED;

drop user fred cascade;
create user fred identified by fred profile myprof quota unlimited on users;

create table fred.tab1(col1 char(1));
insert into fred.tab1 values('a');
commit;

drop user barney cascade;
create user barney identified by barney profile myprof quota unlimited on users;

create table barney.tab1(col1 char(1));
insert into barney.tab1 values('b');
commit;

Open in new window


The "magic":
expdp user/password full=y include=schema:\"in (select username from dba_users where profile='MYPROF')\"

Open in new window


Note: I'm on Windows and had to escape the double quotes on the command line.
bkreynolds48Author Commented:
I get a syntax error
ksh o403-057 Syntax errr: `(' is not expected
I just added the userid and password and the name of the profile
slightwv (䄆 Netminder) Commented:
What OS?
Post your command.

You might need to escape the parans as well.

Maybe use a PARFILE.  There are many examples of this on the web and in the docs.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bkreynolds48Author Commented:
aix 6.1
bkreynolds48Author Commented:
I put it in a par file but get the error
UDE-00014: invalid value for parameter, 'include'
slightwv (䄆 Netminder) Commented:
In the parfile you don't need to escape the quotes.

That is just because the OS tried to interpret the quotes before they were passed off to expdp.

>>aix 6.1

If you want to use the command line, escape the parans?  I'm not on Unix right now so cannot try different options to see what special characters need escaping.

try this:
expdp user/password full=y include=schema:\"in \(select username from dba_users where profile='MYPROF'\)\"
bkreynolds48Author Commented:
slightwv
I get
ora 39001: invalid argument value
ora 39071: value for INCLUDE is badly formed
ora 00904: 'MYPROF': invalid identifier
slightwv (䄆 Netminder) Commented:
What all did you try escaping?

I now have access to a Linux box and was able to run it with the example below.  I cannot say if it will also work on AIX.

expdp user/password full=y include=schema:\"in \(select username from dba_users where profile=\'MYPROF\'\)\"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bkreynolds48Author Commented:
this is what I ran

expdp  userid/pwd full=y include=schema:\"in \(select username from dba_users where profile='USR_PROF'\)\"
bkreynolds48Author Commented:
ok this ran
expdp user/password full=y include=schema:\"in \(select username from dba_users where profile=\'MYPROF\'\)\"

HOWEVER - it only exports the user you use for userid/pwd
slightwv (䄆 Netminder) Commented:
When I executed it on my test machine it exported the FRED and BARNEY objects.

>>ok this ran

Did you change MYPROF to the correct profile name?  Double check the profile you are using and which users are in it.
bkreynolds48Author Commented:
there are 145 users with the profile I used
slightwv (䄆 Netminder) Commented:
When you change the profile name, are you entering it in upper case?

I just retested it and even modified the test so barney didn't own any objects.  Just a grant from fred.

The export file had everything in it.

How are you verifying the export file doesn't have what you need?

I used:
impdp user/pass full=y sqlfile=mytest.sql

Then edit mytest.sql to see what was in the export.

Can you run my test successfully?

Remember, my posted expdp is escaped for Windows.
DROP PROFILE myprof cascade;
CREATE PROFILE myprof LIMIT SESSIONS_PER_USER UNLIMITED;

drop user fred cascade;
create user fred identified by fred profile myprof quota unlimited on users;

create table fred.tab1(col1 char(1));
insert into fred.tab1 values('a');
commit;

drop user barney cascade;
create user barney identified by barney profile myprof quota unlimited on users;
grant select on fred.tab1 to barney;

commit;

expdp user/pass full=y include=schema:\"in (select username from dba_users where profile='MYPROF')\"

Open in new window

bkreynolds48Author Commented:
I ran your test - had to change the passwords and it then errors out
ksh: 0403-057 Syntax error: '(' is not expected
I changed the line to

expdp user/pass full=y include=schema:\"in \(select username from dba_users where profile=\'MYPROF\'\)\"

and get errors
ora 39001 invalid argument value
ora 39071: value for INCLUE is badly formed
ora 01756 quoted string not properly terminated


so still working on it
slightwv (䄆 Netminder) Commented:
Work with your system administrator to figure out proper escaping for AIX
or
use the parameter file (without the escapes)
or
play around with them yourself with echo.

From a shell prompt try:
echo \"in \(select username from dba_users where profile=\'MYPROF\'\)\"

Keep experimenting until you get clean output of:
"in (select username from dba_users where profile='MYPROF')"

That is what expdp is expecting.
bkreynolds48Author Commented:
I did this
From a shell prompt try:
 echo \"in \(select username from dba_users where profile=\'MYPROF\'\)\"

 Keep experimenting until you get clean output of:
 "in (select username from dba_users where profile='MYPROF')"

the echo was exactly what I expected.

I went to a Linux system and ran both the command line and par file exports and they both worked - so there is something weird about the aix system I am on - so will keep looking into that

Thanks for your patience
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.