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 system checkpoint at the specified intervals (in seconds)

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;

-- RaduStoica - 19 Sep 2007
Edit | Attach | Watch | Print version | History: r8 < r7 < r6 < r5 < r4 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r8 - 2007-10-17 - RaduStoica
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    LHCb All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback