[ORACLE ADMIN] How to move object to other Tablespace step by step

-- Create New Tablespace

CREATE TABLESPACE TPB_LOG DATAFILE
  '/datafs/xxx/tpb_log01.dbf' SIZE 2M AUTOEXTEND ON NEXT 100M MAXSIZE 20480M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT)
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
/


--Check Default tablespace from users, and set new tablespace to users as default tablespace

select 'alter user ' || USERNAME || ' default tablespace TPB_LOG;' from dba_users where DEFAULT_TABLESPACE='LOG';

--Check grant quota on original tablespace. And grant for new tablespace:

select   TABLESPACE_NAME, USERNAME, BYTES,
         DECODE(MAX_BYTES, -1, 'Unlimited', MAX_BYTES) MAX_BYTES,
         BLOCKS,
         DECODE(MAX_BLOCKS, -1, 'Unlimited', MAX_BLOCKS) MAX_BLOCKS
from     sys.dba_ts_quotas
where    tablespace_name in ('LOG')
order by TABLESPACE_NAME, USERNAME;

alter user xx quota unlimited on TPB_LOG;
alter user yy quota unlimited on TPB_LOG;

-- Purge Recycle bin -- for all recyclebin

purge dba_recyclebin;

--Move Table to new Tablespace

--Rebuild index to new tablepace:

--Move LOB object to new Tablespace:

--Make sure that doesn't have any tables on old Tablespace

select count(*) from dba_tables where tablespace_name = 'LOG';
select count(*) from dba_indexes where tablespace_name = 'LOG';
select count(*) from dba_ind_partitions where tablespace_name = 'LOG';
select count(*) from dba_ind_subpartitions where tablespace_name = 'LOG';

-- Check and rebuild index Unusable
select 'alter index '||owner||'.'||index_name||' rebuild online parallel 8;' from dba_indexes where status='UNUSABLE';
-- Rebuild Partition index
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' online parallel 8;' from dba_ind_partitions where status='UNUSABLE';
-- Rebuild Sub Partition index
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' online parallel 8;' from dba_ind_subpartitions where status='UNUSABLE';

-- Compile Object

@?/rdbms/admin/utlrp.sql

-- Drop old tablespace:

DROP TABLESPACE LOG INCLUDING CONTENTS AND DATAFILES
/

-- Option: Rename new tablespace to original:

alter tablespace TPB_LOG rename to LOG

[ORACLE] Restricted Mode, Quiesced State

Open Database in Restricted Mode
SQL> startup restrict;
SQL> alter system enable restricted session;
SQL> alter system disable restricted session;
SQL> select logins from v$instance;



Placing a Database into a Quiesced State
SQL> ALTER SYSTEM QUIESCE RESTRICTED;
SQL> ALTER SYSTEM UNQUIESCE;
SQL> select ACTIVE_STATE from V$INSTANCE ;

[ORACLE PL/SQL] Grant select on schemas to user.

DECLARE
   TYPE T_V_SCHEMA IS TABLE OF VARCHAR2 (20);
   V_SCHEMA   T_V_SCHEMA;
   V_USER VARCHAR2(20);
BEGIN
   V_SCHEMA := T_V_SCHEMA ('xxx','yyy');          --Input Schema Name
   V_USER := 'zzz';
   FOR L_V_SCHEMA IN 1 .. V_SCHEMA.COUNT             --Loop schema in the list
   LOOP
       
      FOR L_V_TABLE IN (SELECT TABLE_NAME                     --Grant select on Table to the User
                          FROM DBA_TABLES
                         WHERE OWNER = V_SCHEMA (L_V_SCHEMA))
      LOOP
                              EXECUTE IMMEDIATE  'grant select on '
                                 || V_SCHEMA (L_V_SCHEMA)
                                 || '.'
                                 || L_V_TABLE.TABLE_NAME
|| ' to '
|| V_USER;

      END LOOP L_V_TABLE;
   END LOOP;
END;
/