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;
