Home » SQL & PL/SQL » SQL & PL/SQL » join query (RDBMS 11G linux)
join query [message #626406] Mon, 27 October 2014 12:28 Go to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
Hello
how can I join this sql query to spool only 1 file out. basically I need a sql to see all users, permissions, roles and last time they logged into the database

SELECT DISTINCT d.username "Username", d.account_status "Account Status", d.profile "Profile" FROM dba_users d, v$pwfile_users p WHERE p.username (+) = d.username ORDER BY initcap(d.username)
/

select grantee "Username", granted_role "Role", admin_option "Admin Option", default_role "Default" from sys.dba_role_privs order by grantee
/
Re: join query [message #626408 is a reply to message #626406] Mon, 27 October 2014 12:33 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
spool your_File.txt
select distinct ...
select grantee ...
spool off;


EDIT: Or, you could even UNION (ALL) those SELECT statements - just make sure that columns you select match by number and data type (which, basically, means that you have to include TO_CHAR(NULL) into the first SELECT).

[Updated on: Mon, 27 October 2014 12:35]

Report message to a moderator

Re: join query [message #626409 is a reply to message #626406] Mon, 27 October 2014 12:47 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
thanks
what I mean is how to join these 2 query's into a single sql statement

SELECT DISTINCT d.username "Username", d.account_status "Account Status", d.profile "Profile" FROM dba_users d, v$pwfile_users p WHERE p.username (+) = d.username ORDER BY initcap(d.username)
/

select grantee "Username", granted_role "Role", admin_option "Admin Option", default_role "Default" from sys.dba_role_privs order by grantee
/
Re: join query [message #626410 is a reply to message #626409] Mon, 27 October 2014 12:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
what I mean is how to join these 2 query's into a single sql statement
UNION ALL. As said already.

Quote:
and last time they logged into the database
You'll need to query user$.spare6 for this.
Re: join query [message #626411 is a reply to message #626409] Mon, 27 October 2014 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Why DISTINCT in the first query?
2/ Why do you want to union 2 unrelated queries? What is the problem with 2 queries?

Re: join query [message #626412 is a reply to message #626411] Mon, 27 October 2014 13:02 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
it does not have to be distinct - I just need one query to select username, account_status, profile, v$pwfile_users from dbausers and join with grantee grated_role , admin_option , default_role from sysdbaroles

so basically join the 2 into 1
thank you
Re: join query [message #626413 is a reply to message #626412] Mon, 27 October 2014 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not clear what you want.
What do you mean by "join",
Note that "sysdbaroles" does not exist.

Re: join query [message #626414 is a reply to message #626413] Mon, 27 October 2014 13:22 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
sorry
what I need is a query to select all of these into one single statement


SELECT DISTINCT d.username "Username", d.account_status "Account Status", d.profile "Profile" FROM dba_users d, v$pwfile_users p WHERE p.username (+) = d.username ORDER BY initcap(d.username)
/

select grantee "Username", granted_role "Role", admin_option "Admin Option", default_role "Default" from sys.dba_role_privs order by grantee
Re: join query [message #626415 is a reply to message #626414] Mon, 27 October 2014 13:23 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
A compound query (as suggested by LF) is a single statement. What's your problem with it?

--update: incidentally, why are you including v$pwfile_users in the query? It adds nothing.

[Updated on: Mon, 27 October 2014 13:25]

Report message to a moderator

Re: join query [message #626416 is a reply to message #626415] Mon, 27 October 2014 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what is the problem with 2 queries?

Re: join query [message #626418 is a reply to message #626416] Mon, 27 October 2014 13:54 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
we need it in a single select statement to

select username, account_status, profile from dba_users
then join with
select grantee, granted_role, admin_option, default_role from sys.dba_role_privs

how do I join them into one?

Re: join query [message #626419 is a reply to message #626418] Mon, 27 October 2014 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 27 October 2014 19:17

Not clear what you want.
What do you mean by "join",
...

Re: join query [message #626420 is a reply to message #626419] Mon, 27 October 2014 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Mon, 27 October 2014 19:41

And what is the problem with 2 queries?


Re: join query [message #626421 is a reply to message #626419] Mon, 27 October 2014 14:08 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
how to join these two selects from dba_users and sys.dba_role_privs

select username, account_status, profile from dba_users

select grantee, granted_role, admin_option, default_role from sys.dba_role_privs

Re: join query [message #626423 is a reply to message #626421] Mon, 27 October 2014 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 27 October 2014 20:03

Michel Cadot wrote on Mon, 27 October 2014 19:17

Not clear what you want.
What do you mean by "join",
...


Michel Cadot wrote on Mon, 27 October 2014 20:04

Michel Cadot wrote on Mon, 27 October 2014 19:41

And what is the problem with 2 queries?


Re: join query [message #626424 is a reply to message #626421] Mon, 27 October 2014 14:48 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
-:) here we go

how to I get a select from dba_users and from sys.dba_roles_privs
to get
username, account_status , profile grantee , granted_role, admin_option ,default_role

thanks

Re: join query [message #626425 is a reply to message #626424] Mon, 27 October 2014 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just join dba_users and dba_role_privs on username=grantee.



Re: join query [message #626426 is a reply to message #626424] Mon, 27 October 2014 15:05 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
thanks
Im trying this

SQL> select a.username , a.account_status, a.profile , b.grantee, b.granted_role, b.admin_option, b.default_role from a dba_users b sys.dba_role_privs;
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
Re: join query [message #626427 is a reply to message #626426] Mon, 27 October 2014 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you know how to write a query and a join?

Re: join query [message #626428 is a reply to message #626427] Mon, 27 October 2014 15:16 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
I figure it out = sorry and thank you
bad day
Re: join query [message #626431 is a reply to message #626428] Mon, 27 October 2014 15:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post the final query to complete the topic for future readers.

Re: join query [message #626432 is a reply to message #626431] Mon, 27 October 2014 15:43 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
here we go
sorry bad day sometimes cant think -;)

--col UserName format a30
--col Server format a15
--col DBInstance format a15
--col DatabaseName format a15
--col SchemaObjects format a30
--col Roles format a20
col LastLogonDate format a15
col ResourceOwner format a15
col EmplID format a10
set colsep ,
set lines 500
set pages 50000
set echo off
set feedback off
--set sqlprompt ''
--set trimspool on
set headsep off
spool MRA5_&DBName._&Date..csv

select a.username, a.account_status, a.profile, a.expiry_date, b.grantee, b.granted_role, b.admin_option, b.default_role from dba_users a, sys.dba_role_privs b
order by a.username
/
spool off
exit
Re: join query [message #626434 is a reply to message #626432] Mon, 27 October 2014 15:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not correct.
You do not join the views as I mentioned; you just make a Cartesian product. A WHERE clause is missing.

Please How to use [code] tags and make your code easier to read.

Re: join query [message #626435 is a reply to message #626434] Mon, 27 October 2014 15:54 Go to previous messageGo to next message
evoradba
Messages: 144
Registered: April 2005
Location: Canada
Senior Member
I get what I need

USERNAME ,ACCOUNT_STATUS ,PROFILE ,EXPIRY_DA,GRANTEE ,GRANTED_ROLE ,ADM,DEF


------------------------------,--------------------------------,------------------------------,---------,---------------------------- --,------------------------------,---,---


TEST ,LOCKED ,APPLICATION ,08-APR-14,SYS ,XDB_SET_INVOKER ,YES,YES
Re: join query [message #626436 is a reply to message #626435] Mon, 27 October 2014 15:56 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The query you gave can't return the result you posted.
I repeat, the query is wrong.

Please read and APPLY OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Previous Topic: SQL Query Optimization
Next Topic: Sequence ora-8002 error for every connection.
Goto Forum:
  


Current Time: Fri Mar 29 06:59:50 CDT 2024