ORA DML
main monitor ddl objects dml stato e configurazione rman & dpump pl/sql cluster ASM
main config start-shut process memory monitor diag
user rman & dpump pl/sql cluster ASM

USER

List User Status

set line 200 pages 500
col username for a32
col profile for a24
select username, profile, account_status, lock_date, expiry_date, to_char(created, 'DD-MON-YYYY HH24:MI:SS') created 
from dba_users 
where username like '%RMAN%' 
order by created;

Generate SQL to drop users

set pages 5000 line 200
spool drop_user
select 'drop user ' || username || ' cascade;' uname from dba_users where username like 'RMAN%';
spool off
User: Re-create
drop user "WFM_APP" cascade;
CREATE USER "WFM_APP" IDENTIFIED BY VALUES '0AA3535A4767BF57'
      DEFAULT TABLESPACE "WFM_DATA"
      TEMPORARY TABLESPACE "TEMP"
      PROFILE "FW_USER_APP";

PROFILE

Profile list
SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT';
Alter profile
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
 
ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME UNLIMITED;
 
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME UNLIMITED;

TABLESPACE

Create Tablespace
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  '/ora03_kim/KIM_PRO/temp01.dbf' SIZE 34.358.689.792
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
  CREATE TABLESPACE "DWH_STAGING_OWN_DATA" DATAFILE
  '/ora01_kim/KIM_PRO/dwh_staging_own_data01.dbf' SIZE 4294967296,
  '/ora01_kim/KIM_PRO/dwh_staging_own_data02.dbf' SIZE 10737418240,
  '/ora02_kim/KIM_PRO/dwh_staging_own_data03.dbf' SIZE 10737418240,
  '/ora02_kim/KIM_PRO/dwh_staging_own_data04.dbf' SIZE 10737418240
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;
Drop Tablespace
DROP TABLESPACE TEMPTS1
    INCLUDING CONTENTS 
        CASCADE CONSTRAINTS;

GRANT

GRANT: Esempi
GRANT "CONNECT" TO "MFRARE";
GRANT CREATE VIEW TO "MFRARE";
GRANT CREATE SYNONYM TO "MFRARE";
GRANT ALTER SESSION TO "MFRARE";
Profile: Create
CREATE PROFILE "FW_USER_APP"
    LIMIT
         COMPOSITE_LIMIT DEFAULT
         SESSIONS_PER_USER DEFAULT
         CPU_PER_SESSION DEFAULT
         CPU_PER_CALL DEFAULT
         LOGICAL_READS_PER_SESSION DEFAULT
         LOGICAL_READS_PER_CALL DEFAULT
         IDLE_TIME DEFAULT
         CONNECT_TIME DEFAULT
         PRIVATE_SGA DEFAULT
         FAILED_LOGIN_ATTEMPTS UNLIMITED
         PASSWORD_LIFE_TIME UNLIMITED
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_LOCK_TIME UNLIMITED
         PASSWORD_GRACE_TIME UNLIMITED ;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License