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

Tags: Administration, Oracle, SQL

Jul 05 2011

Users, Roles and Grants in Oracle

Category: AdministrationFatih Acar @ 04:08

Oracle has predefined accounts which are SYS and SYSTEM user. These users can not be drop. SYS user has all privileges with Admin Option. If you want to connect SYS account, you must use the AS SYSDBA or AS SYSOPER clause. Any user can connect SYSDBA privilege by using the AS SYSDBA clause. Only privileged users who are granted the SYSDBA or SYSOPER privilege are allowed to STARTUP and SHUTDOWN the database instance.

Create and Drop User

DROP USER User_Name;

Create and Drop Role

DROP ROLE Role_Name;

Grant and Revoke

GRANT create table,create sequence,create session,create procedure,create view to User_Name (User,Role,Public) [WITH GRANT OPTION]

Note : If you use WITH GRANT OPTION, This user can give own role to other user.

GRANT update(department_id,department_name) ON departments TO Role_Name;

REVOKE create table FROM User_Name;

9,730 total views, no views today

Tags: Administration, Oracle

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;

9,098 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.


You use this query to see recyclebin status

show parameter recyc;
recyclebin –> on


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.


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

You can see your deleted table.

8,799 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.


select table_name , comments from dictionary;

select * from dictionary where table_name = ‘USER_OBJECTS’;

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

9,173 total views, no views today

Tags: Administration, Oracle, SQL