Need To See All Objects in Database

#1

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...4232fbff48aa59

.... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

#2

grantschulte (AT) gmail (DOT) com wrote:

Quote:

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...4232fbff48aa59

... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

You are doing the equivalent of dropping a brick on your foot if you
think you can be successful with Oracle by treating it like it is in
any significant way similar to other RDBMS products.

Before you touch it you need to read the concepts and architecture
docs at http://tahiti.oracle.com.

The data dictionary
http://www.psoug.org/reference/data_dict.html
is where information is located.
You want to learn the ALL_ and USER_ views for developer work.
The DBA_ views for answering your specific question assuming
you know what the word "database" means in Oracle. Hint: It's
meaning has nothing to do with the definition of "database" in
SQL Server: Two entirely different constructs.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

grantschulte (AT) gmail (DOT) com wrote:

Quote:

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...4232fbff48aa59

... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

You are doing the equivalent of dropping a brick on your foot if you
think you can be successful with Oracle by treating it like it is in
any significant way similar to other RDBMS products.

Before you touch it you need to read the concepts and architecture
docs at http://tahiti.oracle.com.

The data dictionary
http://www.psoug.org/reference/data_dict.html
is where information is located.
You want to learn the ALL_ and USER_ views for developer work.
The DBA_ views for answering your specific question assuming
you know what the word "database" means in Oracle. Hint: It's
meaning has nothing to do with the definition of "database" in
SQL Server: Two entirely different constructs.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

grantschulte (AT) gmail (DOT) com wrote:

Quote:

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...4232fbff48aa59

... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

You are doing the equivalent of dropping a brick on your foot if you
think you can be successful with Oracle by treating it like it is in
any significant way similar to other RDBMS products.

Before you touch it you need to read the concepts and architecture
docs at http://tahiti.oracle.com.

The data dictionary
http://www.psoug.org/reference/data_dict.html
is where information is located.
You want to learn the ALL_ and USER_ views for developer work.
The DBA_ views for answering your specific question assuming
you know what the word "database" means in Oracle. Hint: It's
meaning has nothing to do with the definition of "database" in
SQL Server: Two entirely different constructs.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

grantschulte (AT) gmail (DOT) com wrote:

Quote:

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...4232fbff48aa59

... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

You are doing the equivalent of dropping a brick on your foot if you
think you can be successful with Oracle by treating it like it is in
any significant way similar to other RDBMS products.

Before you touch it you need to read the concepts and architecture
docs at http://tahiti.oracle.com.

The data dictionary
http://www.psoug.org/reference/data_dict.html
is where information is located.
You want to learn the ALL_ and USER_ views for developer work.
The DBA_ views for answering your specific question assuming
you know what the word "database" means in Oracle. Hint: It's
meaning has nothing to do with the definition of "database" in
SQL Server: Two entirely different constructs.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

On 19 Aug, 00:49, grantschu... (AT) gmail (DOT) com wrote:

Quote:

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...wse_thread/thr...

... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

In SQL Developer, when connected as SYSTEM, you should be able to see
objects belonging to other schemas by opening the "Other Users" tree
under the "Connections" tab.

HTH

-g

#7

On 19 Aug, 00:49, grantschu... (AT) gmail (DOT) com wrote:

Quote:

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...wse_thread/thr...

... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

In SQL Developer, when connected as SYSTEM, you should be able to see
objects belonging to other schemas by opening the "Other Users" tree
under the "Connections" tab.

HTH

-g

#8

On 19 Aug, 00:49, grantschu... (AT) gmail (DOT) com wrote:

Quote:

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...wse_thread/thr...

... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

In SQL Developer, when connected as SYSTEM, you should be able to see
objects belonging to other schemas by opening the "Other Users" tree
under the "Connections" tab.

HTH

-g

#9

On 19 Aug, 00:49, grantschu... (AT) gmail (DOT) com wrote:

Quote:

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...wse_thread/thr...

... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

In SQL Developer, when connected as SYSTEM, you should be able to see
objects belonging to other schemas by opening the "Other Users" tree
under the "Connections" tab.

HTH

-g

#10

On Aug 19, 6:01*am, gazzag <gar... (AT) jamms (DOT) org> wrote:

Quote:

On 19 Aug, 00:49, grantschu... (AT) gmail (DOT) com wrote:

Hi,

I am a SQL Server / PostgreSQL developer and am fairly new to Oracle.
I'm starting out using Oracle XE 10g with the web based client that
comes with it, and Oracle SQL Developer as my clients.

I'm trying to figure out how to see all database objects (for all
users) in the Object Browser, similar to what you see when logged in
as 'sa' in SQL Server, or 'postgres' in PostgreSQL. I know there are
the SYS and SYSTEM accounts, but I don't see other users' tables,
views, etc when logged in under SYS or SYSTEM. I know that I can
SELECT TABLE_NAME FROM ALL_TABLES; but that's not like seeing them in
a tree view.

The following Groups post was the closest thing I could find to an
answer:

http://groups.google.ca/group/comp.d...wse_thread/thr...

... but this is from 2001, and the answer given is that it can't be
done. Has anything changed since then?

I have used a SQL Server 2000 DTS package to connect to Oracle with an
Oracle username/password and was able to browse other users' objects
that I had permissions to see, so there must be a way to do this.

Any help would be greatly appreciated!

Thanks,
Grant Schulte

In SQL Developer, when connected as SYSTEM, you should be able to see
objects belonging to other schemas by opening the "Other Users" tree
under the "Connections" tab.

HTH

-g

Daniel and gazzag,

thanks very much for your responses! Both were very helpful.

I'll read and become familiar with all the docs you sent Daniel, and
gazzag, thanks to you I can see all user objects in one spot grouped
by user. I'm migrating a large project from PostgreSQL to Oracle for
an external client and it helps to see things grouped this way (looks
similar to viewing schemas in PostgreSQL).

Grant