Dec 20 2012

Show Data Files Size in Oracle

Category: Administration,SQLFatih Acar @ 17:41

If you want to show tablespace and datafile size, you can use below query. Also, you can add to procedure with schedule job for notification as mail.

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;

10,650 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration


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

10,062 total views, no views today

Tags: Database Administration, Oracle, SQL


Jun 04 2012

Create Trigger in Oracle

Category: Administration,SQLFatih Acar @ 16:40

A database trigger is a stored procedure that automatically executes whenever an event occurs. The event may be insert-delete-update operations. For example, Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has occurred or an ‘AFTER UPDATE’ trigger after an update event has occurred.

You can write triggers that fire whenever one of the following operations occurs:

  • DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
  • DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
  • Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours. You can also use triggers to:

  • Automatically generate derived column values
  • Prevent invalid transactions
  • Enforce complex security authorizations
  • Enforce referential integrity across nodes in a distributed database
  • Enforce complex business rules
  • Provide transparent event logging
  • Provide auditing
  • Maintain synchronous table replicates
  • Gather statistics on table access
  • Modify table data when DML statements are issued against views
  • Publish information about database events, user events, and SQL statements to subscribing applications

Continue reading “Create Trigger in Oracle”

10,532 total views, no views today

Tags: Database Administration, Oracle


May 10 2012

Delete Duplicate Rows Without All Records in Oracle

Category: Administration,Procedure,SQLFatih Acar @ 10:23

If you want to delete duplicate rows but not all rows in table, you can use a procedure. For example, you have 10 duplicate rows and you want to delete 9 rows, then you can use procedure to delete process. For instance, you have a table as tmp_table. tmp_table’s columns are id,name,surname,city,phone. You want to delete duplicate for name,surname,city. You can use below procedure for this process.

Delete Duplicate Rows Procedure

CREATE OR REPLACE PROCEDURE deleteduplicate
IS
BEGIN
FOR duplicates IN ( SELECT max(id) id, name, surname, city
FROM tmp_table GROUP BY name,surname,city
HAVING count(*)>1)

LOOP
DELETE FROM tmp_table t
WHERE t.name = duplicates.name
AND t.surname = duplicates.surname
AND t.city = duplicates.city
AND t.id != duplicates.id;
END LOOP;
COMMIT;
END;

12,516 total views, 5 views today

Tags: Database Administration, Oracle, Stored Procedure


Mar 30 2012

Turkish Letter (i) Convert Problem in Oracle

Category: SQLFatih Acar @ 11:05

When you make converting Turkish chacter (i) with upper function, the result is (I). This is a problem. Because you want to pair two word but you can not pair with using the familiar functions which are upper, lower, initcap.
You can use NLS_UPPER function for the solution to solve this problem.

Example

SELECT NLS_UPPER(‘şampiyon’, ‘NLS_SORT = XTURKISH’) as Result FROM DUAL;

Result : ŞAMPİYON

123,704 total views, no views today


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,495 total views, no views today

Tags: Database Administration, Oracle, SQL


Dec 13 2011

Show Last Refresh Time of Materialized Views in Oracle

Category: Administration,SQLFatih Acar @ 20:37

You can demand last refresh time of materialized views. You can use all_mviews view for show refresh times.

Example

SQL> select last_refresh_date , mview_name from all_mviews;

28,944 total views, no views today

Tags: Database Administration, Oracle


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

20,547 total views, 5 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;

101,850 total views, no 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;

8,009 total views, no views today

Tags: Database Administration, Oracle, SQL


« Previous PageNext Page »