Tablespaces:

• Create tablespace:

    CREATE SMALLFILE TABLESPACE "SYSTEMINFODB_TS" DATAFILE'+ORADG01' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 
UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
    ALTER DATABASE DEFAULT TABLESPACE "SYSTEMINFODB_TS";

• Create user:

    CREATE USER "TEST" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "SYSTEMINFODB_TS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
    GRANT "CONNECT" TO "SYSINFODB_AMDIN"
    ALTER USER "SYSINFO_ADMIN" QUOTA UNLIMITED ON "SYSTEMINFODB_TS"

• Disable PL/SQL formatting for creating scripts:

    SET NEWPAGE 0
    SET SPACE 0
    SET LINESIZE 300
    SET PAGESIZE 0
    SET ECHO OFF
    SET FEEDBACK OFF
    SET HEADING OFF
    SET MARKUP HTML OFF
    SET ESCAPE  /

• Shrink tablespaces and tables:

ALTER DATABASE DATAFILE '+TIER_NINE/oradb01/datafile/users.269.635799209' RESIZE 1M;

ALTER TABLE test SHRINK SPACE CASCADE;  --affects al table segments
ALTER TABLE test MODIFY PARTITION test_part SHRINK SPACE; --affects only a partition

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;

• RMAN backup commands

BACKUP AS BACKUPSET DATAFILE '<full path file1>', '<full path file1>', ... ;
BACKUP AS COPY DATABASE; -- backup all datafiles as image copies
BACKUP ARCHIVELOG ALL DELETE INPUT;
BACKUP TABLESPACE t1, t2;
BACKUP CURRENT CONTROLFILE TO '<some path>'; 
BACKUP SPFILE;
BACKUP BACKUPSET ALL; -backups everything

• Redo log switch and system checkpoints parameters

log_checkpoint_timeout -- triggers a log switch and the specified interval (in seconds)

• Procedure to grant rights on various objects. Change logging destinations as required (possible values 1,2,3).

create or replace procedure sys.config_give_permissions (owner IN varchar,
                                                         obj_name IN varchar,
                                                         obj_type IN varchar,
                                                         reader_role IN varchar,
                                                         writer_role IN varchar
                                                        )
as
   gw varchar(256) := 'GRANT SELECT, UPDATE, DELETE, INSERT ON '
                       || owner ||'.'|| obj_name ||
                      ' TO ' || writer_role;
   gr varchar(256) := 'GRANT SELECT ON '
                       || owner ||'.'|| obj_name ||
                      ' TO ' || reader_role;
 begin
   sys.dbms_system.ksdwrt(3, 'OWNER: '|| owner ||' grants write privileges to: '|| writer_role ||' on object: '||obj_name||'('||obj_type||')' );
   execute immediate gw;
   sys.dbms_system.ksdwrt(3, 'OWNER: '|| owner ||' grants read privileges to: '|| reader_role ||' on object: '||obj_name||'('||obj_type||')');
   execute immediate gr;
 exception
   when others then
     null;
 end;
/

• Trigger to set a default schema for a user:

CREATE OR REPLACE TRIGGER sys.config_set_reader_schema
        AFTER logon ON CONFIG_READER.SCHEMA
BEGIN
        execute immediate 'ALTER SESSION SET current_schema=CONFIG_ADMIN';
END;
/

• Trigger to auto grant privileges on various object. Usual user should NEVER have permissions on dbms_system.ksdwrt.

CREATE OR REPLACE TRIGGER sys.config_autogrant_perms_2_roles
    AFTER CREATE ON config_admin.SCHEMA
DECLARE
    job_no number;
BEGIN
      IF DICTIONARY_OBJ_OWNER = 'CONFIG_ADMIN' --AND DICTIONARY_OBJ_TYPE = 'TABLE'
      THEN
          dbms_job.submit(job_no,'sys.config_give_permissions('''
                                           ||DICTIONARY_OBJ_OWNER||''','''
                                           ||DICTIONARY_OBJ_NAME||''','''
                                           ||DICTIONARY_OBJ_TYPE ||
                                           ''',''CONFIGROLE_READER'',''CONFIGROLE_WRITER'');'
                         );
          sys.dbms_system.ksdwrt(3, 'Job '||job_no||' submited to propagate permissions on object '
                                    ||DICTIONARY_OBJ_NAME||'(type='||DICTIONARY_OBJ_TYPE||')'
                                );
      END IF;
END;
/

• Resource allocation

   
      DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.SERVICE_NAME, 'ONLINE', 'HIGH_PRIORITY');

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.set_consumer_group_mapping(
    dbms_resource_manager.oracle_user,
    'TEST',
    NULL
);
dbms_resource_manager.set_consumer_group_mapping(
    dbms_resource_manager.oracle_user,
    'TEST',
    'TEST_GRP'
);
dbms_resource_manager.submit_pending_area();
END;