This discussion is archived
7 Replies Latest reply: Jan 23, 2013 10:43 PM by Dave Rabone RSS

Dropping Non-System Users

895327 Newbie
Currently Being Moderated
I've been looking at our Oracle database here in the office and it seems it's been some time since someone sat down and wore a DBA hat for some maintenance and much needed clean up. 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. I was wondering how I can tell the difference between a Oracle required system user needed for RDBMS operations or if it was a post install added user / role / schema? Just trying to pro-actively clean up and remove the clutter. Before any changes were made to this system, I used RMAN to create a complete full backup of the entire system state & data.

Thanks for any info and or help!
  • 1. Re: Dropping Non-System Users
    sb92075 Guru
    Currently Being Moderated
    SELECT OWNER, OBJECT_NAME, CREATED FROM ALL_OBJECTS;
  • 2. Re: Dropping Non-System Users
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post details of OS and database versions.

    Pl see if this MOS Doc can help

    Information On Installed Database Components and Schemas [ID 472937.1]

    HTH
    Srini
  • 3. Re: Dropping Non-System Users
    895327 Newbie
    Currently Being Moderated
    sb92075 wrote:
    SELECT OWNER, OBJECT_NAME, CREATED FROM ALL_OBJECTS;
    The following statement above produced an extremely large scrolling list which ran well past my scroll back buffer...
    74361 rows selected.
    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'?

    I am running:

    Oracle EE 11.2.0.3
    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.
  • 4. Re: Dropping Non-System Users
    vlethakula Expert
    Currently Being Moderated
    It depends on what components are installed.

    select comp_name,status from dba_registry;

    And check this link and go to Predefined Administrative Accounts

    http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_user_accounts.htm
  • 5. Re: Dropping Non-System Users
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    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.

    This :
    select username from dba_users
    where username not in (select distinct(owner) from dba_objects);
    will list database accounts that don't own 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
  • 6. Re: Dropping Non-System Users
    moreajays Pro
    Currently Being Moderated
    Hi,

    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;
    Thanks,
    Ajay More
    http://www.moreajays.com
  • 7. Re: Dropping Non-System Users
    Dave Rabone Journeyer
    Currently Being Moderated
    It's a fair bet that any username in dba_users where dba_users.created is the same as SYS and SYSTEM was created during the databse creation process and should not be touched.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points