Feb 03 2017

Round Up A Number By Protecting 2 Decimal in Oracle

Category: SQLFatih Acar @ 12:04

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

You can use trunc function with add 0.009 to number.

Examples

select trunc(1.224 + 0.009,2) from dual;
1.23
select trunct(1.16 + 0.009,2) from dual;
1.16
select trunct(1.521 + 0.009,2) from dual;
1.53

5,075 total views, 10 views today

Tags: Oracle Administration, Oracle SQL Query, SQL


Jul 04 2012

Show First and Last Value of Data in Oracle

Category: Administration,SQLFatih Acar @ 01:00

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’;

9,592 total views, 5 views today

Tags: Database Administration, Oracle, SQL


Dec 17 2011

Create Fast Refresh for Materialized Views in Oracle

Category: Administration,SQLFatih Acar @ 01:51

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 have to determine which table to perform log.

Create Log Table

SQL> create materialized view log on tablename with sequence,rowid
(column1, column2, column3)
including new values;

Create Materialized View With Fast Refresh Option

SQL> create materialized view mviewname
parallel
build immediate
refresh fast with rowid on demand as
(select column1,column2,column3 from tablename);

First refresh have to be conflict refresh. After you can use fast refresh.

Execute Refresh

SQL> exec dbms_mview.refresh(‘mviewname’,’C’);
SQL> exec dbms_mview.refresh(‘mviewname’,’FAST’);

66,010 total views, 10 views today

Tags: Database Administration, Oracle, SQL


Dec 08 2011

Translate Function in Oracle

Category: SQLFatih Acar @ 23:01

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 2nd character in the replacement_string, and so on. You can use for translation from turkish character to english character.

Example

SQL> select translate(‘Yıldırım Şimşekoğlu’,’ıİşŞçÇöÖüÜ’,’iIsScCoOuU’) from dual;
Result : Yildirim Simsekoglu
SQL> select translate(’12test44′,’14’,’PL’) from dual;
Result : P2testLL

19,812 total views, no views today

Tags: Database Administration, SQL


Nov 17 2011

Drop User With Own Schema in Oracle

Category: Administration,SQLFatih Acar @ 10:46

If you use cascade statement, delete user with own schema.

Example

SQL> Drop User username Cascade;

100,175 total views, 5 views today

Tags: Database Administration, Oracle, SQL


Oct 07 2011

Tracking changes in datas in Oracle

Category: Administration,SQLFatih Acar @ 15:20

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;

7,549 total views, no views today

Tags: Database Administration, Oracle, SQL


Sep 09 2011

Data Dump in PostgreSQL

Category: AdministrationFatih Acar @ 09:11

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 take dump

postgres$ > pg_dump databasename > dumpname.dump.out Note:You can take backup only one database.
postgres$ > pg_dumpall > dumpname.dump.out Note:You can take all database object with this statement.

For restore from dump

postgres$ > psql -f dumpname.dump.out

9,686 total views, no views today

Tags: Database Administration, PostgreSQL, SQL


Jul 31 2011

Learn Free Space of Tablespaces in Oracle

Category: Administration,SQLFatih Acar @ 07:23

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;

9,284 total views, no views today

Tags: Administration, Oracle, SQL


Jul 03 2011

XML file export with stored procedure in Oracle

Category: ProcedureFatih Acar @ 19:50

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 XMLDUMP TO HR;

Create Procedure

CREATE OR REPLACE PROCEDURE PR_XMLEXPORT
IS
CURSOR CRS IS
SELECT first_name,last_name,salary FROM employees;

REC CRS%ROWTYPE;
XML_CONTENT VARCHAR2(10000) := ”;
FILENAME varchar2(100) := ’employees_salary.xml’;
FILEWRITE UTL_FILE.FILE_TYPE;

BEGIN

OPEN CRS;

XML_CONTENT := XML_CONTENT || ‘< ?xml version="1.0" encoding="ISO-8859-9" ? >‘;

LOOP
FETCH CRS INTO REC;
EXIT WHEN CRS%NOTFOUND;

XML_CONTENT := XML_CONTENT || ‘< employee>‘;
XML_CONTENT := XML_CONTENT || ‘< first_name>‘||TO_CHAR(REC.FIRST_NAME)||’< /first_name>‘;
XML_CONTENT := XML_CONTENT || ‘< last_name>‘||TO_CHAR(REC.LAST_NAME)||’< /last_name>‘;
XML_CONTENT := XML_CONTENT || ‘< salary>‘||TO_CHAR(REC.SALARY)||’< /salary>‘;
XML_CONTENT := XML_CONTENT || ‘< /employee>‘;

END LOOP;

XML_CONTENT := XML_CONTENT || ‘< /employees >‘;

FILEWRITE := UTL_FILE.FOPEN(‘XMLDUMP’,’sehirleregorenes.xml’,’w’,32767);
UTL_FILE.PUTF(FILEWRITE,XML_CONTENT);
UTL_FILE.FCLOSE(FILEWRITE);

COMMIT;

CLOSE CRS;

END;

28,641 total views, 10 views today

Tags: Oracle, SQL, Stored Procedure


Jul 02 2011

Recovery committed processing with Flashback in Oracle

Category: Administration,SQLFatih Acar @ 16:58

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 query gives countries table data five minutes ago. You can update or insert query and where statement has this query.

Second Method

alter table countries enable row movement;
flashback table countries to timestamp sysdate-5/1440;
alter table countries disable row movement;

8,533 total views, no views today

Tags: Administration, Oracle, SQL


Next Page »