Monday, March 25, 2013

Common Oracle Commands

List Services
lsnrctl SERVICES​

Ping TNS
tnsping [servername]

To Reset System Password (command prompt on server)
sqlplus / as sysdba​
alter user system identified by [password];


MAKE SURE TEMPORARY TABLESPACE EXISTS
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 'F:\Oracle 11g\Data\TEMP2.dat' SIZE 1096M AUTOEXTEND ON NEXT 100M;

CREATE PERCEPTION TABLESPACE (MODIFY SIZE BASED ON DMP)
CREATE TABLESPACE [USERNAME] DATAFILE 'F:\Oracle 11g\Data\[USERNAME].dat' SIZE 4096M AUTOEXTEND ON NEXT 100M MAXSIZE 6096M extent management local;

CREATE PERCEPTION USER
CREATE USER [USERNAME] IDENTIFIED BY [PASSWORD] DEFAULT TABLESPACE [USERNAME] TEMPORARY TABLESPACE TEMP2 QUOTA UNLIMITED ON [USERNAME];

ADD PERMISSIONS TO PERCEPTION USER
CREATE ROLE MY_ROLE; 
GRANT ALTER SESSION TO MY_ROLE
GRANT CREATE CLUSTER TO MY_ROLE
GRANT CREATE DATABASE LINK TO MY_ROLE
GRANT CREATE SEQUENCE TO MY_ROLE
GRANT CREATE SYNONYM TO MY_ROLE
GRANT CREATE TABLE TO MY_ROLE
GRANT CREATE VIEW TO MY_ROLE;
GRANT MY_ROLE TO [USERNAME];


IMPORT DMP FILE
Place the dmp file in the location:
C:\app\oracle\admin\orcl\dpdump

Copy to COMMAND PROMPT - not Query Window (run as admin)

To See the Import SQL Statements which will be run and to get Schema Names​
impdp system/[password] DUMPFILE=[file].dmp sqlfile=ImportSql.sql  ​

To Import a specific schema after determining what schemas are in the dmp:
impdp system/[password] DUMPFILE=[file].dmp schemas=[SchemaName] table_exists_action=truncate

Full Import of DMP:
impdp system/[password] FULL=y FILE=[File Name].dmp
(no path on the file name as long as it exists in the dpdump folder)​


*** EXTRA STUFF ***

DROP SCHEMA
DROP USER [USERNAME];

UNLOCK ACCOUNT
SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS;
alter user [USERNAME] account unlock;
grant connect, resource to [USERNAME];

DROP TABLESPACE
DROP TABLESPACE [USERNAME] INCLUDING CONTENTS AND DATAFILES;

Check This Out!

More Links to Good Information