Subquery versus Joins:
The main difference between subquery and join is:
• subquery is faster when we have to retrieve data from large number of tables. Because it becomes tedious to join more tables. Join is faster to retrieve data from database when we have less number of tables
• subquery retrieve the data depending on certain condition or manipulation in inner query. Where as joins will join the entire data depending on the conditions.
While using Order by Operator:
• Don't use ORDER BY in your SELECT statements unless you really need to, as it adds a lot of extra overhead.
While Using AND operator:
If you have a WHERE clause that includes expressions connected by two or more AND operators, Oracle Server will evaluate them from left to right in the order they are written.
Locate the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time. If both parts of an AND expression are equally likely being false, put the least complex AND expression first.
Using Where Clause:
• When you have a choice of using the IN or the EXISTS clause in your SQL, Use the EXISTS clause, as it is usually more efficient and its performance is good.
• If you use LIKE in your WHERE clause, try to use one or more leading character in the clause, if at all possible For example, use: LIKE 'm%' not: LIKE '%m‘.
• If you use a leading character in your LIKE clause, then the Query Optimizer has the ability to potentially use an index to perform the query.
• If the leading character in a LIKE clause is a wildcard, the Query Optimizer will not be able to use an index, and a table scan must be run, which will reduce performance and will take more time.
• When you have a choice of using the IN or the BETWEEN clauses in your SQL, use the BETWEEN clause, as it is much more efficient.
• For example:
SELECT emp_id, emp_name FROM employee WHERE emp_id IN (1000, 1001, 1002, 1003, 1004);
is much less efficient than this:
SELECT emp_id, emp_name FROM employee WHERE emp_id BETWEEN 1000 and 1004;
If your SELECT statement includes an IN operator along with a list of values to be tested in the query :-
• Order the list of values so that the most frequently found values are placed at the first of the list, and the less frequently found values are placed at the end of the list.
• This can speed performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes.
Returning data from Select Statement:
• Do not return the column data that you do no need in you queries.
• You should avoid using SELECT * when returning data from tables.
• Select only the columns that you need. For example, you should not use SELECT * to return all the columns from a table if you don't need all the data from each column.
• In addition, using SELECT * prevents the use of covered indexes, further potentially hurting query performance.
Using the UNION statement:
• The UNION statement performs equivalent of a SELECT DISTINCT on the final result set.
• UNION takes the results of two recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. Example :-
SELECT column_name1, column_name2 FROM table_name1 WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2 FROM table_name1 WHERE column_name2 = some_value;
• You can write the same query as:
SELECT DISTINCT column_name1, column_name2 FROM table_name1 WHERE column_name1 = some_value OR column_name2 = some_value
It will boost the performance of your sql queries.
Wednesday, July 29, 2009
Wednesday, July 15, 2009
ORACLE Recyclebin
There are two recyclebin views present in ORACLE :
1. USER_RECYCLEBIN and
2. DBA_RECYCLEBIN.
For convenience, when we are firing queries at RECYCLEBIN which is a public synonym that points to USER_RECYCLEBIN. The RECYCLEBIN is enabled by default in 10g, but one can turn it on or off with the RECYCLEBIN init parameter, at the session level or system level.
Let’s say RECYCLEBIN is enabled, any tables that we drop are not actually getting deleted. Instead, Oracle just renames that particular table which has been dropped and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.
For example, consider this simple table:
SQL> Create table test (num number);
Table created.
SQL> Insert into test values (1);
1 row created.
SQL> Insert into test values (2);
1 row created.
SQL> Insert into test values (3);
1 row created.
SQL> Select * from test;
NUM
----------
1
2
3
If the RECYCLEBIN init parameter is set to ON (the default in 10g), then the dropped table will placed in the RECYCLEBIN:
SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_DROP CAN_PURGE DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- ------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:50:33
What has happened to the table is that when we dropped it that has got renamed. The table data is still there and can be queried just like a normal table:
Note: the table name must be in quotes (“ “)
SQL> select * from "BIN$brwK19W3F/7gRAADuhyf0w==$0";
NUM
----------
1
2
3
Since the table data is still there, we can "undrop" the table. This operation is known as a "flashback drop". This operation will simply rename the BIN$... table to its original name:
SQL> Flashback table "BIN$brwK19W3F/7gRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
NUM
----------
1
2
3
It's important to know that after you've dropped a table, it has only been renamed; the table segments still exist there in your tablespace, unchanged, occupying space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
Object_name original_name type can_undrop can_purge droptime
--------------------------------------------------------------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-09:12:26:22
SQL> purge table “BIN$brwK19W3F/7gRAADuhyf0w==$0” ;
Table purged.
SQL> select * from recyclebin;
No rows returned.
You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER.
Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.
Dealing with dropped table versions
Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin also. For example, if we create and drop the TEST table twice, we'll have two versions in the recyclebin:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values('Version 2');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime fro
m recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brrGkpUUF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:05
BIN$brrGkpUVF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:31
Now query the two dropped tables to verify that whether they are different:
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we are going to issue a FLASHBACK DROP command for TEST, which version will Oracle restore?
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 2
Oracle will always restore the most recent version of the dropped object. If we want the previous version then we can select the new BIN$... name of the TEST from the recyclebin and we can perform flashback query operation on the BIN$... table as follows. This will restore the required version of the TEST table.
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we want to restore the Version 1 table then we need to capture the BIN$... name of the TEST table i.e.,
BIN$brrGkpUUF7vgRAADuhyf0w==$0
Now issue the flash back query as follows it will restore the required version of the TEST table.
SQL> flashback table "BIN$brrGkpUUF7vgRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 1
Going with DEPENDENT OBJECTS
In a modern RDBMS, Most will have indexes, constraints, and/or triggers some stand alone. When a table is dropped ORALCE drops the dependent objects associated with it. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, will be invalidated.)
For example:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> create index ind_test_col on test(test_col);
Index created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WxF/7gRAADuhyf0w==$0 IND_TEST_COL INDEX NO YES 2009-07-15:10:18:46 53649 53650
BIN$brwK19WyF/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:18:46 53649 53649
SQL> flashback table test to before drop;
Flashback complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
no rows selected
The RECYCLEBIN views have a few other columns that make the relationship between TEST and IND_TEST_COL clear:
The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TEST_COL is 53650. Note the value of the BASE_OBJECT column for IND_TEST_COL: 53649, the object number of the associated version of the TEST table.
If we FLASHBACK DROP the TEST table, its index will be restored - but Oracle will not rename it to its original name. It will retain its BIN$.. name:
SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------
BIN$brwK19WxF/7gRAADuhyf0w==$0
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WzF/7gRAADuhyf0w==$1 BIN$brwK19WxF/7gRAADuhyf0w==$0 INDEX NO YES 2009-07-15:10:43:33
BIN$brwK19W0F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:43:33 53649 53649
Note the values in the CAN_UNDROP and CAN_PURGE columns for the index. An index cannot be undropped without the table - so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:
SQL> purge index "BIN$brwK19WzF/7gRAADuhyf0w==$1";
Index purged.
Now, if we restore the table, it will be restored without the index:
SQL> select index_name from user_indexes where table_name='TEST';
no rows selected
LIMITATIONS
A few types of dependent objects are not handled like the simple index above.
o Bitmap join indexes are not put in the recyclebin when their base table is dropped, and not retrieved when the table is restored with FLASHBACK DROP.
o The same applies for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
o Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.
If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.
1. USER_RECYCLEBIN and
2. DBA_RECYCLEBIN.
For convenience, when we are firing queries at RECYCLEBIN which is a public synonym that points to USER_RECYCLEBIN. The RECYCLEBIN is enabled by default in 10g, but one can turn it on or off with the RECYCLEBIN init parameter, at the session level or system level.
Let’s say RECYCLEBIN is enabled, any tables that we drop are not actually getting deleted. Instead, Oracle just renames that particular table which has been dropped and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.
For example, consider this simple table:
SQL> Create table test (num number);
Table created.
SQL> Insert into test values (1);
1 row created.
SQL> Insert into test values (2);
1 row created.
SQL> Insert into test values (3);
1 row created.
SQL> Select * from test;
NUM
----------
1
2
3
If the RECYCLEBIN init parameter is set to ON (the default in 10g), then the dropped table will placed in the RECYCLEBIN:
SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_DROP CAN_PURGE DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- ------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:50:33
What has happened to the table is that when we dropped it that has got renamed. The table data is still there and can be queried just like a normal table:
Note: the table name must be in quotes (“ “)
SQL> select * from "BIN$brwK19W3F/7gRAADuhyf0w==$0";
NUM
----------
1
2
3
Since the table data is still there, we can "undrop" the table. This operation is known as a "flashback drop". This operation will simply rename the BIN$... table to its original name:
SQL> Flashback table "BIN$brwK19W3F/7gRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
NUM
----------
1
2
3
It's important to know that after you've dropped a table, it has only been renamed; the table segments still exist there in your tablespace, unchanged, occupying space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
Object_name original_name type can_undrop can_purge droptime
--------------------------------------------------------------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-09:12:26:22
SQL> purge table “BIN$brwK19W3F/7gRAADuhyf0w==$0” ;
Table purged.
SQL> select * from recyclebin;
No rows returned.
You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER.
Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.
Dealing with dropped table versions
Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin also. For example, if we create and drop the TEST table twice, we'll have two versions in the recyclebin:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values('Version 2');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime fro
m recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brrGkpUUF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:05
BIN$brrGkpUVF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:31
Now query the two dropped tables to verify that whether they are different:
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we are going to issue a FLASHBACK DROP command for TEST, which version will Oracle restore?
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 2
Oracle will always restore the most recent version of the dropped object. If we want the previous version then we can select the new BIN$... name of the TEST from the recyclebin and we can perform flashback query operation on the BIN$... table as follows. This will restore the required version of the TEST table.
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we want to restore the Version 1 table then we need to capture the BIN$... name of the TEST table i.e.,
BIN$brrGkpUUF7vgRAADuhyf0w==$0
Now issue the flash back query as follows it will restore the required version of the TEST table.
SQL> flashback table "BIN$brrGkpUUF7vgRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 1
Going with DEPENDENT OBJECTS
In a modern RDBMS, Most will have indexes, constraints, and/or triggers some stand alone. When a table is dropped ORALCE drops the dependent objects associated with it. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, will be invalidated.)
For example:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> create index ind_test_col on test(test_col);
Index created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WxF/7gRAADuhyf0w==$0 IND_TEST_COL INDEX NO YES 2009-07-15:10:18:46 53649 53650
BIN$brwK19WyF/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:18:46 53649 53649
SQL> flashback table test to before drop;
Flashback complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
no rows selected
The RECYCLEBIN views have a few other columns that make the relationship between TEST and IND_TEST_COL clear:
The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TEST_COL is 53650. Note the value of the BASE_OBJECT column for IND_TEST_COL: 53649, the object number of the associated version of the TEST table.
If we FLASHBACK DROP the TEST table, its index will be restored - but Oracle will not rename it to its original name. It will retain its BIN$.. name:
SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------
BIN$brwK19WxF/7gRAADuhyf0w==$0
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WzF/7gRAADuhyf0w==$1 BIN$brwK19WxF/7gRAADuhyf0w==$0 INDEX NO YES 2009-07-15:10:43:33
BIN$brwK19W0F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:43:33 53649 53649
Note the values in the CAN_UNDROP and CAN_PURGE columns for the index. An index cannot be undropped without the table - so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:
SQL> purge index "BIN$brwK19WzF/7gRAADuhyf0w==$1";
Index purged.
Now, if we restore the table, it will be restored without the index:
SQL> select index_name from user_indexes where table_name='TEST';
no rows selected
LIMITATIONS
A few types of dependent objects are not handled like the simple index above.
o Bitmap join indexes are not put in the recyclebin when their base table is dropped, and not retrieved when the table is restored with FLASHBACK DROP.
o The same applies for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
o Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.
If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.
Thursday, July 9, 2009
Finding the “Floating” calendar dates:
Using the SQL we can find the floating calendar dates, such as the second Tuesday of the month, with modular arithmetic and brute force reasoning.
To calculate the second Tuesday of the month, this depends on the day of the week of the first day of the month. But we need to know which month is this? Obviously, all we need to know are the month and year, but it’s easier if we start with a date as such. For testing purposes, the following table can be used.
Create table month_date (month_date date);
Insert into month_date values(date ‘2009-04-01’);
Insert into month_date values(date ‘2009-05-01’);
Insert into month_date values(date ‘2009-06-01’);
Insert into month_date values(date ‘2009-07-01’);
So, given a date, the first day of a month, what date is the second Tuesday of that month? The process begins by calculating the day of the week for the first day of the month.
Day-of-week function:
Even though standard SQL doesn’t provide any function to give the day of the week for any date, most of the database systems do.
ORACLE provides a function:
TO_CHAR(DATE,’D’);
The essence of this function is that it will return a number between 0 and 6 or 1 and 7.Sometimes 0 or 1 is Sunday and 6 or 7 is Saturday, and sometime 0 or 1 is Monday and 6 or 7 is Sunday. This depends on how the NLS_TERRITORY parameter is set.
Note: We follow that 1 is Sunday and 7 is Saturday in our example.
-----
The Formula:
To convert the first day of the month into second Tuesday of the month simply involves manipulating the day of the week of the first day with in an arithmetic formula. Before we see the formula, we should know what happens when the first day of the month falls on each day of the week from Sunday through Saturday.
Lets see this:
1. A Sunday, the third is a Tuesday, so the tenth is the second Tuesday
2. A Monday, the second is Tuesday, so the ninth is the second Tuesday
3. A Tuesday, the eighth is the second Tuesday
4. A Wednesday, the seventh is the next Tuesday and fourteenth is the second Tuesday.
5. A Thursday, the sixth is the next Tuesday and thirteenth is the second Tuesday
6. A Friday, the fifth is the next Tuesday and the twelfth is the second Tuesday
7. A Saturday, the fourth is the next Tuesday and the eleventh is the second Tuesday
This gives us all the possibilities. So now we have to reduce these facts to formula.
The first column (A) is the day of the week of the first day of the month; and the second column is the numerical equivalent of this day using the range from 1 to 7.
The important data in the above table is the last column; which is the number of days to add to the date of the first day of the month.
So, the formula can be derived as follows.
1. Find B, the day of the week of the first day of the month, using
1-Sunday……7-Saturday.
2. Subtract this number from 10 to get C:
With Sunday -1 …. Saturday – 7, Tuesday would be 3.
The number 3-B is the offset (relative to the first of the month) for a Tuesday, but it might be in the current month or the previous month; 10-B is also a Tuesday, and so are 17-B and 24-B.
You should choose to subtract 10 because you want C to be positive for all inputs. Because you need D to be positive in the next step, but a negative value for C would result in a negative value for D. this is because -1%7 gives -1 on most systems.
3. Divide by 7 and keep remainder to get D value.
D is also the offset for a Tuesday, and D in the range of 0 to 6. every day in the first week has an offset between 0 to 6. So D is the first Tuesday of the month.
4. Add 7 to get E.
That takes the range of E from 7 to 13. Every day in the second week has an offset in the range of 7-13.
5. Take the result and add that number of days to the date of the first day of the month.
Implementing this on ORACLE database:
SELECT TO_CHAR (month_date, 'dd-mon-yyyy') AS first_day_of_month,
TO_CHAR (( (month_date)
+ MOD ((10 - TO_CHAR ((month_date), 'd')), 7)
+ 7
),
'dd-mon-yyyy'
) AS second_tuesday_of_month
FROM month_date
OUTPUT
*******
Hope this is useful for you guys.
To calculate the second Tuesday of the month, this depends on the day of the week of the first day of the month. But we need to know which month is this? Obviously, all we need to know are the month and year, but it’s easier if we start with a date as such. For testing purposes, the following table can be used.
Create table month_date (month_date date);
Insert into month_date values(date ‘2009-04-01’);
Insert into month_date values(date ‘2009-05-01’);
Insert into month_date values(date ‘2009-06-01’);
Insert into month_date values(date ‘2009-07-01’);
So, given a date, the first day of a month, what date is the second Tuesday of that month? The process begins by calculating the day of the week for the first day of the month.
Day-of-week function:
Even though standard SQL doesn’t provide any function to give the day of the week for any date, most of the database systems do.
ORACLE provides a function:
TO_CHAR(DATE,’D’);
The essence of this function is that it will return a number between 0 and 6 or 1 and 7.Sometimes 0 or 1 is Sunday and 6 or 7 is Saturday, and sometime 0 or 1 is Monday and 6 or 7 is Sunday. This depends on how the NLS_TERRITORY parameter is set.
Note: We follow that 1 is Sunday and 7 is Saturday in our example.
-----
The Formula:
To convert the first day of the month into second Tuesday of the month simply involves manipulating the day of the week of the first day with in an arithmetic formula. Before we see the formula, we should know what happens when the first day of the month falls on each day of the week from Sunday through Saturday.
Lets see this:
1. A Sunday, the third is a Tuesday, so the tenth is the second Tuesday
2. A Monday, the second is Tuesday, so the ninth is the second Tuesday
3. A Tuesday, the eighth is the second Tuesday
4. A Wednesday, the seventh is the next Tuesday and fourteenth is the second Tuesday.
5. A Thursday, the sixth is the next Tuesday and thirteenth is the second Tuesday
6. A Friday, the fifth is the next Tuesday and the twelfth is the second Tuesday
7. A Saturday, the fourth is the next Tuesday and the eleventh is the second Tuesday
This gives us all the possibilities. So now we have to reduce these facts to formula.
| A | B | C | D | E |
|---|---|---|---|---|
| 1ST | WEEK DAY | 10-B | C MOD 7 | D+7 |
| SUN | 1 | 9 | 2 | 9 |
| MON | 2 | 8 | 1 | 8 |
| TUE | 3 | 7 | 0 | 7 |
| WED | 4 | 6 | 6 | 13 |
| THU | 5 | 5 | 5 | 12 |
| FRI | 6 | 4 | 4 | 11 |
| SAT | 7 | 3 | 3 | 10 |
The first column (A) is the day of the week of the first day of the month; and the second column is the numerical equivalent of this day using the range from 1 to 7.
The important data in the above table is the last column; which is the number of days to add to the date of the first day of the month.
So, the formula can be derived as follows.
1. Find B, the day of the week of the first day of the month, using
1-Sunday……7-Saturday.
2. Subtract this number from 10 to get C:
With Sunday -1 …. Saturday – 7, Tuesday would be 3.
The number 3-B is the offset (relative to the first of the month) for a Tuesday, but it might be in the current month or the previous month; 10-B is also a Tuesday, and so are 17-B and 24-B.
You should choose to subtract 10 because you want C to be positive for all inputs. Because you need D to be positive in the next step, but a negative value for C would result in a negative value for D. this is because -1%7 gives -1 on most systems.
3. Divide by 7 and keep remainder to get D value.
D is also the offset for a Tuesday, and D in the range of 0 to 6. every day in the first week has an offset between 0 to 6. So D is the first Tuesday of the month.
4. Add 7 to get E.
That takes the range of E from 7 to 13. Every day in the second week has an offset in the range of 7-13.
5. Take the result and add that number of days to the date of the first day of the month.
Implementing this on ORACLE database:
SELECT TO_CHAR (month_date, 'dd-mon-yyyy') AS first_day_of_month,
TO_CHAR (( (month_date)
+ MOD ((10 - TO_CHAR ((month_date), 'd')), 7)
+ 7
),
'dd-mon-yyyy'
) AS second_tuesday_of_month
FROM month_date
OUTPUT
*******
| FIRST_DAY_OF_MONTH | SECOND_TUESDAY_OF_MONTH |
|---|---|
| 01-apr-2009 | 14-apr-2009 |
| 01-may-2009 | 12-may-2009 |
| 01-jun-2009 | 09-jun-2009 |
| 01-jul-2009 | 14-jul-2009 |
Hope this is useful for you guys.
Tuesday, June 16, 2009
Dedicated server Vs Shared Server:
Dedicated server:
--> The user process and the server process are separate all together.
--> In a dedicated server environment each user process has its own server process.
--> To take the advantage of distributed processing the user process and server process can run on different machines.
--> The dedicated server exists even when a user process is not making any database requests, at this particular point of time the server process remains idle.
--> The ratio between the user process and server process in a dedicated server environment will be one – to – one.
If the user and server process are on the same machine then the interface in use will be IPC (Inter Process Communication) mechanism.
Shared server:
--> Generally we go to shared server in order to increase the server efficiency.
--> In an OLTP (On Line Transaction Processing) environment, at the time of order entry the server process will be in the idle state for almost 90 % of the time. Due to this we are not able to actually utilize the system resources which are getting wasted.
--> Unlike dedicated server processes, a shared server process can process can process any request which comes, instead waiting for a particular request.
--> In a shared server the client (User) process hits the dispatcher which in turn assigns the request to any one of the server processes which is free at that particular point of time.
--> The PMON process registers the location and load of the dispatcher with the listener, enabling the listener to forward the requests to least utilized dispatcher.
--> A dispatched can support multiple client connection concurrently.
Benefits of Shared server:
--> Reduces the number of server process which are idle.
--> Reduces the umber of processes against an instance.
--> Load balancing is achieved.
--> The number of possible users can be increased.
Note:
A dedicated server can be used at –
--> The time of connecting as a SYSDBA to start up / shutdown / at the time of performing recovery.
--> When batch jobs are executed as there will be minimal / no idle time as such.
Subscribe to Oracle Bullets by Email
--> The user process and the server process are separate all together.
--> In a dedicated server environment each user process has its own server process.
--> To take the advantage of distributed processing the user process and server process can run on different machines.
--> The dedicated server exists even when a user process is not making any database requests, at this particular point of time the server process remains idle.
--> The ratio between the user process and server process in a dedicated server environment will be one – to – one.
If the user and server process are on the same machine then the interface in use will be IPC (Inter Process Communication) mechanism.
Shared server:
--> Generally we go to shared server in order to increase the server efficiency.
--> In an OLTP (On Line Transaction Processing) environment, at the time of order entry the server process will be in the idle state for almost 90 % of the time. Due to this we are not able to actually utilize the system resources which are getting wasted.
--> Unlike dedicated server processes, a shared server process can process can process any request which comes, instead waiting for a particular request.
--> In a shared server the client (User) process hits the dispatcher which in turn assigns the request to any one of the server processes which is free at that particular point of time.
--> The PMON process registers the location and load of the dispatcher with the listener, enabling the listener to forward the requests to least utilized dispatcher.
--> A dispatched can support multiple client connection concurrently.
Benefits of Shared server:
--> Reduces the number of server process which are idle.
--> Reduces the umber of processes against an instance.
--> Load balancing is achieved.
--> The number of possible users can be increased.
Note:
A dedicated server can be used at –
--> The time of connecting as a SYSDBA to start up / shutdown / at the time of performing recovery.
--> When batch jobs are executed as there will be minimal / no idle time as such.
Subscribe to Oracle Bullets by Email
Gist of Materialized views:
People might be aware of Snapshots in ORACLE, now in recent versions of ORACLE the place of snapshots has been occupied by MATERIALIZED VIEWS. These materialized views have many uses:
1. We can copy the data from one instance to other with the same format, and we can query against the materialized view directly.
2. We can copy the aggregated (Summarized) data from one instance to other instance and we can fire queries against the materialized view directly.
3. We can create a summarized (aggregated) version of a table, the interesting point here is that if we query the base table, then the query is automatically be rewritten to use the materialized view.
Let’s have examples for each of the above uses of the materialized views.
1. Here all the code is in our local database LOCALDB. We have a remote database RMDB, where there is a table named employee. However, one time per day our materialized view wakes up, dips into the RMDB and grabs the data and brings the same to LOCALDB.
Because of this we can fire the required queries to the materialized view even though we don’t have access to the RMDB.
In our LOCALDB:
Create materialized view emp_mview_ex1
Refresh complete
Start with sysdate
Next sysdate+1 as
Select emp_name, dept_name from employee@RMDB_dblink;
Now we can query the Materialized view as such
Select * from emp_mview_ex1;
2. Now let’s discuss about the summary (aggregated) table example. Even her all the code is in the local database LOCALDB. We have a employee table in the RMDB, now this data is summarized daily – getting the number of employees per department, as well as the average salary in that particular department.
Even, here we can fire queries against our local database LOCALDB with no access to the RMDB, aside from the refreshing the materialized view daily.
In our LOCALDB:
Create materialized view emp_mview_ex2
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name,count(*), avg(salary) from employee@RMDB_dblink; group by dept_name;
Now we can query the Materialized view,
Select * from emp_mview_ex2;
3. Now the interesting use of the materialized view query rewrite. A materialized view is built with the help of a base table and data is summarized, The ORACLE optimizer is smart enough in such a way that it can realize that when a query is fired on a base table, the data is already been aggregated in a materialized view, and will go with the data in the materialized view.
In our LOCALDB:
Create materialized view emp_mview_ex3
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name, count(*) from employee group by dept_name;
Now we query the LOCALDB with the base table n the from clause
Select dept_name, count(*) from employee group by dept_name;
Even though the query is fired against the base table, at the time of execution the optimizer will switch to the existing materialized view with the information already aggregated (summarized) and it will use the emp_mview_ex3 instead.
To make optimizer to think of using the existing materialized views there are some init.ora parameters to be set they are:
1. query_rewrite_enabled needs to be set to a value TRUE and we should have the ‘query rewrite’ system privilege.
2. If the materialized view has to be refreshed based on a schedule the it is going to use dbms_job to do so, so job_queue_processes needs to be some value > 0.
Subscribe to Oracle Bullets by Email
1. We can copy the data from one instance to other with the same format, and we can query against the materialized view directly.
2. We can copy the aggregated (Summarized) data from one instance to other instance and we can fire queries against the materialized view directly.
3. We can create a summarized (aggregated) version of a table, the interesting point here is that if we query the base table, then the query is automatically be rewritten to use the materialized view.
Let’s have examples for each of the above uses of the materialized views.
1. Here all the code is in our local database LOCALDB. We have a remote database RMDB, where there is a table named employee. However, one time per day our materialized view wakes up, dips into the RMDB and grabs the data and brings the same to LOCALDB.
Because of this we can fire the required queries to the materialized view even though we don’t have access to the RMDB.
In our LOCALDB:
Create materialized view emp_mview_ex1
Refresh complete
Start with sysdate
Next sysdate+1 as
Select emp_name, dept_name from employee@RMDB_dblink;
Now we can query the Materialized view as such
Select * from emp_mview_ex1;
2. Now let’s discuss about the summary (aggregated) table example. Even her all the code is in the local database LOCALDB. We have a employee table in the RMDB, now this data is summarized daily – getting the number of employees per department, as well as the average salary in that particular department.
Even, here we can fire queries against our local database LOCALDB with no access to the RMDB, aside from the refreshing the materialized view daily.
In our LOCALDB:
Create materialized view emp_mview_ex2
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name,count(*), avg(salary) from employee@RMDB_dblink; group by dept_name;
Now we can query the Materialized view,
Select * from emp_mview_ex2;
3. Now the interesting use of the materialized view query rewrite. A materialized view is built with the help of a base table and data is summarized, The ORACLE optimizer is smart enough in such a way that it can realize that when a query is fired on a base table, the data is already been aggregated in a materialized view, and will go with the data in the materialized view.
In our LOCALDB:
Create materialized view emp_mview_ex3
Refresh complete
Start with sysdate
Next sysdate+1 as
Select dept_name, count(*) from employee group by dept_name;
Now we query the LOCALDB with the base table n the from clause
Select dept_name, count(*) from employee group by dept_name;
Even though the query is fired against the base table, at the time of execution the optimizer will switch to the existing materialized view with the information already aggregated (summarized) and it will use the emp_mview_ex3 instead.
To make optimizer to think of using the existing materialized views there are some init.ora parameters to be set they are:
1. query_rewrite_enabled needs to be set to a value TRUE and we should have the ‘query rewrite’ system privilege.
2. If the materialized view has to be refreshed based on a schedule the it is going to use dbms_job to do so, so job_queue_processes needs to be some value > 0.
Subscribe to Oracle Bullets by Email
Friday, June 12, 2009
Generating random numbers and strings in Oracle is easier
For testing purposes at times we may need some test data, for this we might need to generate some random numbers or strings. People may write their own Random number generation scripts, but even ORACLE is providing a package for generating the random numbers and strings as well. This may be useful to in creating the auto generated passwords in a web page.
The fact is that Oracle provides us with a random number generation package. When this package is used to generate the random numbers it is faster than writing our own random generation scripts in PL/SQL. This Random generator package is not only used to generate the numbers and strings but this can be used to generate the alpha numeric strings also with the desired size (number of characters).
The DBMS_RANDOM package which is provided by ORACLE will generate random data in character, numeric or alphanumeric formats. The size of the random values which are to be generated can also be specified.
This package can be created using the script dbmsrand.sql available in the/RDBMS/ADMIN directory.
The following are the functions that are present in the package which can be used to serve the purpose of generating random numbers and strings.
RANDOM – This function is used to generate the random numbers.
VALUE – This function is used to generate random numbers from the range provided. The range will be taken as 0-1 if nothing is provided.
STRING – This function is used to generate the strings either in upper case or lower case or alphanumeric format.
• The first argument of the functions takes the following values which will define the string type to be generated.
• U - Upper case
• L - Lower case
• A - Alphanumeric
• X - Alphanumeric with upper case alphabets.
• P - Printable characters only.
If we use any character other than these characters the output will be in Upper case.
The second argument will be the size of the random number / string to be generated.
Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.
INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.
SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.
TERMINATE - Close the process of random number generation.
Below are some of the examples of using the package.
Example 1: To generate a random number (positive or negative)
Select dbms_random.random from dual;
RANDOM
_____________
1254544778
Example 2: To generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;
VALUE
_____________
0.98121484036063
Example 3: To generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;
NUM
_____________
979.825208337552
Example 4: To generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;
NUM
_____________
886832250758.021
Example 5: To generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;
STR
_______________________
TSKZJBAGRQREGUEBRXIO
Example 6: To generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;
STR
____________________
thfrkllkuhxqeybkcykg
Example 7: To generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.
SQL> select dbms_random.string('A', 20) str from dual;
STR
__________________
xWPvLfHYuPWDNFEIHtUI
Example 8: To generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;
STR
________________________
CWWULRKBQLAMCLGYWC4A
Example 9: To generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
SQL> select dbms_random.string('P', 20) str from dual;
STR
___________________
dsL&B}l?mMUIr?'T1r6{
Subscribe to Oracle Bullets by Email
The fact is that Oracle provides us with a random number generation package. When this package is used to generate the random numbers it is faster than writing our own random generation scripts in PL/SQL. This Random generator package is not only used to generate the numbers and strings but this can be used to generate the alpha numeric strings also with the desired size (number of characters).
Bullet:
DBMS_RANDOM package
The DBMS_RANDOM package which is provided by ORACLE will generate random data in character, numeric or alphanumeric formats. The size of the random values which are to be generated can also be specified.
This package can be created using the script dbmsrand.sql available in the
The following are the functions that are present in the package which can be used to serve the purpose of generating random numbers and strings.
RANDOM – This function is used to generate the random numbers.
VALUE – This function is used to generate random numbers from the range provided. The range will be taken as 0-1 if nothing is provided.
STRING – This function is used to generate the strings either in upper case or lower case or alphanumeric format.
• The first argument of the functions takes the following values which will define the string type to be generated.
• U - Upper case
• L - Lower case
• A - Alphanumeric
• X - Alphanumeric with upper case alphabets.
• P - Printable characters only.
If we use any character other than these characters the output will be in Upper case.
The second argument will be the size of the random number / string to be generated.
Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.
INITIALIZE - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.
SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.
TERMINATE - Close the process of random number generation.
Examples:
Below are some of the examples of using the package.
Example 1: To generate a random number (positive or negative)
Select dbms_random.random from dual;
RANDOM
_____________
1254544778
Example 2: To generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;
VALUE
_____________
0.98121484036063
Example 3: To generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;
NUM
_____________
979.825208337552
Example 4: To generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;
NUM
_____________
886832250758.021
Example 5: To generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;
STR
_______________________
TSKZJBAGRQREGUEBRXIO
Example 6: To generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;
STR
____________________
thfrkllkuhxqeybkcykg
Example 7: To generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.
SQL> select dbms_random.string('A', 20) str from dual;
STR
__________________
xWPvLfHYuPWDNFEIHtUI
Example 8: To generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;
STR
________________________
CWWULRKBQLAMCLGYWC4A
Example 9: To generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
SQL> select dbms_random.string('P', 20) str from dual;
STR
___________________
dsL&B}l?mMUIr?'T1r6{
Subscribe to Oracle Bullets by Email
Tuesday, June 9, 2009
Running out of Disk Space:
A Query which is badly written may even fill up the disk drive even if the disk is large enough. We need to get back the system onto its feet as quickly as possible.
Temporary Tablespace:
Temporary tablespace generally it holds SQL Execution workspace, intermediate indexes, sub-query information, sorts etc… Generally it is released automatically, but there are situations where this may lead to problems. Issues such as fragmentation, DBMS bugs, badly written queries may cause temporary space to grow till you run out of disk space.
To housekeep the temporary space in oracle is a common activity. The temporary space is flexible and powerful but it comes with some administration overhead.
Bullet:
Let us assume our database is in /u01/oradata/Chaitanya/ and our temporary space is named as temp01.dbf. We have some free space in u02 to use some space for a specified period of time. A SYSDBA user can do this.
Find the name of the current default temporary tablespace
SELECT property_value FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
-----------------------------
TEMP
Create a new temporary tablespace to allow the system to operate while we are making the changes:
CREATE temporary tablespace t1
Tempfile ‘/u02/Chaitanya_temp/t01.dbf’
Size 512M reuse autoextend on next 10M maxsize unlimited
Extent management local uniform size 256K;
Bring the new temporary tablespace online
ALTER DATABASE
Tempfile ‘u02/Chaitanya_temp/t01.dbf’
ONLINE ;
Change the New temporary tablespce as the default temporary tablespace
Alter database default temporary tablespace t1;
Drop the old temporary tablespace which is using the disk space
Drop tablespace temp including the contents and datafiles;
We are running with the new temporary tablespace but what we really wanted to do is recreate the old space in old partition and use the same. While we are doing so, we can restrict the size of the new space as 0.5 GB / 1 Gb, and allow it to grow till some extent let it be 4 GB.
Create temporary tablespace temp
Tempfile ‘u01/oradata/chaitanya/temp01.dbf’
Size 512M reuse autoextend on next 10M maxsize 4096M
Extent management local uniform size 256K;
Now we have to switch every thing back to using this final temp tablespace, and delete the temporary tablespce which we have created for the temporary use (/u02/Chaitanya_temp/t1)
Alter database
Tempfile ‘u01/oradata/Chaitanya/temp01.dbf’
ONLINE;
Alter database default temporary tablespace temp;
Drop tablespace t1 including contents and datafiles;
Please post your comments on this Bullet.
Subscribe to Oracle Bullets by Email
Temporary Tablespace:
Temporary tablespace generally it holds SQL Execution workspace, intermediate indexes, sub-query information, sorts etc… Generally it is released automatically, but there are situations where this may lead to problems. Issues such as fragmentation, DBMS bugs, badly written queries may cause temporary space to grow till you run out of disk space.
To housekeep the temporary space in oracle is a common activity. The temporary space is flexible and powerful but it comes with some administration overhead.
Bullet:
Let us assume our database is in /u01/oradata/Chaitanya/ and our temporary space is named as temp01.dbf. We have some free space in u02 to use some space for a specified period of time. A SYSDBA user can do this.
Find the name of the current default temporary tablespace
SELECT property_value FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
-----------------------------
TEMP
Create a new temporary tablespace to allow the system to operate while we are making the changes:
CREATE temporary tablespace t1
Tempfile ‘/u02/Chaitanya_temp/t01.dbf’
Size 512M reuse autoextend on next 10M maxsize unlimited
Extent management local uniform size 256K;
Bring the new temporary tablespace online
ALTER DATABASE
Tempfile ‘u02/Chaitanya_temp/t01.dbf’
ONLINE ;
Change the New temporary tablespce as the default temporary tablespace
Alter database default temporary tablespace t1;
Drop the old temporary tablespace which is using the disk space
Drop tablespace temp including the contents and datafiles;
We are running with the new temporary tablespace but what we really wanted to do is recreate the old space in old partition and use the same. While we are doing so, we can restrict the size of the new space as 0.5 GB / 1 Gb, and allow it to grow till some extent let it be 4 GB.
Create temporary tablespace temp
Tempfile ‘u01/oradata/chaitanya/temp01.dbf’
Size 512M reuse autoextend on next 10M maxsize 4096M
Extent management local uniform size 256K;
Now we have to switch every thing back to using this final temp tablespace, and delete the temporary tablespce which we have created for the temporary use (/u02/Chaitanya_temp/t1)
Alter database
Tempfile ‘u01/oradata/Chaitanya/temp01.dbf’
ONLINE;
Alter database default temporary tablespace temp;
Drop tablespace t1 including contents and datafiles;
Please post your comments on this Bullet.
Subscribe to Oracle Bullets by Email
Subscribe to:
Posts (Atom)