Tablespaces:
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 "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;
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;
/
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