for loop cursor

BEGINEND LOOP;END;

jnrjian

32人浏览 · 2026-03-23 17:45:36

jnrjian · 2026-03-23 17:45:36 发布

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/oradata/DB11G/temp02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Switch all existing users to new temp tablespace.
BEGIN
  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP') LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE temp2';
  END LOOP;
END;
/

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;