[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;
/

[ORACLE 11GR2 PLSQL] MAKE A SCHEDULE JOB TO KILL INACTIVE SESSION

1/CREATE PROCEDURE KILL_INACTIVE_SESSION

CREATE OR REPLACE PROCEDURE KILL_INACTIVE_SESSION
IS
   V_SQLIMMEDIATE   VARCHAR2 (100) := NULL;
BEGIN
   FOR V_L_SESSION
      IN (SELECT SID, SERIAL#, INST_ID
            FROM GV$SESSION
           WHERE STATUS = 'INACTIVE' AND LOGON_TIME < (SYSDATE - 3 / 24))
   LOOP
      V_SQLIMMEDIATE :=
            'ALTER SYSTEM KILL SESSION '''
         || V_L_SESSION.SID
         || ','
         || V_L_SESSION.SERIAL#
         || ',@'
         || V_L_SESSION.INST_ID
         || ''' IMMEDIATE';

      BEGIN
         EXECUTE IMMEDIATE V_SQLIMMEDIATE;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   --DBMS_OUTPUT.PUT_LINE (V_SQLIMMEDIATE);
   END LOOP;
END;
/

2/ CREATE JOB RUN DAILY AT 12 AM

BEGIN
   SYS.DBMS_SCHEDULER.CREATE_JOB (
      job_name          => 'SYS.JOB_KILL_INACTIVE_SESSION',
      start_date        => NULL,
      repeat_interval   => 'FREQ=DAILY;INTERVAL=1;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
      end_date          => NULL,
      job_class         => 'DEFAULT_JOB_CLASS',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'SYS.KILL_INACTIVE_SESSION',
      comments          => NULL);
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_KILL_INACTIVE_SESSION',
                                     attribute   => 'RESTARTABLE',
                                     VALUE       => FALSE);
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
      name        => 'SYS.JOB_KILL_INACTIVE_SESSION',
      attribute   => 'MAX_FAILURES');
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
      name        => 'SYS.JOB_KILL_INACTIVE_SESSION',
      attribute   => 'MAX_RUNS');

   BEGIN
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_KILL_INACTIVE_SESSION',
                                        attribute   => 'STOP_ON_WINDOW_CLOSE',
                                        VALUE       => FALSE);
   EXCEPTION
      -- could fail if program is of type EXECUTABLE...
      WHEN OTHERS
      THEN
         NULL;
   END;

   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_KILL_INACTIVE_SESSION',
                                     attribute   => 'JOB_PRIORITY',
                                     VALUE       => 3);
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
      name        => 'SYS.JOB_KILL_INACTIVE_SESSION',
      attribute   => 'SCHEDULE_LIMIT');
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_KILL_INACTIVE_SESSION',
                                     attribute   => 'AUTO_DROP',
                                     VALUE       => FALSE);
   SYS.DBMS_SCHEDULER.ENABLE (name => 'SYS.JOB_KILL_INACTIVE_SESSION');
END;
/

[Oracle PLSQL] Truncate Table in many Schemas

/* Formatted on 5/28/2018 2:54:17 PM (QP5 v5.149.1003.31008) */
DECLARE
   TYPE T_V_SCHEMA IS TABLE OF VARCHAR2 (20);
   V_SCHEMA   T_V_SCHEMA;
BEGIN
   V_SCHEMA := T_V_SCHEMA ('TEST1','TEST2','TEST3');          --Input Schema Name

   FOR L_V_SCHEMA IN 1 .. V_SCHEMA.COUNT             --Loop schema in the list
   LOOP
      FOR L_V_TABLE
         IN (SELECT TABLE_NAME, CONSTRAINT_NAME --Disable first the foreign key
               FROM DBA_CONSTRAINTS
              WHERE     CONSTRAINT_TYPE = 'R'
                    AND STATUS = 'ENABLED'
                    AND OWNER = V_SCHEMA (L_V_SCHEMA))
      LOOP
         --                     EXECUTE IMMEDIATE   ' ALTER TABLE '
         --                        || V_SCHEMA (L_V_SCHEMA)
         --                        || '.'
         --                        || L_V_TABLE.TABLE_NAME
         --                        || ' DISABLE CONSTRAINT '
         --                        || V_SCHEMA (L_V_SCHEMA)
         --                        || '.'
         --                        || L_V_TABLE.CONSTRAINT_NAME;

         DBMS_OUTPUT.
          PUT_LINE (
               'ALTER TABLE '
            || V_SCHEMA (L_V_SCHEMA)
            || '.'
            || L_V_TABLE.TABLE_NAME
            || ' DISABLE CONSTRAINT '
            || L_V_TABLE.CONSTRAINT_NAME || ';');
      END LOOP L_V_TABLE;


      FOR L_V_TABLE
         IN (SELECT TABLE_NAME, CONSTRAINT_NAME -- then disable all constraints
               FROM DBA_CONSTRAINTS
              WHERE STATUS = 'ENABLED' AND OWNER = V_SCHEMA (L_V_SCHEMA))
      LOOP
         --                     EXECUTE IMMEDIATE   'ALTER TABLE '
         --                        || V_SCHEMA (L_V_SCHEMA)
         --                        || '.'
         --                        || L_V_TABLE.TABLE_NAME
         --                        || ' DISABLE CONSTRAINT '
         --                        || V_SCHEMA (L_V_SCHEMA)
         --                        || '.'
         --                        || L_V_TABLE.CONSTRAINT_NAME;
         DBMS_OUTPUT.
          PUT_LINE (
               'ALTER TABLE '
            || V_SCHEMA (L_V_SCHEMA)
            || '.'
            || L_V_TABLE.TABLE_NAME
            || ' DISABLE CONSTRAINT '
            || L_V_TABLE.CONSTRAINT_NAME || ';');
      END LOOP L_V_TABLE;

      FOR L_V_TABLE IN (SELECT TABLE_NAME                     --Truncate Table
                          FROM DBA_TABLES
                         WHERE OWNER = V_SCHEMA (L_V_SCHEMA))
      LOOP
         --                     EXECUTE IMMEDIATE  'TRUNCATE table '
         --                        || V_SCHEMA (L_V_SCHEMA)
         --                        || '.'
         --                        || L_V_TABLE.TABLE_NAME;

         DBMS_OUTPUT.
          PUT_LINE (
               'TRUNCATE table '
            || V_SCHEMA (L_V_SCHEMA)
            || '.'
            || L_V_TABLE.TABLE_NAME || ';');
      END LOOP L_V_TABLE;
   END LOOP;
END;
/

[Oracle RAC ASM] Adding and removing disks in ASM

1/ To add new disk to ASM DISKGROUP. We use one among these commands depend on your case.

ALTER DISKGROUP FRA01 ADD FAILGROUP FAILGROUPA DISK '/ASMDISKS/ASMDISKA28';

ALTER DISKGROUP FRA01 ADD DISK '/DEVICES/ASMDISKA28' REBALANCE POWER 5 WAIT;

ALTER DISKGROUP FRA01 ADD DISK '/DEVICES/ASMDISKA28' FORCE;

ALTER DISKGROUP FRA01 ADD DISK

     '/DEVICES/ASMDISKA28' NAME ASMDISKA28,

     '/DEVICES/ASMDISKA29' NAME ASMDISKA29;

2/ To drop Disk from ASM DISKGROUP. We use one among these commands depend on your case. Note: Oracle recommend that is best if we using command drop and add (replace) will for best balance and performance.

ALTER DISKGROUP DATA01 DROP DISK ASMDISKA28;

ALTER DISKGROUP DATA01 DROP DISK ASMDISKA28 ADD FAILGROUP FAILGRP1 DISK '/DEVICES/ ASMDISKA29' NAME ASMDISKA29;

3/ These commands to view status:

SELECT GROUP_NUMBER, DISK_NUMBER, NAME, TOTAL_MB, FREE_MB, PATH FROM V$ASM_DISK
SELECT DISK_NUMBER, NAME, FAILGROUP, GROUP_NUMBER FROM V$ASM_DISK WHERE GROUP_NUMBER=3 ORDER BY NAME;
SELECT DISK_NUMBER, NAME, FAILGROUP, GROUP_NUMBER FROM V$ASM_DISK;

SELECT GROUP_NUMBER,NAME FROM V$ASM_DISKGROUP;

[IBM AIX] BONDING (Make etherchannel) Network Interface Card

1/ first we must determine network interface card to bonding

- Should speed is the same.
- Good is the same vendor, category version.

2/ Next, we will make bonding 2 card ent0,ent1:

#smitty etherchannel
>Add An EtherChannel / Link Aggregation
 >Select Network Card (Will Primary connection)  (ent0)
 >Mode: 8023ad
 >Backup Adapter (ent1)

->Enter, we will see on the screen new logical interface created naming ent2.

3/ We set ip for logical interface ent2.

#smitty mktcpip
>Select ent2

[IBM AIX] Create iSCSI initiatior

1/ Edit iscsi config:

#vi /etc/iscsi/targets

10.2.103.6 3260 iscsi_target_name

2/ configure the iscsi0 device

#smitty iscsi
>iSCSI Protocol Device
>Change / Show Characteristics
>iSCSI Initiator Name [fill local initiator name: iscsi_initiator_name]
>Discovery Policy: file

3/ Run cfgmgr against the iscsi0 device to detect new volume.

#cfgmgr -l iscsi0

4/ list new volume detected:

#lspv