sqlplus username/pwd@//hostname:1521/sidname
Just another new feature in 10g
Sunday, October 29, 2006
Thursday, October 05, 2006
Tuesday, August 22, 2006
Friday, July 07, 2006
Traditional HOT backups !!
Ok..you traditional HOT backup (not RMAN backup) failed or you have killed the HOT backup. Then you have check if any of the files are still active in V$backup and then do a "alter tablespace end backup" for each and every tablespace. In 10g you don't have to do that. You can do a "alter database begin backup" and "alter database end backup". These commands will either put/take the tablespaces in/out of HOT backup mode. Not sure if this is goin' to help a lot of people, 'cos now most of the people use RMAN. In case you are using traditional HOT backups ( I prefer using RMAN backups), then this might be of help to you.
Just another new feature in 10g.
Hope this helps.
-- Vish.
Monday, June 19, 2006
Reverse engineer the DDL
Here is how to reverse engineer the DDL commands. I've found this pretty useful sometimes. For example, in case you want to recreate the db and you want all the tablespace creation scrips. Instead of doin' it from scratch you can use the package, GET_METADA.GET_DDL to get the generate the script for you and then you can use these scripts to create the tablespaces once the db is recreated.
For example, to reverse engineer the tablespace creation script,
sql > set long 900000
sql > select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;
For example, if i want to get the tablespace creation script for the tablespace users, i use
sql > select DBMS_METADATA.GET_DDL('TABLESPACE','USERS') from dual;
and i set long as 900000 before doin' this, otherwise my output is truncated and i can't see the whole script.
To reverse engineer the table creation script,
select DBMS_METADATA.GET_DDL('TABLE','table_name') from dual;
You can do this to get the DDL of all the objects in the database.
Hope this helps.
-- Vish.
ALL ABOUT TABLESPACES
ALL ABOUT TABLESPACES
Here is all that you might need to know about when working with tablespaces. I've just given the syntax taking only some requirements into account. Change them according to your requirement.
DISCLAIMER: All the views experessed here are my own. I'm not responsible for any problem that you might get into by following the below syntax. Use them at your own risk. But, this is pretty much i use and they worked fine for me until now.
NOTE:
1. Never, Ever, Ever delete a datafile. You can only resize the datafile to five oracle blocks. You can drop the whole tablespace though !! If you delete the datafile, then you’ve to recover it. The control file remembers that the datafile is still there eventhough you drop it at the database level. Also, remember that there is no RECYCLE BIN in unix.
2. You can’t drop the default temp and undo tablespaces. You need to create a new temp/undotbs, make it default temp/undotbs and then drop the old temp/undo tbs.
3. Make sure you fill up the temp datafile once you add them to the temp tablespaces, ‘cos it does not really occupy the whole OS space until it is used completely. If you are on solaris, use mkfile command to do that.
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
1. To get the script of an already existing tablespace, use the following. (Reverse engineering the tablespace creation script)
select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;
For Example, to get the script of the SYSTEM tablespace,
select DBMS_METADATA.GET_DDL('TABLESPACE','SYSTEM') from dual;
2. To create a tablespace: (with extent management local, uniform size 128K extent sizes)
CREATE TABLESPACE "tablespace_name" DATAFILE
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M,
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;
3. To alter/add a datafile :
alter database datafile '/db/xxxx/xxx/datafilename.dbf' resize 2048M;
To add a datafile to an existing tablespace:
alter tablespace tablespace_name add datafile ‘datafile_name’ size xxxxM;
For example, to add a datafile of size 512M to the SYSTEM tablespace,
Alter tablespace system add datafile ‘/db/dbxxx/oracle/xxxxxxx/datafile_name.dbf’ size 512M;
4. To drop a tablespace:
drop tablespace tablespace_name including contents and datafiles;
5. To get the tablespace info of a database:
Note that the values are rounded to the nearest integer.
select a.tablespace_name, trunc(b.allocated)Allocated, trunc(a.free) Free, trunc(b.allocated-a.free) USED,
(100-trunc(((b.allocated-a.free)*100)/b.allocated)) Percentfree from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
union
select 'Total', trunc( sum(b.allocated)), trunc(sum(a.free)), trunc(sum((b.allocated-a.free))),
00000 from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
order by 2;
6. To check the default temporary tablespace of the database:
select property_name, property_value from database_properties;
select property_name, property_value from database_properties where property_name like ‘%TEMP%’;
7. To change the default temporary tablespace of the whole database:
alter database default temporary tablespace temp_tablespace_name;
8. To change the default temporary tablespace of a particular user :
alter user username default temporary tablespace temp_tablespace_name;
9. To create a temp tablespace:
On solaris:
host /usr/sbin/mkfile 1024m /db/db004/oracle/TOR_M704/dsmtemp.df01.dbf
create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' reuse
extent management local uniform size 128K;
If the OS is not solaris, then
create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' size 1024M
extent management local uniform size 128K;
NOTE : When you create the tempfile without using mkfile, the file is created but the entire space is not occupied at the OS level. Lets say that there is 1GB in that mount point where you have created the datafile of size 512M. It still shows that there is 1GB of space in that mountpoint eventhough this tempfile is created. This space is occupied only when that space is used. You can use the following script to recursively sort until the space is filled.
Select * from dba_source a, dba_source b, dba_source c order by 1;
This script basically keeps on sorting and is never ending. So, it fills up the temp space continiously until the whole tempfile is full. Once the whole tempfile is full, you can see that the space in that mountpoint (where there was 1GB of space ) is now only 512MB, ‘cos 512MB is used for this tempfile.
10. To add a tempfile to the existing temp tablespace:
alter tablespace temp add tempfile ‘/db/dbxxx/xxx/temp02.dbf’ size 1024M;
11. To resize the tempfile
alter database tempfile ‘/db/dbxxx/xxx/temp02.dbf’ resize 512M;
12. To drop and recreate a temp tablespace (lets say temp1):
a) first create another temp tablespace ..lets say temp2
b) then make this the default tablespace for the database, also for the users who had default temp tablespace as temp1
c) then drop the tablespace temp1
Note: The database won’t let you drop the default temporary tablespace of the database anyway.
13. To drop and recreate the undotablespace:
Follow the same steps as in step 12.
14. To drop a tempfile:
In 10g:
alter database tempfile '/db/dbxxx/xxxx/xxxx/temp01.dbf' drop including datafiles;
In 9i:
Alter database tempfile ‘/db/dbxxx/xxx/xxxx/temp01.dbf’ offline drop;
NOTE : Be careful in deleting the temp files. Refer to the documentation before doin’ that and remember that there is no RECYCLE BIN for unix. So, be extra careful when you delete something in unix.
15. To get the space usage of the datafiles:
COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
Author : Kam Muhamad Sirajdin (Acknowledgement)
-- Will keep on adding stuff as i remember.....so keep checking.....
Here is all that you might need to know about when working with tablespaces. I've just given the syntax taking only some requirements into account. Change them according to your requirement.
DISCLAIMER: All the views experessed here are my own. I'm not responsible for any problem that you might get into by following the below syntax. Use them at your own risk. But, this is pretty much i use and they worked fine for me until now.
NOTE:
1. Never, Ever, Ever delete a datafile. You can only resize the datafile to five oracle blocks. You can drop the whole tablespace though !! If you delete the datafile, then you’ve to recover it. The control file remembers that the datafile is still there eventhough you drop it at the database level. Also, remember that there is no RECYCLE BIN in unix.
2. You can’t drop the default temp and undo tablespaces. You need to create a new temp/undotbs, make it default temp/undotbs and then drop the old temp/undo tbs.
3. Make sure you fill up the temp datafile once you add them to the temp tablespaces, ‘cos it does not really occupy the whole OS space until it is used completely. If you are on solaris, use mkfile command to do that.
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
1. To get the script of an already existing tablespace, use the following. (Reverse engineering the tablespace creation script)
select DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') from dual;
For Example, to get the script of the SYSTEM tablespace,
select DBMS_METADATA.GET_DDL('TABLESPACE','SYSTEM') from dual;
2. To create a tablespace: (with extent management local, uniform size 128K extent sizes)
CREATE TABLESPACE "tablespace_name" DATAFILE
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M,
'/db/dbxxx/oracle/xxxxxxxxx/datafile_name.dbf' SIZE 1024M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;
3. To alter/add a datafile :
alter database datafile '/db/xxxx/xxx/datafilename.dbf' resize 2048M;
To add a datafile to an existing tablespace:
alter tablespace tablespace_name add datafile ‘datafile_name’ size xxxxM;
For example, to add a datafile of size 512M to the SYSTEM tablespace,
Alter tablespace system add datafile ‘/db/dbxxx/oracle/xxxxxxx/datafile_name.dbf’ size 512M;
4. To drop a tablespace:
drop tablespace tablespace_name including contents and datafiles;
5. To get the tablespace info of a database:
Note that the values are rounded to the nearest integer.
select a.tablespace_name, trunc(b.allocated)Allocated, trunc(a.free) Free, trunc(b.allocated-a.free) USED,
(100-trunc(((b.allocated-a.free)*100)/b.allocated)) Percentfree from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
union
select 'Total', trunc( sum(b.allocated)), trunc(sum(a.free)), trunc(sum((b.allocated-a.free))),
00000 from
(select tablespace_name, sum(bytes)/1024/1024 free from sys.dba_free_space group by tablespace_name) a,
(select sum(bytes)/1024/1024 allocated, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
order by 2;
6. To check the default temporary tablespace of the database:
select property_name, property_value from database_properties;
select property_name, property_value from database_properties where property_name like ‘%TEMP%’;
7. To change the default temporary tablespace of the whole database:
alter database default temporary tablespace temp_tablespace_name;
8. To change the default temporary tablespace of a particular user :
alter user username default temporary tablespace temp_tablespace_name;
9. To create a temp tablespace:
On solaris:
host /usr/sbin/mkfile 1024m /db/db004/oracle/TOR_M704/dsmtemp.df01.dbf
create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' reuse
extent management local uniform size 128K;
If the OS is not solaris, then
create temporary tablespace DSMTEMP
tempfile '/db/dbXXX/XXXX/XXXXX/temp.df01.dbf' size 1024M
extent management local uniform size 128K;
NOTE : When you create the tempfile without using mkfile, the file is created but the entire space is not occupied at the OS level. Lets say that there is 1GB in that mount point where you have created the datafile of size 512M. It still shows that there is 1GB of space in that mountpoint eventhough this tempfile is created. This space is occupied only when that space is used. You can use the following script to recursively sort until the space is filled.
Select * from dba_source a, dba_source b, dba_source c order by 1;
This script basically keeps on sorting and is never ending. So, it fills up the temp space continiously until the whole tempfile is full. Once the whole tempfile is full, you can see that the space in that mountpoint (where there was 1GB of space ) is now only 512MB, ‘cos 512MB is used for this tempfile.
10. To add a tempfile to the existing temp tablespace:
alter tablespace temp add tempfile ‘/db/dbxxx/xxx/temp02.dbf’ size 1024M;
11. To resize the tempfile
alter database tempfile ‘/db/dbxxx/xxx/temp02.dbf’ resize 512M;
12. To drop and recreate a temp tablespace (lets say temp1):
a) first create another temp tablespace ..lets say temp2
b) then make this the default tablespace for the database, also for the users who had default temp tablespace as temp1
c) then drop the tablespace temp1
Note: The database won’t let you drop the default temporary tablespace of the database anyway.
13. To drop and recreate the undotablespace:
Follow the same steps as in step 12.
14. To drop a tempfile:
In 10g:
alter database tempfile '/db/dbxxx/xxxx/xxxx/temp01.dbf' drop including datafiles;
In 9i:
Alter database tempfile ‘/db/dbxxx/xxx/xxxx/temp01.dbf’ offline drop;
NOTE : Be careful in deleting the temp files. Refer to the documentation before doin’ that and remember that there is no RECYCLE BIN for unix. So, be extra careful when you delete something in unix.
15. To get the space usage of the datafiles:
COLUMN free_space_mb format 999999.90
COLUMN allocated_mb format 999999.90
COLUMN used_mb format 999999.90
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
Author : Kam Muhamad Sirajdin (Acknowledgement)
-- Will keep on adding stuff as i remember.....so keep checking.....
Monday, May 29, 2006
Installed linux successfully !!
Finally, i was able to install the ubuntu version of linux on my laptop successfully. Now, i've a dual boot system (linux and windows).
Below are the steps i've followed for creating this dual boot system.
1. Installed partition magic
2. Resized my 60GB NTFS hard disk to 39GB, created a 16GB ext3 format partition, a 3GB FAT32 format partition, and a 1GB linux swap partition. I've created the FAT32 partition 'cos this format allows both linux and windows to read/write onto this file system and files on this mount point can be accessed in both OS.
3. Then i've downloaded ubuntu 5.10 (first the live cd and then the direct install cd). You can download it from here.
http://www.ubuntu.com/download/
4. You can try out the live cd to see how it looks (it doesn't do anything except that it lets you to try it out)
5. Once i've downloaded the direct install cd, i've booted my laptop from that cd and it was a pretty straight forward install from there.
6. Just be careful when creating the mountpoints. I've assinged the 16GB ext3 format for my root (/) partition and named 3GB partition as /windows, 1GB linux swap partion for swap. The remaining mount points are left as it is (the default file system given by the partitioner. It shows the 39GB windows ntfs and the other ones. Just leave them as it is).
7. Now, when i asked it to install them i've asked the partitioner to format those /root and /windows partitions and clicked Install.
8. After sometime, BOOM !! got the message "Linux installed successfully".
9. During the install, it was configuring my DHCP client and it asked me to choose my internal network card or the internal wireless card, i chose internal network card and then once it is installed i've configured my wireless card.
Thats it. Now i've a dual boot system, everything working.
PS : I've tried the ubuntu draper drake 6.06, which is still in the beta version and it said "Installer crashed" a couple of times. I've stopped trying it out 'cos i did not want to screw up existing windows OS. Lets try and install it once the final version is released.
Lets install oracle on linux next week.
Now i wonder, why do i want to pay a 100 bucks for windows professional when i'm getting ev. thing here through linux for FREE ?? Lets get used to LINUX !!
-- Vish.
Below are the steps i've followed for creating this dual boot system.
1. Installed partition magic
2. Resized my 60GB NTFS hard disk to 39GB, created a 16GB ext3 format partition, a 3GB FAT32 format partition, and a 1GB linux swap partition. I've created the FAT32 partition 'cos this format allows both linux and windows to read/write onto this file system and files on this mount point can be accessed in both OS.
3. Then i've downloaded ubuntu 5.10 (first the live cd and then the direct install cd). You can download it from here.
http://www.ubuntu.com/download/
4. You can try out the live cd to see how it looks (it doesn't do anything except that it lets you to try it out)
5. Once i've downloaded the direct install cd, i've booted my laptop from that cd and it was a pretty straight forward install from there.
6. Just be careful when creating the mountpoints. I've assinged the 16GB ext3 format for my root (/) partition and named 3GB partition as /windows, 1GB linux swap partion for swap. The remaining mount points are left as it is (the default file system given by the partitioner. It shows the 39GB windows ntfs and the other ones. Just leave them as it is).
7. Now, when i asked it to install them i've asked the partitioner to format those /root and /windows partitions and clicked Install.
8. After sometime, BOOM !! got the message "Linux installed successfully".
9. During the install, it was configuring my DHCP client and it asked me to choose my internal network card or the internal wireless card, i chose internal network card and then once it is installed i've configured my wireless card.
Thats it. Now i've a dual boot system, everything working.
PS : I've tried the ubuntu draper drake 6.06, which is still in the beta version and it said "Installer crashed" a couple of times. I've stopped trying it out 'cos i did not want to screw up existing windows OS. Lets try and install it once the final version is released.
Lets install oracle on linux next week.
Now i wonder, why do i want to pay a 100 bucks for windows professional when i'm getting ev. thing here through linux for FREE ?? Lets get used to LINUX !!
-- Vish.
Monday, May 08, 2006
virtual indexes
I was goin' through one of the blogs (forgot which one it was), but it mentioned that we can create virtual indexes. Basically, what it does is that -- it doesn't create the index physically, but acts as a index virtually.
Sometimes you might be wondering wether creating index on a certain column is goin' to decrease the cost of the execution plan. Then this might be of great help.
Will post the exact syntax next time. Just got reminded of it and wanted to mention it here.
-- Vish.
Sometimes you might be wondering wether creating index on a certain column is goin' to decrease the cost of the execution plan. Then this might be of great help.
Will post the exact syntax next time. Just got reminded of it and wanted to mention it here.
-- Vish.
Installing unix...not quite successful !
Its been quite sometime i'm here. Well, moved on to a new job -- Fannie Mae, VA as a production suport DBA. But, here we go.
I was trying to install solaris on my laptop to make it a dual boot system (windows and solaris), but i was not quite successful.
After 15 hrs of work on it, i could install it, but still can't connected to the internet. Not sure if i don't have the drivers for my network card or something like that.(I have a new dell xps M140 laotop). Went through a lot of threads but could not really get the solution. It really sucked !!
Now, planning to move to ubuntu flavor of unix. Just went through some blogs and it has some excellent reviews. The new version, drapper drape is still in the beta stage, but the best thing with this is that you can have a "LIVE CD", which means that you can just try out your install on your windows machine without really installing it. May be -- u can call it a virtual install. You can even connect to internet through this live cd. If it goes fine and if you like it, then you can go ahead with the acutual install. What else do you need ? You can check out an OS even without installing it ? Amazing !!
You don't even need partition magic to format your drivers. ubuntu does that. Also, i've read somewhere that it even recognises your USB flash drivers ( I think its an NTFS format). Amazing once again (if thats true .. :) !!
I haven't tried it out yet, but i'm goin' to. By this weekend, i should have it ready (hopefully...ev. thing working).
Lets find out how amazing it is by the end of the week.
I was trying to install solaris on my laptop to make it a dual boot system (windows and solaris), but i was not quite successful.
After 15 hrs of work on it, i could install it, but still can't connected to the internet. Not sure if i don't have the drivers for my network card or something like that.(I have a new dell xps M140 laotop). Went through a lot of threads but could not really get the solution. It really sucked !!
Now, planning to move to ubuntu flavor of unix. Just went through some blogs and it has some excellent reviews. The new version, drapper drape is still in the beta stage, but the best thing with this is that you can have a "LIVE CD", which means that you can just try out your install on your windows machine without really installing it. May be -- u can call it a virtual install. You can even connect to internet through this live cd. If it goes fine and if you like it, then you can go ahead with the acutual install. What else do you need ? You can check out an OS even without installing it ? Amazing !!
You don't even need partition magic to format your drivers. ubuntu does that. Also, i've read somewhere that it even recognises your USB flash drivers ( I think its an NTFS format). Amazing once again (if thats true .. :) !!
I haven't tried it out yet, but i'm goin' to. By this weekend, i should have it ready (hopefully...ev. thing working).
Lets find out how amazing it is by the end of the week.
Monday, February 20, 2006
killing a session immediately
To kill a session in oracle, i generally use,
alter system kill session 'sid,serial#';
(where i get sid, serial# by querying v$session. I also get the unix process id from here).
But this many not kill the processes at the unix level. So, i generally kill the process at the unix level too.
I've found on one of the blogs that you can do this at sql itself (without kill -9 ing) by using
alter system disconnect session 'sid,serial#' immediate;
alter system kill session 'sid,serial#';
(where i get sid, serial# by querying v$session. I also get the unix process id from here).
But this many not kill the processes at the unix level. So, i generally kill the process at the unix level too.
I've found on one of the blogs that you can do this at sql itself (without kill -9 ing) by using
alter system disconnect session 'sid,serial#' immediate;
Thursday, February 09, 2006
Size of the table in bytes
To find the size of bytes allocated to a table:
sql > analyze table emp compute statistics;
sql > select num_rows * avg_row_len "Bytes Used" from dba_tables where table_name = 'TEST';
Bytes Used
-----------
560
sql > select bytes "Bytes Allocated" from dba_segments where segment_name = 'TEST';
Bytes Allocated
----------
524288
The result of the query shows that the emp table is using 560 bytes of the 524,288 bytes allocated to it.
**** you can use user_segments too...if u don't have the dba privileges ******
sql > analyze table emp compute statistics;
sql > select num_rows * avg_row_len "Bytes Used" from dba_tables where table_name = 'TEST';
Bytes Used
-----------
560
sql > select bytes "Bytes Allocated" from dba_segments where segment_name = 'TEST';
Bytes Allocated
----------
524288
The result of the query shows that the emp table is using 560 bytes of the 524,288 bytes allocated to it.
**** you can use user_segments too...if u don't have the dba privileges ******
10g installation on windows
I was trying to install oracle 10g on my personal laptop. Tried a couple of times without goin' through much of a documentation and failed. The network configuration assistant fails each time. Its a pretty straight forward installation except that it needs a fixed IP address (you can't have one in a DHCP enabled environment). But you can fake it using the Microsoft loopback adapter. You can see the following link to see how u can install microsoft loopback adapter.
http://support.microsoft.com/default.aspx?scid=kb;en-us;839013
Once i've installed this, i've included the following line in my hosts file (which is located in C:\WINDOWS\system32\drivers\etc). Now my hosts file looks like this (where vish is my computer name, 10.101.10.10 is the fixed IP address i've mentioned in the microsoft loopback adapter).
-----------------------------
10.10.10.10 vish (this is the new line u need to include)
127.0.0.1 localhost (this line is already present..just leave it there)
-----------------------------
Once i've done this, my network configuration assistant was successful.
Here is the link which shows a pretty staright forward way of installing oracle. Similar to 9i.
http://www.compiere.org/support/install/installOracleSteps.html
Now that i've installed 10g successuflly, i need to start learning the new features of oracle. In my next post i'll post the installation of oracle internet directory whcih i've implemented for my home network, so that we can connect to a centralized db instead of having the oracle server on each and ev. laptop.
http://support.microsoft.com/default.aspx?scid=kb;en-us;839013
Once i've installed this, i've included the following line in my hosts file (which is located in C:\WINDOWS\system32\drivers\etc). Now my hosts file looks like this (where vish is my computer name, 10.101.10.10 is the fixed IP address i've mentioned in the microsoft loopback adapter).
-----------------------------
10.10.10.10 vish (this is the new line u need to include)
127.0.0.1 localhost (this line is already present..just leave it there)
-----------------------------
Once i've done this, my network configuration assistant was successful.
Here is the link which shows a pretty staright forward way of installing oracle. Similar to 9i.
http://www.compiere.org/support/install/installOracleSteps.html
Now that i've installed 10g successuflly, i need to start learning the new features of oracle. In my next post i'll post the installation of oracle internet directory whcih i've implemented for my home network, so that we can connect to a centralized db instead of having the oracle server on each and ev. laptop.
Oracle Project Raptor
Finally, the much awaited TOAD like software is released by oracle. Thanks to oracle for recognizing that they need to have a software like TOAD delivered free of cost.
They have not yet implemented all the functionalities of TOAD, but its pretty OK. (Its free ofcourse). A delight for oracle pl/sql developers who don't have access to TOAD.
Its more intended towards developers than DBA's.
You can download Oracle project raptor here.
http://www.oracle.com/technology/software/products/sql/index.html
I've started to use it and almost got familiar with it.
They have not yet implemented all the functionalities of TOAD, but its pretty OK. (Its free ofcourse). A delight for oracle pl/sql developers who don't have access to TOAD.
Its more intended towards developers than DBA's.
You can download Oracle project raptor here.
http://www.oracle.com/technology/software/products/sql/index.html
I've started to use it and almost got familiar with it.
Wednesday, January 18, 2006
ORA-01114: IO error writing block to file name block %s
Problem Description: got this error when trying to read from a table/view: ORA-01114: IO error writing block to file name block %s
******From metalink: ORA-01114: IO error writing block to file string (block # string)
Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.
Action: Restore access to the device or remove unnecessary files to free up space. *****
In my case, this has happened b'cos we have created a temporary datafile in a mountpoint and did not extend it completely and now when it is trying to extend it, it can't ... ...this might be the cause of the error. As you know, when you create a tempfile, it doesn't immediately occupy the OS space unless, otherwise all the segments are extended. It just says database altered even though there is no space in that mountpoint.
We can see the the usage in enterprise manager. I've seen that and i've observed that i've allocated about 1GB to that tempfile..but there was only 0.9GB left....and it was trying to extend beyond that size. so, it was giving that error.
SOlUTION:
drop and recreate your temp tablespace.
procedure: create a new temp tablespace, make this as the default temp tablespace and drop the old one (in 9i , drop tablespace temp including contents and datafiles).
Any comments ??
******From metalink: ORA-01114: IO error writing block to file string (block # string)
Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.
Action: Restore access to the device or remove unnecessary files to free up space. *****
In my case, this has happened b'cos we have created a temporary datafile in a mountpoint and did not extend it completely and now when it is trying to extend it, it can't ... ...this might be the cause of the error. As you know, when you create a tempfile, it doesn't immediately occupy the OS space unless, otherwise all the segments are extended. It just says database altered even though there is no space in that mountpoint.
We can see the the usage in enterprise manager. I've seen that and i've observed that i've allocated about 1GB to that tempfile..but there was only 0.9GB left....and it was trying to extend beyond that size. so, it was giving that error.
SOlUTION:
drop and recreate your temp tablespace.
procedure: create a new temp tablespace, make this as the default temp tablespace and drop the old one (in 9i , drop tablespace temp including contents and datafiles).
Any comments ??
Tuesday, January 17, 2006
Tablespaces
Here are some scripts, tips regarding the tablespaces. This script basically gives you all the tablespace info in a database.
****************** BEGIN -- Tablespace info script ******************
set linesize 150;
select ddf.TABLESPACE_NAME Tablespace,
ddf.BYTES/1024/1024 MBytes_allocated,
(ddf.BYTES-DFS.BYTES)/1024/1024 MBytes_used,
round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) Percent_used,
dfs.BYTES/1024/1024 MBytes_free,
round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) Percent_free
from (select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_data_files
group by TABLESPACE_NAME) ddf,
(select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_free_space
group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME
order by ((ddf.BYTES-dfs.BYTES)/ddf.BYTES) desc;
****************** END-- Tablespace info script *****************
****************** BEGIN -- Tablespace info script ******************
set linesize 150;
select ddf.TABLESPACE_NAME Tablespace,
ddf.BYTES/1024/1024 MBytes_allocated,
(ddf.BYTES-DFS.BYTES)/1024/1024 MBytes_used,
round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) Percent_used,
dfs.BYTES/1024/1024 MBytes_free,
round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) Percent_free
from (select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_data_files
group by TABLESPACE_NAME) ddf,
(select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_free_space
group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME
order by ((ddf.BYTES-dfs.BYTES)/ddf.BYTES) desc;
****************** END-- Tablespace info script *****************
Monday, January 16, 2006
Oracle posts
This blog is to demonstrate my experiences with oracle. planning to post the new things i learn.
Subscribe to:
Posts (Atom)