The translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the…
Alter Profile in Oracle
If you want to change profile parameters, you have to use alter command. Default User Life Time coming limited as 180 in Oracle 11g. You can change of this limit. Because this parameter cause of expired password. If you do not want to expired password, You have to change this parameter. Show Profile Parameters SQL>…
Delete File or Directory from Linux System
rm and rmdir commands uses to delete from linux system. rm command use for delete file. On the other hand, rmdir command uses for delete directory from linux system. Also, rm command can delete directory with -rf option. Firstly, we would create a directory and files for test. create directory and files #> mkdir /testdirectory…
Drop User With Own Schema in Oracle
If you use cascade statement, delete user with own schema. Example SQL> Drop User username Cascade;
Delete Archivelog Using RMAN in Oracle
If your storage is about full, you must either delete old backup and archivelog or move to tape. If you want to delete archivelog from FRA(ASM Storage-Flash Revovery Area) or filesystem for win space, you can use below commands. You can delete archivelog safely, because archivelog deleting does not harm to database. Archivelog List Commands…
Use LogMiner with TOAD in Oracle
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…
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;