I will show how to round up a number by protecting 2 decimal. When I use round or ceil function, I can not have correct result. I want to round a number upwards with calculate 2 decimal. I want result like below examples. 1.2445 > 1.25 1.21 > 1.21 1.521 > 1.53 1.1 > 1.1…
Tag: SQL
Show First and Last Value of Data in Oracle
You can show first and last value of data which changes with update. Also you can show all values. Example Select versions_starttime, versions_endtime, salary from employees versions between scn minvalue and maxvalue where last_name=’King’;
Create Fast Refresh for Materialized Views in Oracle
You can demand fast refresh for materialized views. Because refresh of tables which have large data are hard. The refresh time is long. Thus, you have to use fast refresh statement for this object. Firstly, You have to create materialized view log for table. If change a data, this changes will write this log. You…
Translate Function in Oracle
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…
Drop User With Own Schema in Oracle
If you use cascade statement, delete user with own schema. Example SQL> Drop User username Cascade;
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;
Data Dump in PostgreSQL
You can use two statement for data dump. These statements are pg_dump and pg_dumpall. pg_dump can has table data, but pg_dumpall can has all database objects these are users, groups, stores and other object, so pg_dumpall has all database object. You can move database from product system to test system with pg_dumpall easyly. Example For…
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;
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…