Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to Check Database Schemas in Oracle Database on Linux Server?

Posted on 2012-04-06
21
Medium Priority
?
571 Views
Last Modified: 2012-04-10
I'm not a Unix expert, but we have installed an Oracle DB on Linux server. we ran a script that created a lot of oracle schemas inside a database called: demo.

I am logged on to that database server and using Sqlplus to login as SYSTEM.

I would like to be able to view all the schemas that were created and also to be able to change the password for some of them.

could you please provide some commands on how I can do this type of DB admin tasks direclty from the sqlplus command line?

Thanks.
0
Comment
Question by:matrix0511
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 4
  • +2
21 Comments
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 37817711
Check with

select * from dba_users;

Alter the password with

alter user schema identified by new_password;

Wmp
0
 
LVL 59

Expert Comment

by:Darius Ghassem
ID: 37817712
One you can use Oracle Enterprise Manager

Or from sqlplus using this command select distinct owner from dba_objects;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37817713
Instead of 'select *':  select username
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 37817722
I'm no Oracle expert either, but try these:

SELECT username FROM all_users ORDER BY username;

alter user <user_name> identified by <new_password>;
0
 

Author Comment

by:matrix0511
ID: 37818023
Guys, How can I use Oracle Enterprise Manager? I think that the GUI of using Oracle Enterprise Manager would be best.

I have the Oracle client intalled with admin stools but i don't see Oracle Enterprise Manager. Can someone help me with access to OEM?

Thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37818036
>>Can someone help me with access to OEM?

OEM is now browser based as of 10g.  The 9i OEM fat Java version still works against a 10g database.  After that:  Either dbConsole inside the database itself or Grid Control for managing multiple instances.   Either way:  Browser based.

>>I think that the GUI of using Oracle Enterprise Manager would be best.

Opinion.  I prefer sqlplus and a simple select statement.  But, that is just me.  Besides, OEM doesn't offer a SQL worksheet like SQL Developer, if you like GUIs.
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 37818541
0
 

Author Comment

by:matrix0511
ID: 37827829
Last thing Guys, whats the SQL command to search for ALL tablespaces in the Oracle DB?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37827849
>>whats the SQL command to search for ALL tablespaces in the Oracle DB?

This is a different question.

All the data dictionalry views are in the docs:
select tablespace_name from dba_tablespaces;


Keep this SQL handy:
select view_name from dba_views where view_name like upper('%&view_to_find%') order by 1
/


I call it: find_view.sql

When you execute it and prompted, just enter a partial word like tablespace and it will show you ALL the views that are like that term.

99% of the time a view will pop up and it will be obvious what one you need.
0
 

Author Comment

by:matrix0511
ID: 37827941
@slightwv,

With the SQL you provided: select view_name from dba_views where view_name like upper('%&view_to_find%') order by 1

Are you suggesting that I take that one line and paste it in Notepad and save it as...  .SQL file? then upload it to my Oracle DB Linux server  location?

If so, how would I run that script from the Linux server? Not sure how to execute sql scripts on Oracle Linux server.

I just need more detailed steps. Thanks
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37827954
>>then upload it to my Oracle DB Linux server  location?

Save it where ever you run sqlplus from.  To execute the script from sqlplus:
SQL>@find_view

by default sqplplus looks for scripts in the folder you execute sqlplus from.  If you save it to another folder:
SQL> @/some/path/find_view
0
 

Author Comment

by:matrix0511
ID: 37827972
Ok. I just copied your SQL and pasted it into my Sqlplus session. See results below. It still doesn't list all of my tablespaces. Did I not enter the correct info? when it prompted me I entered: tablespace.

But I know I have tablespaces called: PRODDTAT, PRODDTAI, TESTDTAT, TESTDTAI, etc, etc.
0
 

Author Comment

by:matrix0511
ID: 37827978
SQL Command Results
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37828002
you are mis-understanding what I am saying:

To find your tablespaces uses  the DBA_TABLESPACES view that I posted in http:#a37827849

select tablespace_name from dba_tablespaces;



The other SQL is just a helper to help you locate the data dictionary view you need.  For example: You were looking for tablespaces, run my find_view script and enter tablespace.  In the list, you see DBA_TABLESPACES?  That might be the view you need...

The original question, you were looking for USERS, run the find_view script, enter USER and you see the view: DBA_USERS.

If is just a simple way to locate the correct view to find what you are looking for.  Then again, you can always ask a question here...
0
 
LVL 59

Expert Comment

by:Darius Ghassem
ID: 37828012
That shows you the views within the system this is not showing you the tablespaces.

this will show you the view for tablespaces:

select tablespace_name from dba_tablespaces;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37828022
dariusg,

I already posted that exact query in http:#a37827849

The rest of the SQL was for future help in finding the correct view.

Please read all previous posts before posting.
0
 
LVL 59

Expert Comment

by:Darius Ghassem
ID: 37828043
slightwv,

I was explaining to him what you put. I wasn't trying to post the same thing over again.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37828052
"this will show you the view for tablespaces:" comes across as posting duplicate information not explaining a previous post without a reference to a previous post.
0
 

Author Comment

by:matrix0511
ID: 37828057
I got it guys. Thanks for explaining all this Slightwv. I appreciate it.
0
 
LVL 59

Expert Comment

by:Darius Ghassem
ID: 37828124
@slightwv

As you can see the first part of the second sentence was cut off which was pointing back to your post.

Look I have been an expert for a while on EE check my credentials.
0
 

Author Closing Comment

by:matrix0511
ID: 37829949
Thanks!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question