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

Tags: Administration, Oracle, SQL


Jul 01 2011

Recovery dropped table with Flashback in Oracle

Category: Administration,SQLFatih Acar @ 16:59

If you take to back dropped table, you use flashback statement. The recyclebin must be on to use flashback.

Example

You use this query to see recyclebin status

show parameter recyc;
recyclebin –> on

After

drop table employees;
flashback table employees to before drop;

Recyclebin Table

If you delete a table , This table insert recyclebin table. So this table in fact not delete. Only the table move to recyclebin table.

Example

drop table employees;
select object_name, original_name from recyclebin where original_name=’EMPLOYEES’;

You can see your deleted table.

8,294 total views, no views today

Tags: Administration, Oracle, SQL


Jun 21 2011

Data Dictionary in Oracle

Category: Administration,SQLFatih Acar @ 11:34

The data dictionary is structured in tables and views, but it is also an important tool for all users, from end users to application designers and database administrators. You use SQL statements to access the data dictionary. Because the data dictionary is read-only, you can issue only queries against its tables and views.

The data dictionary has two columns. These columns are table_name and comments.

Example

select table_name , comments from dictionary;

select * from dictionary where table_name = ‘USER_OBJECTS’;

select * from dictionary where lower(comments) = ‘views’;

8,573 total views, no views today

Tags: Administration, Oracle, SQL


Jun 21 2011

ALTER TABLE Statement in Oracle

Category: SQLFatih Acar @ 07:35

If you want to change table, you can use alter table. You can add or drop constraint, add or drop column, modify column name or type, modify table name with alter table statement.

Add, Drop and Modify Column

ALTER TABLE employees ADD (birth_date varchar2(20));

ALTER TABLE employees MODIFY (birth_date date);

ALTER TABLE employees MODIFY (birth_date not null);

ALTER TABLE employees DROP COLUMN birth_date;

Add and Drop Constraint

ALTER TABLE employees ADD CONSTRAINT pk_emp_id PRIMARY KEY(employee_id);

ALTER TABLE employees DROP CONSTRAINT pk_emp_id;

ALTER TABLE employees ADD CONSTRAINT fk_dept_id FOREIGN KEY(department_id) REFERENCES departments(department_id);

ALTER TABLE employees DROP CONSTRAINT fk_dept_id;

ALTER TABLE employees ADD CONSTRAINT ck_salary CHECK (salary > 2000);

ALTER TABLE employees DROP CONSTRAINT ck_salary;

SET UNUSED option

The Set Unused option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. This clause does not actually remove the target columns from each row in the table. Thus, the response time is faster than if you executed the drop clause. After a column has been marked as unused, you have not access to that column. You can use the Drop Unused Columns option to remove the columns that are marked as unused.

ALTER TABLE employees SET UNUSED COLUMN last_name;

ALTER TABLE employees DROP UNUSED COLUMNS;

Give Quota to User

ALTER USER username QUOTA 2G ON USERS;

If you want to add any object or data to database, you have to give ouota to user.

14,563 total views, no views today

Tags: Oracle, SQL


Jun 15 2011

Delete row from table which have constraint column

Category: SQLFatih Acar @ 17:56

You can use two method for this processing. You can either do disable constraint or use on delete cascade or on delete set null in table.

Method 1

Table Name : employees
Constraints : primary_key_employee_id , foreign_key_department_id

If you want to delete row from employees, you must disable foreign key constraint.

ALTER TABLE employees DISABLE CONSTRAINT foreign_key_department_id;

After, you can delete row from employees.If you want to make enable constraint, you can use bottom query.

ALTER TABLE employees ENABLE CONSTRAINT foreign_key_department_id;

Method 2

You must do table alter. You can use on delete cascade or on delete set null.
On Delete Cascade : Deletes the dependent rows in the child table when a row in the parent table is deleted.
On Delete Set Null : Converts the dependent foreign key values to null.

ALTER TABLE employees ON DELETE CASCADE;
ALTER TABLE employees ON DELETE SET NULL;

After, you can delete row from employees.

468,351 total views, no views today

Tags: Oracle, SQL


Jun 15 2011

NVL, NVL2, COALESCE and NULLIF Function in Oracle

Category: SQLFatih Acar @ 03:39

These functions work with any data type and pertain to the use of null values in the expression list. These functions use of null value and returns result value.

