--grant select on v$session to user;
--grant select on v$lock to user;
my_cursor number;
my_statement varchar2(100);
result integer;
cursor c1 is
select 'alter system kill session ' || '''' || v.sid || ',' || v.SERIAL# || ''' immediate' command
from v$session v
where v.SID in (select distinct(l.sid) from v$lock l where
l.block = 1 and
l.ctime > itime);
-- select 'alter system kill session ' || '''' || to_char(a.sid) || ',' ||
-- to_char(a.serial#) || ''''
-- from v$session a, v$lock b
-- where a.sid = b.sid
-- and b.lmode = 6
-- and a.username like 'THE_BOREING_USER'
-- and b.ctime > time;
begin
open c1;
loop
fetch c1
into my_statement;
exit when c1%notfound;
dbms_output.put_line(my_statement);
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor, my_statement, dbms_sql.v7);
--dbms_output.put_line('1 - ' || my_statement);
result := dbms_sql.execute(my_cursor);
---- execute immediate sqltxt;
dbms_sql.close_cursor(my_cursor);
end loop;
close c1;
result := 0;
end;
/
where o.status <> 'VALID';
begin
--1分钟检查一次
DBMS_JOB.SUBMIT(:JOBNO, 'KILL_LOCKED_USER(90);',SYSDATE,'SYSDATE+(60/86400)',NULL);
COMMIT;
end;
dbms_job.remove('2');
end;
SQL> exec dbms_job.broken(2,true)
SQL> exec dbms_job.broken(2,false)
Wednesday, September 8, 2010
oracle: auto kill lock session
Tuesday, January 22, 2008
How do I see who is currently connected?
SELECT username, program FROM v$session WHERE username IS NOT NULL;
How to see the database version?
To view components and their versions, loaded into the database:
SELECT comp_name, status, version FROM dba_registry;
To view version numbers of core library components, including the database:
SELECT banner FROM v$version;
To display options that are installed (the value column is ‘FALSE’ if the option is not installed)
SELECT parameter, value FROM v$option;
Friday, January 18, 2008
How to set a table in read-only mode ?
ALTER TABLE emp
ADD CONSTRAINT read_only CHECK (1=1) DISABLE VALIDATE;
It is now, impossible to insert, update or delete anything with this table.
Oracle Stored procedures JAVA
Purpose : To demonstrate that Oracle database has an embedded JVM Objective: PL/SQL is great, but it is an Oracle Proprietary language that cannot be ported to other database platforms,, Java is portable Environment : The example uses SQL*Plus, but other tools also work Oracle Version: I Used Oracle9i, Oracle8i should work similarly SQL> CREATE OR REPLACE and RESOLVE JAVA SOURCE NAMED "Hello" AS SQL>SELECT hello (ENAME) from EMP;
public class Hello {
static public String Msg(String tail) {
return "Hello " + tail;
}
}
/
Java created.
SQL> CREATE OR REPLACE FUNCTION hello( str VARCHAR2 )
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME
'Hello.Msg (java.lang.String)
return java.lang.String';
/
HELLO(ENAME)
-----------------------------
Hello SMITH
Hello ALLEN
Hello WARD
Hello JONES
Hello MARTIN
Hello BLAKE
Hello CLARK
Hello SCOTT
Hello KING
Hello TURNER
Hello ADAMS
HELLO(ENAME)
-----------------------------
Hello JAMES
Hello FORD
Hello MILLER
Using Loggers in Java Applications
In order to add logging features to your applications, you can use apache log4j API
You need 3 things
1) have access to log4j jar file
2) have a properties file that will act as a configuration environment for the logging process
3) write the appropriate Java code
here is more details using the Oracle Jdeveloper IDE
Download log4j1.3.jar from http://logging.apache.org
Put the log4j.jar in the classpath, or add it to the Jdeveloper project as shown
You need to specify where the properties file is located, otherwise logging will fail
As an example, I have create a properties file and called it log4j.properties and added placed the file at C:\
You can use the project properties dialogue to define the location of the Properties file
Invoke project properties à Run/Debug à Edit the default setting à and add the Java option shown below
-Dlog4j.configuration = file:c:/log.properties
The following is a sample properties file in which the logging output is specified (log4j.appender.R.File=C:\x.log)
Log.properties
log4j.rootLogger=INFO, R
# first appender writes to a file
log4j.appender.R=org.apache.log4j.RollingFileAppender
#RollingFileAppender OPTIONS
log4j.appender.R.Threshold=INFO
log4j.appender.R.ImmediateFlush=true
log4j.appender.R.File=C:\x.log
log4j.appender.R.MaxFileSize=6000KB
log4j.appender.R.MaxBackupIndex=2
#log4j.debug=true
#log4j.disable=fatal
# Pattern to output the caller's file name and line number.
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%p %c %d{dd/MM/yyyy HH:mm:ss} - %m%n
#log4j.appender.stdout.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n
#log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n
Finally, this is an example code
package model;
import org.apache.log4j.Logger;
public class Class1 {
public Class1() {
Logger log = Logger.getLogger(Class1.class.getName());
log.warn("hello warning");
}
public static void main(String[] args) {
Class1 class1 = new Class1();
}
}
Note: the (Class1.class.getName()) retrieves the name of the current class that is being executes. Every logging message entry will have this variable printed so that the users can know which class was responsible for this entry.. off course, if you choose to use a string value instead, that strng value shall appear in each logging entry.
Thanks to Mr. Ammar for this Article..
Showing Calendar in SQL*Plus
Similar to UNIX cal command Thanks to Tony Davis for this tip Thanks to Mr. Ammar too
Thursday, January 17, 2008
Hidden columns
Interestingly, dbms_stats will collect statistics on hidden columns, eg, from a function based index, but it doesn't appear that you can actually retrieve them
SQL> create table T ( x number ) ;
Table created.
SQL> create index TX on T ( x+10 );
Index created.
SQL> select column_name from dba_tab_cols
2 where table_name = 'T'
3 and owner = user;
COLUMN_NAME
------------------------------
SYS_NC00002$
X
SQL> declare
2 srec dbms_stats.statrec;
3 DISTCNT number;
4 DENSITY number;
5 NULLCNT number;
6 AVGCLEN number;
7 begin
8 dbms_stats.GET_COLUMN_STATS
9 (OWNNAME=>user
10 ,TABNAME=>'T'
11 ,COLNAME=>'SYS_NC00002$'
12 ,DISTCNT=>distcnt
13 ,DENSITY=>density
14 ,NULLCNT=>nullcnt
15 ,SREC=>srec
16 ,AVGCLEN=>avgclen);
17 end;
18 /
declare
*
ERROR at line 1:
ORA-20000: Unable to get values for column SYS_NC00002$
ORA-06512: at "SYS.DBMS_STATS", line 3976
ORA-06512: at "SYS.DBMS_STATS", line 3991
ORA-06512: at line 8
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select column_name , num_distinct
2 from dba_tab_cols
3 where table_name = 'T'
4 and owner = user;
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
SYS_NC00002$ 0
X 0
Bulk collect on records
Lets start with a working example of bulk collect into records.
SQL> create table T ( c1 number, c2 number );
Table created.
SQL> declare
2 type r is record (
3 x number,
4 y number );
5
6 type rt is table of r;
7
8 d rt;
9
10 begin
11 select rownum, rownum
12 bulk collect into d
13 from all_Objects
14 where rownum <= 20;
15
16 forall i in 1 .. 20
17 insert into T values d(i);
18
19 end;
20 /
PL/SQL procedure successfully completed.
But what if table T has three columns, and we wanted to add the constant value "10" when we insert. Then we have problems because it would look like this:
SQL> declare
2 type r is record (
3 x number,
4 y number );
5
6 type rt is table of r;
7
8 d rt;
9
10 begin
11 select rownum, rownum
12 bulk collect into d
13 from all_Objects
14 where rownum <= 20;
15
16 forall i in 1 .. 20
17 insert into T values ( d(i).x, d(i).y, 10);
18
19 end;
20 /
insert into T values ( d(i).x, d(i).y, 10);
*
ERROR at line 17:
ORA-06550: line 17, column 28:
PLS-00436: implementation restriction: cannot reference .... etc
However, what we CAN do is use objects and then apply SQL to them. All we need is some objects to mimic our PLSQL types
SQL> create or replace type r is object ( x number, y number );
2 /
Type created.
SQL> create or replace type rt is table of r;
2 /
Type created.
SQL> declare
2 d rt; -- this is now pointing to a database definition not a plsql definition
3
4 begin
5 select r(rownum, rownum)
6 bulk collect into d
7 from all_Objects
8 where rownum <= 20;
9
10 insert into T
11 select x,y,10
12 from table(d);
13
14 end;
15 /
PL/SQL procedure successfully completed.
Importing Shapefiles into Oracle
shp2sdo.exe is the conversion tool for the specific platform. It can be downloaded at Shape2SDO Download and is available in /home/oracle/shp2sdo on taifun. A readme file is there, too.
Running shp2sdo.exe (even on LINUX) without any command line parameter works fine. You'll be asked a couple of questions. Here's an example:
oracle@taifun:~/shp2sdo$ shp2sdo.exe
shp2sdo - Shapefile(r) To Oracle Spatial Converter
Version 2.15 21-May-2004
Copyright 1997,2004 Oracle Corporation
For use with Oracle Spatial.
Input shapefile (no extension): ../tmpdata/gg_limburg
Shape file ../tmpdata/gg_limburg.shp contains 47 polygons
Output table [../tmpdata/gg_limburg]: gg_limburg
Output data model [O]:
Geometry column [GEOM]:
ID column []:
Use a spatial reference system ID (SRID) ? [N]:
Change tolerance value from the default (0.00000005) ? [N]:
Generate data inside control files ? [N]:
Target database Oracle8i? [N]:
Spatial Data requires more than 6 digits precision? [N]:
Bounds: X=[167507.730000,213448.380000] Y=[306838.822000,421214.230000]
Override ? [N]:
Processing shapefile ../tmpdata/gg_limburg into spatial table GG_LIMBURG
Data model is object-relational
Geometry column is GEOM
Points stored in SDO_POINT attributes
Data is in a separate file(s)
Control file generation for Oracle9i or higher
Spatial data loaded with 6 digits of precision
Conversion complete : 47 polygons processed
The following files have been created:
gg_limburg.sql : SQL script to create the table
gg_limburg.ctl : Control file for loading the table
gg_limburg.dat : Data file
It does the same as:
shp2sdo.exe ../tmpdata/gg_limburg gg_limburg -g GEOM -s 90112
-- except that the -s parameter was specified here.
To load the data into Oracle...
oracle@taifun:~/shp2sdo$ sqlplus developer/dev @gg_limburg.sql
oracle@taifun:~/shp2sdo$ sqlldr developer/dev control=gg_limburg.ctl
Monday, January 14, 2008
ALTER DATABASE BEGIN BACKUP
Today I have learned a new thing about backup and recovery on how to keep the entire database in backup mode, instead of issuing separate BEGIN BACKUP statement for every tablespace.
I know Oracle strongly recommend of using RMAN for backup and recovery . I thought, this would be good for the DBAs who still use the legacy method of backup, i.e. ALTER TABLESPACE BEGIN/END BACKUP and if they are not aware of this new command in 9i and 10g versions.
Starting with version 9i(I dont know the exact release), Oracle gives the facility to put the entire database in backup by simply using the following command:
ALTER DATABASE BEGIN BACKUP;
ALTER DATABASE END BACKUP; -- to exit from the backup mode.
In version 9i, the above statement can be used only when the database is mounted, not opend. In 10g, this behavior changes. The statement can be executed while the database is open.
-- The following has done with Oracle 10gR1
SYS OCM AS SYSDBA>alter database begin backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE
-- All the datafiles are in backup mode now
SYS OCM AS SYSDBA>alter database end backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE
-- All the datafiles are out of backup mode now.
Happy reading,
Saturday, January 12, 2008
What is Oracle Apps (ERP)?
Lets take an example. Suppose you are running a small grocery shop named “Janata Grocery”, so the typical operation as a shop owner is you basically buy groceries from some big seller and stock it in your shop. Now people come to your shop for day-to-day needs and buy stuff from your shop at a slightly higher price than what you originally bought and stocked it in your shop.
Ocassionally you may not be carrying items or run out of stock that people ask for so you make a note of it and promise the person to come back tomorrow and they will get their item. So far so good, now lets name some entities before we proceed and things get complicated. The big seller from whom you buy stock is called as Vendor, the people who come to your shop to buy things are known as customers, the stock in your shop is known as inventory.
So far we have identified few entities that play an active role in your day-to-day operations. As time goes by, your business expands and now you take orders over the phone and provide service to deliver the items to your customers, so you hire people to help you out in maintaining the inventory, do the delivery part and all the necessary stuff to keep the business running smoothly. The people you hire are known as employees.
So in this small shop, you typically manage the bookkeeping activities by hand using a notepad or something similar. Now imagine the same setup on a larger scale where you have more than 10,000 customers, have more than 1000 vendors, have more than 1000 employees and have a huge warehouse to maintain your inventory. Do you think you can manage all that information using pen and paper? Absolutely no way! Your business will come to a sudden stop sign.
To facilitate big businesses, companies like Oracle Corporation have created huge software known in the category of ERP (Enterprise Resource Planning) as Oracle Applications. Now coming to think of it, Oracle Apps is not one huge software, instead it is a collection of software known as modules that are integrated and talk to each other.
Now what is meant by integrated? First let us identify the modules by entities. For e.g Purchasing and Account Payables deal with the vendors since you typically purchase from vendors and eventually have to pay the dues. Oracle Purchasing handles all the requisitions and purchase orders to the vendors whereas Oracle Accounts Payables handles all the payments to the vendors.
Similarly Oracle Inventory deals with the items you maintain in stock, warehouse etc. Dealing with customers is handled collectively with the help of Oracle Receivables and Oracle Order Management. Order Management helps you collect all the information that your customer is ordering over the phone or webstore etc whereas Receivables help you collect the money for the orders that are delivered to the customers.
Now who maintains the paychecks, benefits of the 1000 employees? right! it is managed by Oracle Human Resources. So you get the idea by now that for each logical function there is a separate module that helps to execute and maintain that function.
So all the individual functions are being taken care but how do I know if I am making profit or loss? That’s where integration comes into play. There is another module known as Oracle General Ledger. This module receives information from all the different transaction modules and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc.
Just to simplify the explaination, when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items in your inventory it is transferred to GL as money coming in, when your customer sends payment it is transfered to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.
All the equipment, shops, warehouses, computers can be termed as Assets and they are managed by Oracle Fixed Assets. Initially Oracle Applications started as bunch of modules and as time passed by they added new modules for different and new functions growing out of the need for today’s internet world.
So if you come across a module that you are trying to learn and work on, first try to understand what business need is it trying to fulfill and then try to understand what the immediate modules that it interacts with. For e.g lets say you come across Oracle Cost Management module, you will learn that it helps to maintain the costs of items in your inventory and the immediate modules that it interacts with are Oracle Inventory (ofcourse), Oracle Bills of Material, Order Management and so on..
There is more to ERP than this layman explanation of a complex beast that does not justify a single bit but I wished I had this knowledge when I was thrown into Oracle Applications right after I graduated from college. Back then the only piece of software I had known to write was implementing binary trees, infix, prefix, postfix notations in pascal and TSRs (Terminate and Stay resident) using assembly.
Thursday, January 10, 2008
Installing Oracle Applications E-Business 11i on Windows XP / Windowws 2003 Server
Hi! you will be able to install EBS 11i on Windows XP/ Windows 2003 Server, if you just follow these instructions else you should not get into it.
System Requirements Hardware used.
Windows XP (professional or Home) Service Pack 2 with 1 GB RAM ( you can do it on 512 MB also but not at all efficient) and make sure you have NTFS file system.
HD min 150 GB (c’mon your system and staging also need some space don’t agree with who says it work 100 GB with vision DB). You can use external hard drive too.
Don’t listen to those who advise you to install EBS 11i without vision database. I know
you are installing all this for learning etc.
Very Important.
Make sure there is no oracle product installed on your system. No entry of oracle should be in windows registry except system default by windows itself. So it is recommended to use fresh system for this purpose. If you tried a failed installation before on your PC then reinstall the OS (Win XP) again otherwise you will waste your time.
There should be !!!!NO!!! JDK installed on your machine. EBS 11i will install JDK version on your machine otherwise you will get errors I don’t want to discuss all this here. Just NOOOO JDK on your machine.
Pre-requisite software requirements.
1. Microsoft VC++ - http://msdn.microsoft.com/vstudio/express/visualc/download/
while installing VC++ make sure it is done in C:\V98. No spaces in any directory
structure of any software you install as pre-requisite for Oracle Apps. Opt for Register
Environment Variables while installing VC++.
If you installed MS Visual Studio C++ 6.0 then make sure you installed service pack 6.
http://msdn2.microsoft.com/en-us/vstudio/aa718364.aspx
Copy LINK.exe from C:\V98\bin to C:\WINDOWS\System32
2. Perl - http://www.activestate.com/Products/ActivePerl/
Version 5.8 or more and use MSI package type.
Perl is only needed if you are using Oracle Media Pack. If you are downloading from oracle site then you can skip this installation.
2. CYGWIN - http://cygwin.com/
- Download setup.exe to your machine.
Run setup.exe and click Next, which will bring up the Choose Installation Type screen.
Select -> Install from Internet and Click Next
Select -> Root Directory C:\cygwin and select radio button
Install for -> All Users and Default Text File Type -> DOS / text -> Next Local Package Directory can be any but C:\cygwin is recommended. -> Next Direct Connection.
Click on Next again to get to the Select Packages screen, and select the following packages (click on to toggle):
a. All Default
b. Archive Default, plus manually select the zip package
c. Base Default, plus manually select the following extra packages: ash, coreutils,
diffutils, findutils, gawk, grep, sed, tar and which
d. Devel Default, plus manually select binutils, gcc, gcc-core, gcc-g++, make and
mktemp
e. Doc Default, plus manually select cygwin-doc and man
f. Editors Default, plus manually select vim
g. Interpreters Default, plus manually select gawk
h. Shells Default, plus manually select ash and tcsh
i. Utils Default, plus manually select cygutils and file
Click on Next again to download the selected files. It will take some time depending upon mirror site you selected and after download cygwin setup will be automatically starts.
After installation completed
Open folder C:\cygwin\bin
And rename followings
gawk.exe to awk.exe
grep.exe to egrep.exe
make.exe to gnumake.exe
gcc.exe to cc.exe
You can override the name if system says that awk or egrep or cc already exits just remove or move to some other place.
Set the Path to include the C:\cygwin\bin
C:\> set PATH = %PATH%;c:\cygwin\bin
And then check your PATH to see that it include C:\cygwin\bin and C:\V98\bin
C:\>echo %PATH%
Oracle Media
You should be having Lasted available Oracle media pack (recommended) or Software download from oracle site and follow the unzipping instruction as per oracle documentation.
Domain Name Configuration
1. Open file c:\windows\system32\drivers\etc\hosts. Enter a domain next to localhost entry you will be using this domain while installing Oracle Applications. I used mydomain so you can replace what ever you want. 198.168.1.102 is IP Address of my PC you should enter your machine’s IP. If you don’t know it you can find it.
C:\>ipconfig
Example
127.0.0.1 localhost mydomain mydomain.com
198.168.1.102 mydomain mydomain.com
2. Go to Control Panel -> Systems -> Computer Name Tab -> Click on Change -> Click on More -> Enter Primary DNS Suffix as ‘mydomain’.
3. Open Network connections, Select your Connection on which your machine is connected (You will find Wireless or LAN) and right click and select properties. Select Internet Protocol( TCP/IP) and click on to properties. Click on Advanced button and go to DNS tab. Enter DNS suffix for this connection as ‘mydomain’.
4. Restart the PC.
.Staging the Oracle
If you have Oracle Media Pack then
Insert the Start Here DVD in DVD Rom and go to folder
Stage11i\startCD\Disk1\rapidwiz in DOS Prompt and type
D:\Stage11i\startCD\Disk1\rapidwiz\perl adautostg.pl
And follow the wizard.
Recommended Steps but not necessary.
There steps are only if your installation fails due to any reason so you can restore your registry and system to pre installation state.
When Staging is done successfully then export the registry and save it on a file.
Start -> Run -> regedit-> File -> Export and select the location where you want to store it .
Take a Computer Restore Point.
Start -> All Programs -> Accessories -> System Tools -> System Restore ->Create a Restore Point -> Enter name of restore point for example ‘PreOra’ and Press the Create.
Restart the PC.
Installing the the EBS 11i
So far you have done a great job. Now time to get the job done.
Go to folder C:\Stage11i\startCD\Disk1\rapidwiz or where you have done your staging on hard drive and just click RapidWiz
On welcome screen -> Next and Select
Install Oracle Applications E-Bussiness 11i Check on Use Express Configuration -> Next
In Express Configuration Information screen enter the following:
Database Type = Vision Demo Database
Database SID = VIS
MKS directory = C:\cygwin\bin or where “cygwin\bin” is
MSDEV directory = C:\V98 or where VC++ was installed
Domain = mydomain or what so ever is your domain.
Oracle Base directory =C:\oracle or where ever you want to install oracle
applications
Follow the wizard and you should pass all the Pre-Install Checks
If not then you did not follow instructions above and help yourself.
If you passed all the checks then just Click Next and follow the Wizard and installation will be done in 5 steps and take about 2-3 hrs depends upon the system.
After the installation is completed successfully (it should be) then Post-Installation Checks will be preformed. If you passed all the checks It is really a Wonderful and You Rocks
If your system don’t pass the Post-Installation Checks even then don’t worry just smile coz installation is successful you need to restart your PC and Open Command Prompt open to staging directory c:\Stage11i\startCD\Disk1\rapidwiz
And now just type rapidwiz –restart
Follow the Wizard and Enter all the info as you enter before Now This time it will take few minutes and you should pass Post-Installation Checks
If still a problem just repeat above step again you will get to it . Restart the PC and enter the URL in web browser
http://hostname.mydomain:8000/
E-Bussiness Login Username = sysadmin Password = sysadmin
These are the successful installation steps. I practically have done all that. Thanks to Mr. Naveed
Cheers …..
ORA-12571: TNS Packet writer failure
Strange error!
My developer was running fine . why I got this error.
I had tried many solutions provided on web by oracle experts but unable to connect my developer to database. I had tried to remove developer from C:\orant directory and also from registry. Then I re-installed it. But I got same error. I made parameter sqlnet authentication … to “None” in “SQLNET.ORA” file - but unable to solve this problem.
At last I analyzed that after which event I was getting this error. That was installation of a software - Download Manager.
Actually it was interrupting services. At last I removed this software and connected successfully developer to DB.
Hope it will be helpful.
Installation / Re-Installation of Oracle Database 10g on Windows
1. Take back-up of data (Full Database).
2. Shutdown your database. (Shutdown Abort).
3. Take back-up of SP file.
(If you have set some parameters previously according to your environment)
4. Note the size and names of all table spaces.
5. Delete Database through wizard (If previously installed).
6. Run Installer and uninstall Oracle from your system.
7. Remove Oracle entries from registry.
8. Restart your system.
9. Remove Oracle Directory from System.
10. Check services from Administrative tools. Please verify that there should not any Oracle related service existing in the list. No TNS, no oracle home etc.
11. Reboot your system.
12. Run Oracle Installer and start installation. Follow wizard and select options according to your requirements.
13. When Database is created, verify your work by connecting to database.
14. Shutdown Database.
15. Restore SP file.
16. Start Database.
(Note if you find any error on startup, then you have to generate SP file from init.ora file)
17. Install patch from its installer and run the related scripts. (It will take around 45 minutes so no need to worry).
18. Reboot your machine.
19. Create table spaces by same name and size as we noted in step 4.
20. Load (Import) backup from dmp file. Create log file as well.
(View log file. If you find any error of “table space already exists”, then don’t worry. It is because we have created the table spaces our selves.)
Good Luck!!!
Wednesday, January 9, 2008
Inconsistency between DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_SEGMENTS views
A question has been raised on Technet forum that inconsistent information is being returned from Oracle dictionary views. OP is trying to relate used/free bytes returned by these dictionary views.
Information provided by OP:
Total bytes occupied by data files is: 11,010,048,000
Total bytes available are: 220,200,960
Total bytes used by objects: 10,989,076,480
Free Space + Used Space = 220,200,960 + 10,989,076,480 = 11,209,277,440
So, the sum of “Free Space” and “Used Space” is more than the “Total Space” available to the datafiles. The question is “How can I get more space than the existing one?”
Answer to the question goes here:
SQL> conn test/test
Connected.
SQL>
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPDATA TABLE
TEST_ERRORS TABLE
SQL> create table test1 as select * from tab;
Table created.
SQL> create table test2 as select * from tab;
Table created.
SQL> create table test3 as select * from tab;
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TEST2 TABLE
TEST3 TABLE
EMPDATA TABLE
TEST_ERRORS TABLE
Now, I will drop these newly created tables:
SQL> drop table test1;
Table dropped.
SQL> drop table test2;
Table dropped.
SQL> drop table test3;
Table dropped.
Now, query the dictionary views for used, free and allocated bytes/blocks information:
SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';
BLOCKS
----------
3712
SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';
SUM(BLOCKS)
-----------
3680
SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';
SUM(BLOCKS)
-----------
48
Opps, we get more space (3680 + 48 = 3728) than being allocated to the datafiles. Probably, by now you might have arrived to the answer, but let me reveal it to you.
OP hasn’t mentioned the Oracle database version but I am pretty sure it’s Oracle 10g or above. With Oracle database 10g, Oracle has added a new feature of recycle bin. When you drop an object it goes and stays in your recycle bin and will occupy the same amount of space. You need to purge the object to reclaim the space.
This is what is happening in this case. Dropped objects are still lying in the TEST_TS tablespace and being counted by the DBA_SEGMENTS view, whereas, DBA_FREE_SPACE correctly report the free space available.
Let me purge the dropped objects out of the recycle bin and rerun the queries:
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1 BIN$BEExbY8xS0aXH3U+e9XPDg==$0 TABLE 2007-12-18:13:42:36
TEST2 BIN$WGV0P4B4TaCUukiPyctTPg==$0 TABLE 2007-12-18:13:42:38
TEST3 BIN$1P4aTA1IR8ijw4btdRkmzw==$0 TABLE 2007-12-18:13:42:39
SQL> purge recyclebin;
Recyclebin purged.
SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';
BLOCKS
----------
3712
SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';
SUM(BLOCKS)
-----------
3680
SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';
SUM(BLOCKS)
-----------
24
Wow, Oracle now reports correctly. The sum of “Free Space” and “Used Space” (3680 + 24 = 3704) is less than the “Total Space” (3712) available to the datafiles.
Simulating 11g Snapshot Standby Database feature on Oracle 10g?
As we all knew that the Oracle 11g improved the capabilities of standby database immensely, where a physical standby database can easily open in read-write mode, which can be ideally suitable for test and development environments. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.
We initially said, we can open the database in read only mode for their testing, but, the requirement demands the database to be in read write mode. We thought, we can break the standby database for their testing and once the testing is done, we can rebuild the standby database again. We know that this is very well possible with Oracle 11g but not with Oracle 10g. My colleague, Mr. Asif Momen, did some R&D come up with a solution where a Oracle 10g standby database can open in read write mode and can also be reverted back to standby mode.
1. Set the following parameters on the standby database:
3. When the standby is in MOUNT mode, Create a guaranteed restore point:
CREATE restore point before_rw guarantee flashback database;
alter system archive log current;
alter system set log_archive_dest_state_2=DEFER;
-Make sure the media recovery process is turned off
-Minimize the protection mode to MAXIMUM PERFORMANCE, if the mode is set other than the MAXIMUM PERFORMANCE.
- Startup database in mount mode
- Flash back database to restore point using the following:
- Start the standby database in mount state and drop the restored point.
- Enable the Media Recovery on the Standby database.
- Activate log shipping on the primary using the following:
