7 Replies Latest reply: Jan 24, 2013 12:43 AM by Dave Rabone RSS

    Dropping Non-System Users

    895327
      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
          SELECT OWNER, OBJECT_NAME, CREATED FROM ALL_OBJECTS;
          • 2. Re: Dropping Non-System Users
            Srini Chavali-Oracle
            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
              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
                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
                  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
                    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
                      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.