This content has been marked as final. Show 7 replies
sb92075 wrote:The following statement above produced an extremely large scrolling list which ran well past my scroll back buffer...
SELECT OWNER, OBJECT_NAME, CREATED FROM ALL_OBJECTS;
What exactly is that command showing me? All users on the entire database system? Is there something I can filter with to just show me regular (non-system) users for example, like my user account being 'cmennens'?
74361 rows selected.
I am running:
Oracle EE 220.127.116.11
Single instance database (No RAC Clustering)
Red Hat Enterprise Linux 6.3 64-bit
16 GB RAM
No ASM or anything. Just as vanilla of a single instance Oracle database server as possible.
I was going to start and remove the user roles and assigned schemas for all non-system accounts that had no tables and or data in it.What if it is a database account that, although not owning any objects (tables) in the database, has been granteed access (SELECT , INSERT/UPDATE/DELETE) to another schema's objects ? This non-owning database account might be in use for a particular application or user.
will list database accounts that don't own objects.
select username from dba_users where username not in (select distinct(owner) from dba_objects);
You must be careful not to drop pre-seeded accounts that may not be owning objects but may be needed for various database functions.
Similarly, you must be careful not to drop accounts that are used by applications to access data in other schemas.
Hemant K Chitale
Best way to identify non-system schema is to first get size of each schema except %SYS% users & few more default sys user like XDB,OUTLN,PERFSTAT etc.
Refer below queries which may help you ..
select owner,round(sum(bytes)/1024/1024,0) from dba_segments where owner not like '%SYS%' and owner not in('PERFSTAT','OUTLN','XDB') group by owner order by 2 desc; select 'drop table '|| owner ||'.'|| table_name ||';' from dba_tables where owner in ('&owner'); drop user <owner> cascade;