I read a recent article about the way in which an table name supposedly could not have the same name as a the database name  in SQL Server 2008, this was possibly valid at some time in a previous version. Now most are aware that objects in a different schema in an Oracle DB can have the same name, but can a table and a view, or a table and an index have the same names. So here is a few little tests and lets see what I found.

CREATE USER test1 IDENTIFIED BY test1 DEFAULT TABLESPACE test1;

GRANT CREATE TABLE TO test1;

GRANT CREATE TYPE TO test1;

GRANT CREATE VIEW TO test1;

GRANT CREATE SESSION to test1;

Log in as user TEST1

SQL> create table test1(                                     2  test1 varchar2(10),   3  test1 number(10,2)); test1 number(10,2)) * ERROR at line 3: ORA-00957: duplicate column name Ok thats fair enough

SQL> create table test1(   2  test1 char(10),   3  test2 varchar2(10),   4  test3 number(10,2));

Table created.

SQL> create index test1 on test1(test1);

Index created.

create view test1 as (select test1 from test1); create view test1 as (select test1 from test1)             * ERROR at line 1: ORA-00955: name is already used by an existing object

And that one makes sense as well

So it really makes sense when you think about it if I want to select from an object in the context it is used eg select test1 from test1 is valid to I can have a column in the table test1 called test1 I can have an index the same name but I could not have a link in this schema as test1 as that would be confusing with this schema. I could not have a global link in the database as selecting from the test 1 schema or the Link would not differentiate, I can however have any number of the same object names across many schemas, many developers know this often having their own schema in the database for the code they are working on.

Juast a side note I saw in a Don Burleson article a mention that GRANT CREATE INDEX doesn’t work in some distributions, I found that to be true in my 11.2 distribution on Linux x64. It would appear that the GRANT CREATE TABLE provides the support for creating indexes.

I hope this might help in someone understanding of using object names in Oracle databases.

Oh what was the answer with SQL Server, well it was like Oracle a database and table name can be the same.

 

See ya round

 

Peter

Hi

I recently had to do some installations on a windows server running on a VM. This made me have a look at the VSS technology and at the end can to the conclusion that VSS has limited usefulness for most Oracle sites. Now someone might correct me and show what is missing in my understanding but without regular say hourly snapshots to another place its adds little value. Wouldn’t a daily rman backup and regular backups of archive logs provide a better alternative?

I understand some SANS can provide SAN mirroring with the use of VSS, but to do this the database still have to be quiesced. From what I looked at Data Guard is a much better solution for a recovery from many failure situations. Doing snapshots with VM technology leaves you with a potentially degraded database, some testing suggested in a slow system, that is one with a very low transaction rate it perhaps will be recoverable. Some testing I did left me with  a question mark over what might happen in the event of  a problem occurring.

I ran some tests and had it snapshotted whilst conducting a number of transactions using Jmeter to create some traffic on the database without using VSS to determine if a database was recoverable from a snapshot, in each case as the redo logs were ok the database recovered and started, I identified that this was only good luck as in different circumstances this could easily been in a situation whereby this database became unrecoverable, basically what had happened was all my redo logs were still at the same SCN number and as such the database simply recovered as when a shutdown abort occurs.

 

Oracle provides a fantastic tool in RMAN and Data Guard if you need fast recovery from some situations and using VM snapshots with VSS  or without may not be your best option, check out thoroughly any recovery scenarios you may need to handle and especially your lost time window when you cannot apply logs before choosing one over the other. I am sure in many cases Oracle will provide you with better alternatives than external tools such as VM snapshots.

 

What has been your experiences and what paths have you chosen and why? I am interested to hear

 

 

See ya round

QLD Performance by Design + Optimising Oracle on VMWare, by Guy Harrison (Quest Software): Monday, May 24, 2010 03:00pm Where: Oracle House, Level 14 300 Ann St Brisbane When: Monday, May 24, 2010 03:00pm – 05:30pm RSVP: RSVP to vicepresident.qld@ausoug.org.au to advise your attendance by close of business on 20-MAY-2010. Alternatively, indicate your attendance now: Yes No No, not interested in topic

Guy Harrison, Director of R&D at Quest Software, will be presenting two great topics at our May meeting.

The first topic Guy will be sharing his insights on is Performance by Design and the second is Optimising Oracle on VMWare. We have also invited members of the VM User Group to attend, given the shared interest area.

Agenda:

3:00pm Welcome 3:10pm Performance by Design 4:00pm Tea break 4:15pm Optimizing Oracle on VMWare 5:00pm Questions, networking 5:30pm Finish

