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;
Category: SQL
Show First and Last Value of Data in Oracle
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’;
Create Trigger in Oracle
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…
Delete Duplicate Rows Without All Records in Oracle
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…
Turkish Letter (i) Convert Problem in Oracle
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’,…
Create Fast Refresh for Materialized Views in Oracle
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…
Show Last Refresh Time of Materialized Views in Oracle
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;
Translate Function in Oracle
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…
Drop User With Own Schema in Oracle
If you use cascade statement, delete user with own schema. Example SQL> Drop User username Cascade;
Tracking changes in datas in Oracle
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;