SQL> show user
USER is "SYS"
SQL> create user t identified by t;

User created.

SQL> grant dba to t;

Grant succeeded.

SQL> conn t/t
Connected.
SQL>
SQL> show user
USER is "T"
SQL> select count(*) from DBA_TABLES;

  COUNT(*)
----------
      2216

SQL>

SQL> show user
USER is "SYS"
SQL>
SQL> create user t identified by t;

User created.

SQL> grant connect,resource to t;

Grant succeeded.

SQL>
SQL> CREATE OR REPLACE PROCEDURE SYS.TEMPORARILY_ELAVATE_PRIVS
  2  AUTHID DEFINER
  3  is
  4  --
  5  V_CMD1 VARCHAR2(1000);
  6  BEGIN
  7  V_CMD1 := 'grant DBA to t';
  8  --
  9  EXECUTE IMMEDIATE V_CMD1;
 10  end;
 11  /

Procedure created.

SQL> grant execute on SYS.TEMPORARILY_ELAVATE_PRIVS to t;

Grant succeeded.

SQL>
SQL> CONN t/t
Connected.
SQL>
SQL> show user
USER is "T"
SQL>
SQL>
SQL>
SQL> exec SYS.TEMPORARILY_ELAVATE_PRIVS;

PL/SQL procedure successfully completed.

SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist