0 Votes

Tips & Tricks

Last modified by Jeff McDonald on 2024/04/01 11:05

Here are some tips & tricks to avoid trouble in the future...

Grant All Privileges

Seems obvious, but here's how to actually do it.

grant all privileges to <user>;
grant select any dictionary to <user>;

Change to Unlimited Passwords

To prevent passwords from expiring, causing outages in the future:

select username, account_status, EXPIRY_DATE from dba_users;
 

alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;
alter profile DEFAULT limit PASSWORD_REUSE_MAX unlimited;
alter profile DEFAULT limit PASSWORD_GRACE_TIME unlimited;
alter profile DEFAULT limit PASSWORD_LIFE_TIME  unlimited;
alter profile DEFAULT limit FAILED_LOGIN_ATTEMPTS unlimited;
alter profile DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;
alter profile DEFAULT LIMIT PASSWORD_LOCK_TIME .0104;
alter profile DEFAULT LIMIT INACTIVE_ACCOUNT_TIME UNLIMITED UNLIMITED;

Unlock User Accounts

alter user XS$NULL account unlock ;
alter user ANONYMOUS account unlock ;
alter user CTXSYS account unlock ;

SQL*Plus Connection String

To connect to a database without setting the $ORACLE_SID environment variable...

sqlplus system/<password>@database

Change the Data Pump Directory

The data pump directory cannot be chosen from the command line, instead modify the database:

select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME = 'DATA_PUMP_DIR';
CREATE OR REPLACE directory DATA_PUMP_DIR AS '/oracle/SKIAPROD_Export';

Example Import Using Data Pump

impdp 'system/GthG-gjh3@SKIAPROD' dumpfile=exp_SKIA_TRILNT_20200511_01.dmp schemas=TRILNT_TM,TRILNT_KM

impdp 'system/GthG-gjh3@SKIAPROD' dumpfile=exp_SKIA_TRILNT_20200511_01.dmp,exp_SKIA_TRILNT_20200511_02.dmp schemas=TRILNT_TM,TRILNT_KM remap_tablespace=TRILNT_DATA:USERS

Create BIGFILE Tablespaces

create bigfile tablespace TRILNT_DATA
datafile '/ora/app/oracle/oradata/ABERPROD/trilnt_data_01.dbf' size 32G 
autoextend on maxsize unlimited extent management local autoallocate;

create bigfile temporary tablespace TRILNT_TEMP
tempfile '/ora/app/oracle/oradata/ABERPROD/trilnt_temp_01.dbf'
size 16G autoextend on next 1G
extent management local uniform size 1M;

create profile TRILNT_PROFILE limit password_life_time unlimited;

create user TRILNT_TM identified by "TR1LNT-MART1N"
default tablespace TRILNT_DATA
temporary tablespace TRILNT_TEMP
profile TRILNT_PROFILE
account unlock;

grant all privileges to TRILNT_TM;

create user TRILNT_KM identified by "TR1LNT-L3W15"
default tablespace TRILNT_DATA
temporary tablespace TRILNT_TEMP
profile TRILNT_PROFILE
account unlock;

grant all privileges to TRILNT_KM;

create user ABERADMIN identified by "AB3RPR0D-P4UL" profile TRILNT_PROFILE account unlock;

grant all privileges to ABERADMIN with admin option;

 

    

Navigation