Select users list which default_tablespace is SYSTEM. In my case, application users prefix are APP.
SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%';
USERNAME DEFAULT_TABLESPACE
APPLWEB SYSTEM
APPLDESK SYSTEM
APPLTEST SYSTEM
Now, use below little bit PLSQL block.
BEGIN
FOR i IN (
SELECT username FROM dba_users
WHERE default_tablespace in ('SYSTEM') and username like 'APP%'
) LOOP
EXECUTE IMMEDIATE 'ALTER USER ' || i.username || ' DEFAULT TABLESPACE USERS';
END LOOP;
END;
/
Check
SELECT username, default_tablespace FROM dba_users WHERE username like 'APP%';
USERNAME DEFAULT_TABLESPACE
APPLWEB USERS
APPLDESK USERS
APPLTEST USERS
No comments:
Post a Comment