NVL : Converts null value to an actual value.
NVL2 : If first expression is not null, return second expression. If first expression is null, return third expression. the first expression can have any data type.
COALESCE : Return first not null expression in the expression list.
NULLIF : Compares two expressions and returns null if they are equal,returns the first expression if they are not equal.

Example

Sample Table : employees

ID SALARY COMMISSION
1 10000 0.05
2 8000 null
3 5000 0.1

select ID, SALARY, (NVL(COMMISSION,1)*SALARY)+SALARY as COM_SALARY from employees;

Result :

ID SALARY COM_SALARY
1 10000 10500
2 8000 8000
3 5000 5500

If you do not use NVL function, when COM_SALARY have a null value, print null.

200,714 total views, no views today


Jun 15 2011

LOWER,UPPER,INITCAP Function in Oracle

Category: SQLFatih Acar @ 02:35

Lower, upper and initcap are case-conversion functions. If you want to change string, you can use this functions.

Lower : Converts alpha character values to lowercase.
Upper : Converts alpha character values to uppercase.
Inıtcap : Converts alpha character values to uppercase for the first letter of each word, all other letters in lowercase.

Example

Default value for name : FATİH Acar

define name = ‘FATİH Acar’;

select LOWER(name) from dual; Result : fatih acar
select UPPER(name) from dual; Result : FATİH ACAR
select INITCAP(name) from dual; Result : Fatih Acar

Other Uses

select * from dual where LOWER(name) like ‘%fatih%’; Result : FATİH Acar
select * from dual where UPPER(name) like ‘%FATİH%’; Result : FATİH Acar
select * from dual where INITCAP(name) like ‘%Fatih%’; Result : FATİH Acar

196,838 total views, no views today

Tags: Oracle, SQL


Jun 12 2011

Nested Subquery in Oracle

Category: SQLFatih Acar @ 16:13

Example

select department_id,
(select first_name from employees where employee_id=10) as “First Name”,
(select last_name from employees where employee_id=20) as “Last Name”,
(select department_name from departments where department_id = 20) as “Department”
from employees where department_id = 20;

13,505 total views, no views today

Tags: Oracle, SQL


Jun 12 2011

Date Functions in Oracle

Category: SQLFatih Acar @ 13:54

Oracle default date format DD-MON-RR (14-MAY-11) for English Language Database. DD/MM/YYYY(14/05/2011) for Turkish. You can only subtraction between two dates. The result is day count.

ROUND and TRUNC for Date

ROUND with ‘MONTH’ => If the day is 16 or more than 16, the date rolls next month and the day makes 1.
ROUND with ‘YEAR’ => If the month is 7 or more than 7, the date rolls next year and the date makes 01-JAN.
TRUNC with ‘MONTH’ => The day makes 01 and the month does not change.
TRUNC with ‘YEAR’ => The day and the month make 01-JAN and the year does not change.

sysdate : 20-JUL-03

ROUND(sysdate,’MONTH’) = 01-AUG-03
ROUND(sysdate,’YEAR’) = 01-JAN-04
TRUNC(sysdate,’MONTH’) = 01-JUL-03
TRUNC(sysdate,’YEAR’) = 01-JAN-03

ADD_MONTHS

You can increase or decrease month with add_months function.

sysdate : 10-APR-08

select add_months(sysdate,1) from dual;

Result : 10-MAY-08
or
select add_months(’15-MAY-11′,-1) from dual;

Result : 15-APR-11

TO_DATE

If you do not know date format, you can use to_date to convert to the format you want. For example; default date format ‘DD-MON-YYYY’ for hire_date column. But you can to_date for ‘dd-mm-yyyy’ date format.

select * from employees where hire_date = to_date(’01-04-2011′ , ‘dd-mm-yyyy’);

14,111 total views, no views today

Tags: Oracle, SQL


Jun 12 2011

Sysdate in Oracle

Category: SQLFatih Acar @ 13:32

Sysdate very important date expression in Oracle. It is very useful date format. Sysdate gives current date.

Example

select sysdate from dual;

Sysdate Functions

Sysdate + 1 =>1 day add
Sysdate – 1 =>1 day decrease
Sysdate + 1/24 =>1 hour add
Sysdate + 1/1440 =>1 minute add

Current Date : 15-JUN-10
select sysdate+2 from dual;
Result Date : 17-JUN-10

16,282 total views, no views today

Tags: Oracle, SQL


« Previous PageNext Page »