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;
Category: SQL
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…
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…
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…
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…
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…
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…
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…
Nested Subquery in Oracle
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;
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…