Friday, July 10, 2015
Monday, March 23, 2015
Displays the SQL statements for currently running processes
SELECT s.sid,
s.status "Status",
p.spid "Process",
s.schemaname " Name",
s.osuser "OS User",
Substr(a.sql_text,1,100) "SQL Text file",
s.program "Program"
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr;
SID Status Process Schema Name OS User SQL Text Program
---------- -------- ------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------
125 ACTIVE 3132 SYS SYSTEM ORACLE.EXE (PMON)
187 ACTIVE 3136 SYS SYSTEM ORACLE.EXE (VKTM)
1 ACTIVE 3140 SYS SYSTEM ORACLE.EXE (GEN0)
64 ACTIVE 3144 SYS SYSTEM ORACLE.EXE (DIAG)
126 ACTIVE 3148 SYS SYSTEM ORACLE.EXE (DBRM)
188 ACTIVE 3152 SYS SYSTEM ORACLE.EXE (PSP0)
2 ACTIVE 3156 SYS SYSTEM ORACLE.EXE (DIA0)
65 ACTIVE 3160 SYS SYSTEM ORACLE.EXE (MMAN)
127 ACTIVE 3168 SYS SYSTEM ORACLE.EXE (DBW0)
189 ACTIVE 3172 SYS SYSTEM ORACLE.EXE (LGWR)
3 ACTIVE 3176 SYS SYSTEM ORACLE.EXE (CKPT)
66 ACTIVE 3180 SYS SYSTEM ORACLE.EXE (SMON)
128 ACTIVE 3184 SYS SYSTEM ORACLE.EXE (RECO)
192 ACTIVE 3188 SYS SYSTEM ORACLE.EXE (MMON)
4 ACTIVE 3192 SYS SYSTEM ORACLE.EXE (MMNL)
191 ACTIVE 3856 SYS SYSTEM ORACLE.EXE (Q000)
14 ACTIVE 3512 SYS SYSTEM ORACLE.EXE (SMCO)
72 ACTIVE 3104 SYS SYSTEM ORACLE.EXE (W000)
135 ACTIVE 7768 SYSTEM Prabhakar SELECT s.sid, s.status "Status", p.spid "Process", s.schemaname "Schema Name", s.osuser "OS SQL Developer
194 ACTIVE 3264 SYS SYSTEM ORACLE.EXE (ARC0)
5 ACTIVE 3284 SYS SYSTEM ORACLE.EXE (ARC1)
63 ACTIVE 3288 SYS SYSTEM ORACLE.EXE (ARC2)
129 ACTIVE 3292 SYS SYSTEM ORACLE.EXE (ARC3)
195 ACTIVE 3328 SYS SYSTEM ORACLE.EXE (QMNC)
8 ACTIVE 3852 SYS SYSTEM ORACLE.EXE (CJQ0)
74 ACTIVE 3884 SYS SYSTEM ORACLE.EXE (J000)
133 ACTIVE 6252 SYS SYSTEM ORACLE.EXE (J001)
197 ACTIVE 3860 SYS SYSTEM ORACLE.EXE (Q001)
28 rows selected
s.status "Status",
p.spid "Process",
s.schemaname " Name",
s.osuser "OS User",
Substr(a.sql_text,1,100) "SQL Text file",
s.program "Program"
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr;
SID Status Process Schema Name OS User SQL Text Program
---------- -------- ------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------
125 ACTIVE 3132 SYS SYSTEM ORACLE.EXE (PMON)
187 ACTIVE 3136 SYS SYSTEM ORACLE.EXE (VKTM)
1 ACTIVE 3140 SYS SYSTEM ORACLE.EXE (GEN0)
64 ACTIVE 3144 SYS SYSTEM ORACLE.EXE (DIAG)
126 ACTIVE 3148 SYS SYSTEM ORACLE.EXE (DBRM)
188 ACTIVE 3152 SYS SYSTEM ORACLE.EXE (PSP0)
2 ACTIVE 3156 SYS SYSTEM ORACLE.EXE (DIA0)
65 ACTIVE 3160 SYS SYSTEM ORACLE.EXE (MMAN)
127 ACTIVE 3168 SYS SYSTEM ORACLE.EXE (DBW0)
189 ACTIVE 3172 SYS SYSTEM ORACLE.EXE (LGWR)
3 ACTIVE 3176 SYS SYSTEM ORACLE.EXE (CKPT)
66 ACTIVE 3180 SYS SYSTEM ORACLE.EXE (SMON)
128 ACTIVE 3184 SYS SYSTEM ORACLE.EXE (RECO)
192 ACTIVE 3188 SYS SYSTEM ORACLE.EXE (MMON)
4 ACTIVE 3192 SYS SYSTEM ORACLE.EXE (MMNL)
191 ACTIVE 3856 SYS SYSTEM ORACLE.EXE (Q000)
14 ACTIVE 3512 SYS SYSTEM ORACLE.EXE (SMCO)
72 ACTIVE 3104 SYS SYSTEM ORACLE.EXE (W000)
135 ACTIVE 7768 SYSTEM Prabhakar SELECT s.sid, s.status "Status", p.spid "Process", s.schemaname "Schema Name", s.osuser "OS SQL Developer
194 ACTIVE 3264 SYS SYSTEM ORACLE.EXE (ARC0)
5 ACTIVE 3284 SYS SYSTEM ORACLE.EXE (ARC1)
63 ACTIVE 3288 SYS SYSTEM ORACLE.EXE (ARC2)
129 ACTIVE 3292 SYS SYSTEM ORACLE.EXE (ARC3)
195 ACTIVE 3328 SYS SYSTEM ORACLE.EXE (QMNC)
8 ACTIVE 3852 SYS SYSTEM ORACLE.EXE (CJQ0)
74 ACTIVE 3884 SYS SYSTEM ORACLE.EXE (J000)
133 ACTIVE 6252 SYS SYSTEM ORACLE.EXE (J001)
197 ACTIVE 3860 SYS SYSTEM ORACLE.EXE (Q001)
28 rows selected
Thursday, February 26, 2015
how to Processing SQL in Oracle
The following steps show how Oracle processes SQL
1. Statement is passed to Oracle for processing
2. Before it is placed in the library cache, a hash value is computed that
represent s a number of characteristics of the SQL.
3. Oracle compares the computed hash value against those values in a hash
table where it maintains for SQL statements already in the cache.
4. If a match is found, the new SQL statement is thrown away and the one
sitting in the cache is executed on its behalf.
5. In no match is found, further processing is done on the new SQL
statement, an entry is made in the library cache hash table for newly
arrived code, and it is placed in the library cache.
6. There are 3 stages of SQL processing – parse, execute and fetch
· During parsing, Oracle server checks the syntax and validates
table, column names against data dictionary
· Determines whether user has privilege to execute the statement
· Determines optimal execution plans for statement
· Finds a shared SQL area for the statement
· In execution stage, for UPDATE and DELETE statement, Oracle
locks the affected rows, looks for data blocks in DB buffer cache,
if found, executes becomes faster, if not then Oracle has to read
from physical data files to buffer cache. For SELECT and INSERT
statements, locking is not necessary.
· During fetch operation, rows are fetched to user process.
1. Statement is passed to Oracle for processing
2. Before it is placed in the library cache, a hash value is computed that
represent s a number of characteristics of the SQL.
3. Oracle compares the computed hash value against those values in a hash
table where it maintains for SQL statements already in the cache.
4. If a match is found, the new SQL statement is thrown away and the one
sitting in the cache is executed on its behalf.
5. In no match is found, further processing is done on the new SQL
statement, an entry is made in the library cache hash table for newly
arrived code, and it is placed in the library cache.
6. There are 3 stages of SQL processing – parse, execute and fetch
· During parsing, Oracle server checks the syntax and validates
table, column names against data dictionary
· Determines whether user has privilege to execute the statement
· Determines optimal execution plans for statement
· Finds a shared SQL area for the statement
· In execution stage, for UPDATE and DELETE statement, Oracle
locks the affected rows, looks for data blocks in DB buffer cache,
if found, executes becomes faster, if not then Oracle has to read
from physical data files to buffer cache. For SELECT and INSERT
statements, locking is not necessary.
· During fetch operation, rows are fetched to user process.
Background processes in oracle
Database Writer (DBWn)
:It is responsible to redo log buffer management. Almost all activities against the database are tracked in the online redo logs. As transaction are initiated and
eventually committed or rolled back, a record of this activity is written to these
log files.
Checkpoint (CKPT):
A checkpoint is an event that flushes modified data from buffer cache to disk and updates control file and data files. The CKPT process updates header of data files and control files and
DBWn writes actual blocks to file. Checkpoint occurs automatically when an
online redo log file fills (log switch).
System Monitor (SMON):
At startup, SMON’s job is to ensure that all the database files are consistent and
perform recovery if required. There is also an assortment of other cleanup
activities that may need to be done, which are SMON’s responsibility. The
SMON process by itself checks every so often to see whether there are any tasks
waiting for its attention.
Process Monitor (PMON):
Cleans up failed user processes and frees all resources used by failed process.
Archiver (ARCn):
It automatically saves copies of redo logs in a DBA specified storage location
when media recovery is enabled.
Recover (RECO):
Is used with distributed transaction to resolve failure.
:It is responsible to redo log buffer management. Almost all activities against the database are tracked in the online redo logs. As transaction are initiated and
eventually committed or rolled back, a record of this activity is written to these
log files.
Checkpoint (CKPT):
A checkpoint is an event that flushes modified data from buffer cache to disk and updates control file and data files. The CKPT process updates header of data files and control files and
DBWn writes actual blocks to file. Checkpoint occurs automatically when an
online redo log file fills (log switch).
System Monitor (SMON):
At startup, SMON’s job is to ensure that all the database files are consistent and
perform recovery if required. There is also an assortment of other cleanup
activities that may need to be done, which are SMON’s responsibility. The
SMON process by itself checks every so often to see whether there are any tasks
waiting for its attention.
Process Monitor (PMON):
Cleans up failed user processes and frees all resources used by failed process.
Archiver (ARCn):
It automatically saves copies of redo logs in a DBA specified storage location
when media recovery is enabled.
Recover (RECO):
Is used with distributed transaction to resolve failure.
Saturday, February 21, 2015
Steps to Configure database in archive log mode in oracle
SQL> select NAME,LOG_MODE from v$database;
NAME LOG_MODE
——— ————
ORCL NOARCHIVELOG
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 281018832 bytes
Database Buffers 247463936 bytes
Redo Buffers 5804032 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL> select name,log_mode from V$database;
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
database is configured in archive log mode.
Monday, February 2, 2015
What are the types of SQL statement in Oracle .
Data Definition Language: CREATE, ALTER, DROP, TRUNCATE, REVOKE, NO AUDIT & COMMIT.
Data Manipulation Language: INSERT, UPDATE, DELETE, LOCK TABLE, EXPLAIN PLAN & SELECT.
Transactional Control: COMMIT & ROLLBACK
Session Control: ALTERSESSION & SET ROLE
System Control: ALTER SYSTEM.
How to Increase Import Size Limit in phpMyAdmin
modified the
php.ini.upload_max_filesize = 2M to upload_max_filesize = 200M
Subscribe to:
Posts (Atom)