Results 1 to 12 of 12
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Removing Multiple Tables for Multiple users

    Hello! I recently started practicing oracle. At the moment, I'm stuck Removing all tables for a range of users.

    I'd like to be able to use the following: LIKE 'user9__' with PL/SQL to remove only these user account tables.

    I'm having multiple problems implementing a query with PL/SQL. Foremost, how can I create a loop which cycles through all the different users?

    Second, how do I delete all the tables associated with this user? I've looked at having a "Select * from Cat" to list all tables that the user owns. However, instead of getting table names. I get a list of unreadable results.

    Code:
    .
    .
    .
    ------------ ---------- ---------- ----------- -------------------------
    NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL
    ------------------- ---------- ---------- ----- -------- ----------- ---------
    PAR IOT_TYPE     T S NES BUFFER_ ROW_MOVE GLO USE DURATION        SKIP_COR MON
    --- ------------ - - --- ------- -------- --- --- --------------- -------- ---
    CLUSTER_OWNER                  DEPENDEN
    ------------------------------ --------
    
    
    334 rows selected.
    
    SQL>
    This is very frustrating and I feel like I'm getting no where. Can anyone guide me in the right direction?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As you are about to delete all tables user owns, I suppose this user isn't very important. So, could you consider dropping a user instead?

    Selecting from CAT doesn't look like this (at least, not on any Oracle database I can access, which includes versions 8, 9 and 10). Are you SURE this is output from the CAT (as it contains TABLE_NAME and TABLE_TYPE).

    Also, as you aren't experienced in PL/SQL coding, perhaps you should brush your skills on easier tasks than switching over to different users and doing *something* in difrerent schemas.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Live the adventure!

    Just go for it...try this SQL&Plus script:
    Code:
    set pages 0 term off echo off ver off feed off
    spool delete_users.sql
    Select 'DROP USER '||username||' INCLUDING CONTENTS;'
      From DBA_USERS
     Where username like 'USER9__';
    spool off
    set pages 33 term on echo on ver on feed on
    @delete_users.sql

    PS: You may want to do an export backup before you try this...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by Littlefoot
    As you are about to delete all tables user owns, I suppose this user isn't very important. So, could you consider dropping a user instead?
    Actually I'd like to keep the users. I would just like to drop all their tables and reset their passwords to some common password. Shouldn't there be a simple way of doing this with my LIKE clause targets? Or do I need nested loops? Its also very interesting that you don't get similar output when you type,

    select * from CAT;

    I figured I just wasn't formatting the output properly. Maybe there's a problem with my data dictionary?

    Note: I have a programmer background and I tend to think in terms of nested loops.. but maybe its different with PL/SQL.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As of CAT:
    Code:
    SQL> select banner from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    SQL> select * from cat;
    
    TABLE_NAME                     TABLE_TYPE
    ------------------------------ -----------
    VOC_NO                         SEQUENCE
    MYSEQ                          SEQUENCE
    NOVEL_SEQ                      SEQUENCE
    TEST_SEQ                       SEQUENCE
    TEST                           TABLE
    V_TEST                         VIEW
    V_MOBILE                       VIEW
    EMP                            TABLE
    ...
    As you didn't tell us why exactly you want to do that, I'll try to guess one of possible scenarios: you have students who do ... well, whatever they do in their schemas - create tables, write queries etc. Once they are done, you want to empty these schemas and make them ready for new students.

    If that's the case, in my opinion, it is simpler to drop all these users and recreate them either using a script (create user, grant required privileges) or - maybe even easiser - using the import utility.

    Dropping tables through all users and resetting their passwords seems to be the worst idea.

  6. #6
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by Littlefoot
    As of CAT:
    Code:
    SQL> select banner from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    SQL> select * from cat;
    
    TABLE_NAME                     TABLE_TYPE
    ------------------------------ -----------
    VOC_NO                         SEQUENCE
    MYSEQ                          SEQUENCE
    NOVEL_SEQ                      SEQUENCE
    TEST_SEQ                       SEQUENCE
    TEST                           TABLE
    V_TEST                         VIEW
    V_MOBILE                       VIEW
    EMP                            TABLE
    ...
    If that's the case, in my opinion, it is simpler to drop all these users and recreate them either using a script (create user, grant required privileges) or - maybe even easiser - using the import utility.

    Dropping tables through all users and resetting their passwords seems to be the worst idea.
    It may seem like a bad idea to you but this is what I'm trying to do. I'd like to drop all tables for for all users that match the LIKE clause (above).

    After this, I need to reset all the passwords for the users. to something common. Can this be done with nested loops? Any help would be msuch appreciated creating such a query.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    LKBrwn_DBA posted the solution above!
    Write SQL to write SQL to set passwords as LKBrwn_DBA did.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    But, he doesn't want to drop users ...

    I've re-read LKBrwn_DBA's code and saw
    Code:
    DROP USER username INCLUDING CONTENTS;
    Which Oracle database version offers such a DROP USER syntax? I've checked 10g and 11g, but all I managed to find was DROP USER [CASCADE]. "INCLUDING CONTENTS" is mentioned with the DROP TABLESPACE statement.

    So, what is it about? An undocumented feature? (I'm not in the mood to try it on my database).

  9. #9
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    I'm guessing anacedant wasn't referring to the actual "drop user" statement, but rather to the method of spooling the results of a sql statement to a text file, and then running that text file to accomplish the ultimate goal.

    Obviously, the sql statement would be along the lines of:
    Code:
     select 'DROP TABLE '||username||'.'||table_name||';'
       from dba_tables 
       where username like 'USER9_';
    (not tested for accuracy)

    Referring back to LKBrown_DBA's earlier post, if you wrap this sql statement with the formatting and spool commands, it will attemp to drop all of the tables for a user. I am wondering however, if certain referential integrity constraints might cause issues and prevent some tables from being dropped.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am wondering however, if certain referential integrity constraints might cause issues and prevent some tables from being dropped.
    If you issue DROP TABLE of parent before child is dropped, then it fails.
    To "work around" situation, you could simply invoke the drop table SQL multiple times until all tables are eliminated.
    Eventually all the table are eliminated.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Quote Originally Posted by anacedent
    >I am wondering however, if certain referential integrity constraints might cause issues and prevent some tables from being dropped.
    If you issue DROP TABLE of parent before child is dropped, then it fails.
    To "work around" situation, you could simply invoke the drop table SQL multiple times until all tables are eliminated.
    Eventually all the table are eliminated.
    I know, been there done that. I was really hoping to get the original poster to think about it and realize there has to be a better way. But, I guess the first time I did something similar I tried it this way. I guess we all have to learn on our own.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The trade off is increased logic & complexity vs. brute force simplicity & effectiveness.

    You have the solution good, fast, or cheap. Pick any 2 metrics.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •