Database Shutdown Modes

1-NORMAL
No new connections made on the database and oracle waits for all users to disconnect thier sessions, closes and dismounts database before instance shutdown, no instance recovery required upon next db startup.

2-IMMEDIATE
Rolls back all current uncommitted transactions and disconnects users sessions, closes and dismounts database before instance shutdown, no instance recovery required upon next db startup.

3-TRANSACTIONAL
No new connections, users disconnected when their current transactions are completed.

4-ABORT
All transactions are forcibly terminated, no transactions are rolled back, instance terminated without closing the data files, next startup will require instance recovery.

Using Configurationless Connections To Connect to Database

Configurationless connections (Direct Connect)enable you to directly connect to the database server without having a service configured in the TNSNAMES.ORA file.

Syntax:
sqlplus username/password@[//][hostname][:db_port][/db_sid]

for example:
sqlplus scott/tiger@//192.168.1.100:1521/orcl

where:
- scott/tiger : username and password of the schema you will connect to.
- 192.168.1.100 : the IP Address for the database server.
- 1521 : database port.
- orcl : db_sid

In order for this to work, you need to add a parameter to the sqlnet.ora file on the database server in this path $ORACLE_HOME/network/admin

NAMES.DIRECTORY_PATH=(ezconnect,tnsnames)

External Tables In Oracle

External tables is a very useful feature in Oracle that allows Oracle to query data that is stored outside the database in flat files.It uses the ORACLE_LOADER driver which can access any data stored in any format that can be loaded by SQL*Loader.
- No DML can be performed on external tables but they can be used for query, join and sort operations.
- Views and synonyms can be created against external tables.
- They are useful in Data Warehousing Enviroments since the data doesn't need to be staged but can be queried in parallel.
- They should not be used for frequently queried tables.

To use External Tables in Oracle:

1- You must first create a directory to place the files you want to load into database in.

SQL> CONNECT / AS SYSDBA
SQL> CREATE OR REPLACE DIRECTORY EXTENAL_TABLES_DIR AS 'C:\TEST_EXT_TABLES';

2- Create the metadata for the External Table using the CREATE TABLE..ORGANIZATION EXTERNAL command

SQL> CREATE TABLE SCOTT.EMP_EXT_TABLE (
EMP_NAME VARCHAR2 (128),
EMP_SALARY NUMBER,
EMP_JOB VARCHAR2 (128)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTENAL_TABLES_DIR
ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ' ' )
LOCATION ( 'emp_data.log' )
)
REJECT LIMIT UNLIMITED;

Where:
- DEFAULT DIRECTORY: the directory name you created in Step1.
- ACCESS PARAMETERS: the column and line separators for data in the file.
- LOCATION: the name of the file containing data.
- REJECT LIMIT: is set to UNLIMITED for SELECT on table not to fail.

3- You can query the data in the SCOTT.EMP_EXT_TABLE table
SQL> SELECT * FROM SCOTT.EMP_EXT_TABLE;

4- Now you can create views against this table like this:
SQL> CREATE OR REPLACE VIEW SCOTT.EMP_EXT_TABLE_VU AS
SELECT * FROM SCOTT.EMP_EXT_TABLE;

How to Drop and Create Oracle EM On MS Windows

Steps used to drop and create Oracle EM on MS Windows:

1-Make sure the DB Console service is stopped. If not, try to stop it.

2-Delete the service:
sc delete [service_name]
where [service_name] is the DB Control service name (typically:OracleDBConsole)

3-Check if the service is deleted now. If not, reboot the whole server.

4-Remove the following directories from your filesystem:
[ORACLE_HOME]/[HOSTNAME_SID]
[ORACLE_HOME]/oc4j/j2ee/OC4J_DBConsole_[HOSTNAME_SID]

5-Logon SQLPLUS as user SYS or SYSTEM, and drop the SYSMAN account and management objects:
drop user sysman cascade;
drop role MGMT_USER;
drop user MGMT_VIEW cascade;
drop public synonym MGMT_TARGET_BLACKOUTS;
drop public synonym SETEMVIEWUSERCONTEXT;

6-Rebuild the EM Console using this command from Command Prompt:
EMCA -config dbcontrol db -repos create

Avoid logging in / as sysdba

To avoid a user to connect to Database using connect /as sysdba you can edit the sqlnet.ora file and change the value of parameter SQLNET.AUTHENTICATION_SERVICES from (NTS) to (NONE)

SQLNET.AUTHENTICATION_SERVICES=(NONE)

Enable Archiving In Oracle Database

To enable archivelog mode for oracle database:

SQL> CONN SYS AS SYSDBA
SQL> ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='location=C:\archive' SCOPE=SPFILE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='arch_%t_%s.arc' SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ALTER SYSTEM SWITCH LOGFILE;

Find all tables with no primary key in a specific schema

Script:
select TABLE_NAME
from DBA_TABLES
WHERE OWNER='SCOTT'
MINUS
select TABLE_NAME
from DBA_CONSTRAINTS
where owner='SCOTT' and CONSTRAINT_TYPE='P';


Sample output

TABLE_NAME
-----------
BONUS
PLAN_TABLE
SALGRADE

Get the current Session ID

SELECT TO_NUMBER(SUBSTR(dbms_session.unique_session_id,1,4),'XXXX') SID#
from dual;