Recovery dropped table with Flashback in Oracle

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.

7,714 total views, 10 views today

Data Dictionary in Oracle

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

7,908 total views, no views today

ALTER TABLE Statement in Oracle

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.

13,918 total views, no views today

Delete row from table which have constraint column

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.

460,766 total views, 30 views today

NVL, NVL2, COALESCE and NULLIF Function in Oracle

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.

192,299 total views, 40 views today

LOWER,UPPER,INITCAP Function in Oracle

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

192,553 total views, 15 views today

Date Functions in Oracle

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

13,526 total views, no views today

Sysdate in Oracle

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

15,682 total views, 5 views today

External value in SQL

If you want to use external value in SQL, you must use && or & expressions. You can use external value in column name, table name or variable.

Example

select * from employees where employee_id = &employeeid;

If you use to double expression (&&), this value makes a cache. You can use this cached value with single expression (&). But when your SQL Query is finished, you must do undefine this cached value. Because this variable is read in cache everytime. Your changes are not influence for variable.

Example

select &&column_name from employees where &column_name = &variable;

undefine column_name;

&&column_name wants one column name in employees table and make cache your value. After the system uses your value in &column_name. &variable is noncache. Because single expression (&).

23,766 total views, 5 views today