Guy Harrison is a director of research and development at Quest Software, and has over twenty years experience in application and database administration, performance tuning, and software development. Guy is the author of Oracle Performance Survival Guide (Prentice Hall, 2009) and MySQL Stored Procedure Programming (O’Reilly with Steven Feuerstein) as well as other books, articles and presentations on database technology. Guy is the architect of Quest’s Spotlight® family of diagnostic products and has contributed to the development of other Quest products such as Toad®

Please use the buttons on this email to RSVP for this event – we appreciate your co-operation and it helps greatly for catering purposes.

See ya round

Peter

The Melbourne conference is over and had a great time. It was great to meet up with some old friends and meet some new ones.  As I have in recent times been doing much more work with Middleware I opted to do a presentation on the first day which went all day with the title of “SOA for DBA’s” presented by Lucas Jellema for Amis in the Netherlands. For anyone who works with Oracle databases and has an interest in working more with SOA I recommend this. If you need to learn a bit of what it is about to help provide better support to the middleware people working with SOA this is a presentation well worth the time. From someone who was  interested I got my moneys worth on this one day of the conference.

After a bit of effort to learn some of the material I will hopefully be able to present some of that to the local Oracle consulting people via the Queensland AUSOUG section.

The next day I was fulfilling my obligation to support the conference by taking up the role of a room monitor for the day. I was provided the opportunity to support Peter Koletzke with his presentation of “A Guide to Fusion Web Development with JDeveloper 11 ” this was another full day session and he provided us with an insight into how far Oracle’s vision of Forms development with Java has come. I enjoyed this presentation as it has further expanded my idea’s of the future of Oracle middleware as a platform. I did not realise how simple it had become to develop applications with 11g. Whilst Java purists might baulk at the idea that I  can create a full working application without writing a line of code, I think it is brilliant. A large part of the application can be done but screen builders, but the stuff I might need a Java expert for would the the sort of stuff any Java expert would want to get there teeth into. This is going to be great for Agile development going forward as in a day I could knock up functioning screens for an application to present to a client for initial review, using the functionality of Jdeveloper 11g as it now stands.

So my summary was is that I had a great conference. I did learn a lot of great new information and will certainly be expanding my skills further into the middleware space as it sure is going to be exciting in the near future as projects come online. It was great to catch up with Connor and Chris and others.

I have a bit of video and some photos to go through to see if there is anything that came out good enough for publishing online of the conference

A patch update came from Jdeveloper 11g came out in the past few days It is probably worth the upgrade as there  is a few glitches in the original release 11g Jdeveloper

See ya round

Peter

There is a lot of information to know and understand about Oracle Security and the internal database features. The security features that i will discuss here are those that are internal to the database and in future articles I will look deeper into some of these articles. Transparent Data Encryption (TDE) is the ability to encrypt data internal in the database so it appears only as encrypted strings to those that don’t have appropriate privileges. The data is encrypted by column so only that information that is truly sensitive such as the credit card number or health care number that could identify a patient is encrypted. The encryption requires the use of the wallet. Ideally it would be kept in a separate file system which increases the level of difficulty of it being recovered along with the database from backups in the event of it being stolen. It need not be as the master key cannot be retrieved without the wallet password. The great feature of Transparent Data Encryption is that it works without altering any application code or features. A few quick changes, you can enable transparent data encryption in around 30 minutes with a few simple changes.

Proxy User is another great feature that is there to allow middle tiers to connect to the database with restricted privileges. This then means that a connection to the database from the middle tier that is compromised in an attack as not so likely to expose the data. The other feature that this brings is that in increases the audit capabilities from a three tier application. This feauter from 10gR2 is available via both thick and thin client. There is credential proxy which requires certificates and Internet Directory to associate the certificate with the LDAP DN for the user. The other feature in this is Application user proxy. Many application servers use a connection pool that is using a single user to connect to the database. This allows application users to be traced within the sessions created with the connection pool Using Internet directory is an additional feature as identification of users can be very clearly defined and privileges are able to be managed from central privilege sets. Another useful feature is the shared schem user that can be configured in the Internet Directory This user is able to be mapped along with any others to a single schema and provided with a role that controls what that user has access to. These would be great for use with tools like Discoverer, where users could be logged into a schema with managed views of data and then through applied privileges have data access restricted.

Other features to discuss are Virtual Private database and Row Level Encryption, then there is Auditing and fine grained auditing to boot, which allows very intense scrutiny of what people are looking at.  When Oracle 10g came out there was additional auditing of the SYSDBA account and Oracle Vault has also been added to the mix. These are all features to protect your data within the database. There are still many external features available to protect communications to the database and protect the data at rest in backups or elsewhere. In wrapping up Oracle has many security features internal to the database that allow the DBA to configure a level of security for the businesses needs to protect important data. I will discuss this in greater details in future articles on security that I have coming up

See ya round

Peter