Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

User database objects definition

Manuel G
Frequent Advisor

User database objects definition

Does anyone knows how to get user database objects characteristics (name, definition, everything).

We have a user and want to get a report about his objects including everything (tables, grants, etc).

Can we obtain it from Enterprise Manager??

Thanks
4 REPLIES
malay boy
Trusted Contributor

Re: User database objects definition

Hi,
If you want to know everything taht a user have you can use user_objects view.

some description on user_obejcst

SQL> desc user_objects;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

Hope this help

regards
mB
There are three person in my team-Me ,myself and I.
Brian Crabtree
Honored Contributor

Re: User database objects definition

You can also query user_tab_columns (table columns), user_ind_columns (indexed columns), user_constraints (constraint information), user_views.

To see the list of possible user views, the following will work:

select view_name from dba_views where owner like 'USER%';

Hope this helps,

Brian
Yogeeraj_1
Honored Contributor

Re: User database objects definition

 
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Indira Aramandla
Honored Contributor

Re: User database objects definition

Hi,

To extract user objects definitions you can get them from Enterprise Manageger, or from quering data dictionary views or exporting like Yogeeraj said.

It depends on what type of definition you are looking for.

1. If you what just the object name, object type and date created by that particular user then you can get this from the "Enterprise Manager" report or by querying the USER_OBJECTS dictionary view. This will give the object_name, object_type (table / index / function /view /procedure /synonym...). Through Enterprise Manager, go to security panel and then users and select the options from the tool bar and then report and then this will generate report fro you. But one thing you have to remember here is you should know the type of user objects. Then you can go to the schema panel and select the type (tables / indexes / functions...) and then OPTIONS---REPORT from the tool bar and generate the report.

2. If you wanted the complete definition of the user objects like table name and the create scripts, then use the export option. The export dump will contain the complete definition of all the objects belonging to the user. Then you can do a fake inport with parameter in the import SHOW=Y and generate a log. This will give you all the craate statements of all the user objects.

I hope this helps.
Never give up, Keep Trying