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 |
Table of Contents
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 ;
page revision: 5, last edited: 23 Jun 2017 07:29