Oracle LogMiner, which is part of Oracle Database, enables you to query online and archived redo log files through a SQL interface. Redo log files contain information about the history of activity on a database. Using Log Miner utility, you can query the contents of online redo log files and archived log files. Because LogMiner…
Category: Oracle
Archivelog Mode Open and Close in Oracle
Most of the High Availability features of Oracle require you to enable ARCHIVELOG mode for your database. When you enable this mode redo logs will be archived instead of overwritten. The archivelogs are stored in a seperate place usually can backed up regularly by your standard filesystem backup system. Archive logs are utilized by RMAN,…
Show and Kill Transaction Lock in Oracle
You can see the transaction lock with enterprise manager or command prompt. The KILL SESSION command doesn’t actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait…
Locations of Critical (important) Files in Oracle
SPFILE on filesystems You can find spfile location with show parameter command. SQL>show parameter spfile; Spfile generally holds in \u01\app\oracle\product\10.2.0\dbhome_1\dbs\spfileorcl.ora on filesystems. on ASM SQL>show parameter spfile; +DATA\orcl\spfileorcl.ora CONTROLFILE on filesystem You can find controlfile location with show parameter command. SQL>show parameter control_files; ControlFile generally holds in \u01\app\oracle\product\10.2.0\oradata\orcl\control01.ctl on filesystems. Oracle has three control file…
Tracking changes in datas in Oracle
If you want to learn the changes in the past of table datas, you can use version identifier in SQL. These identifiers are versions_starttime, versions_endtime. Example select versions_starttime,versions_endtime,salary from employees versions between scn minvalue and maxvalue where employee_id=100;
For Turkish Character Set in Oracle
If you want to use turkish character, you can use ‘WE8ISO8859P9 – West European 8’ character set.
Learn Free Space of Tablespaces in Oracle
You can learn allocate area of tablespaces. You can use data dictionary for this processing. SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes, b.free_bytes FROM dba_data_files a, (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id ORDER BY a.tablespace_name;
Users, Roles and Grants in Oracle
Oracle has predefined accounts which are SYS and SYSTEM user. These users can not be drop. SYS user has all privileges with Admin Option. If you want to connect SYS account, you must use the AS SYSDBA or AS SYSOPER clause. Any user can connect SYSDBA privilege by using the AS SYSDBA clause. Only privileged…
XML file export with stored procedure in Oracle
This example provides export xml file. You must create a directory before create procedure. After, you must give grant for user. Finally, You can create procedure and execute. Create Directory and Grant You must be sys user and can execute this queries; CREATE DIRECTORY XMLDUMP AS ‘C:\XmlDirectoryFileName’; SELECT * FROM DBA_DIRECTORIES; GRANT READ,WRITE ON DIRECTORY…
Recovery committed processing with Flashback in Oracle
Sometimes we want to recovery committed processing. You can use two methods for this task. Scenario : We have Countries table. This table has 5 rows. We deleted one row at this table. After we used commit. But we want to take to back. First Method select * from countries as of timestamp sysdate-5/1440; This…