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

AUSOUG QLD is running the FNQ roadshow again. This is a chance for those in Central and North QLD to get a chance to see a number of presentations from Oracle and Implementers of Oracle solutions. if you are in the north of the state I would recommend you making an attempt to attend these presentations

The location, venue and timing details are as follows:

Rockhampton – kindly sponsored by SPARQ/Ergon Energy:

Date: Tuesday, July 20th Time: Starting at 12:30pm, concluding by 5:30pm Venue: Ergon Energy Building, Barcoo Room Level 1, Cnr Alma & Fitzroy Streets Rockhampton Townsville – kindly sponsored by James Cook University: Date: Wednesday, July 21st Time: Starting at 12:30pm, concluding by 5:30pm Venue: TBC, James Cook University, Townsville Cairns : Date: Thursday, July 22nd Time: Starting at 12:30pm, concluding by 5:30pm Venue: TBC Similar to last year, there will be presentations relating to database and middleware, with potential additions this year for applications, SOA governance and Primavera. Invitations will be sent separately, for each location, within the next two weeks … please stay tuned for more information and the final agenda. For more information or if you are not a member or are from interstate and in one of these locations at the time and would be interested in attending send an email to the Vice-President of the QLD section to get further information. Other Information from the Bulletin AUSOUG QLD Committee 2010 Mark Lancaster President Loretta Bayliss Vice-President Michael Krogh OAUG Representative Jon Gooding Committee Member Kris Downey Committee Member Peter McLarty Committee Member Victoria Clarke Committee Member Cameron Hawthorne Committee Member

We have had some very successful events already this year, which include:

Creative Conditional Compilation – sponsored by Sage Computing Services

Scott Wesley of Sage Computing Services in WA presented on “Creative Conditional Compilation” in an evening timeslot in March. The change from a daytime to evening event had a very different vibe, harking back to the traditional “beer and pizza” get together. Scott commented on how well the beer was going down midway through the presentation, and everyone stayed around after the presentation for far longer than a daytime event. Expect to see one or two more evening events later in the year.

R12 Forum – sponsored by Prescience Technology

March saw us run the second of our “R12 Forum” invitation events, with a focus this time on patching, post-upgrade complexities and the sustainability of R12 solutions.  This event was conceptualised as a result of a number of similar organisations, all of which have been early adopters of Oracle’s R12 upgrade roadmap, expressing a strong desire to consult closely with each other with regard to issues, experiences and resolutions they had each encountered.  The event has now evolved into a National Conference Call and presentation, which we aim to run quarterly, for the forseeable future.  Interest has been strong from organisations seeking more information on the upgrade and re-implementation process … please let us know if you would like more information.

APEX Training - sponsored by Sage Computing Services

Both the 3 day beginner and 2 day advanced Oracle APEX training courses in April were well attended, and the feedback received has been very positive. The special discount we arranged through Sage Computing Services meant the more attendees, the cheaper the course became. Final pricing saw a saving of approximately 40% for each course – a great result.

EU says U.S. comment on Oracle, Sun deal unusual | Reuters

The line in this article made me wonder as to what it was that the EU was trying to really protect with this deal. I originally thought it was SAP, as they had an arrangement with MySQL, however I see that they may have closed that arrangement in 2007, cant keep up with all the news. You have to wonder though at the comment “It could be very difficult for a competitor using MySQL codes to sufficiently replace the competitive constraints currently exerted by MySQL in a timely manner,” What competitor are they referring to if its not SAP. What other application companies such that there existence or failure would have ramifications within Europe to suggest that Oracles management, I wont say control of MySQL, would impact a competitor. Does BAAN use MySQL or some other EU application developer with a sufficiently large market share?

But then just as some were not entirely happy with Suns management of MySQL they forked the codebase to do as they wanted. The same will happen under Oracle if Oracle cannot manage the product for the betterment of MySQL and the community. Many will then commence migrating their websites etc to the fork as word extends of Oracles troublesome ways. MySQL will move on and Oracle will be left with a brand and there own version. Don’t forget and old adage, “keep your friends close and your enemies closer”. This would seem to be a great example of where Oracle will need to work hard to do just that.

I think it is a load of rubbish and the EU is stalling for an EU company and regardless SAP would seem to be the one with the most to loose.

If I was a shareholder of SUN stock I would be seriously questioning this and whether the damage that it is inflicting on the company warrants a lawsuit.

So EU fess up as to the real motive or step out of the way

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

I am installing the 10.2.0.5 Agent for Oracle Grid Control and found the following as just a little humorous. You see the one off patch is showing as optional. Now that would be good if I had been given a chance to choose to install the “Optional” one off patch, but at no time leading up to this little screen showing up was I informed that a one off patch existed and it was in fact optional to install it. Now I am not sure about you but Oracle has decided that a one off patch needs to be installed in the process of installing the Agent or any other software, then I am not likely to finish the installation and then first thing go and remove that patch as it is “Optional”.   Ok it must have been a slow morning to find it funny. Enjoy the thought that you can have optional when its not really. I guess I can now run OPatch and find out as the the merits and remove it if I want however that might mean other issues down the track so I think I will just leave the

Image via Wikipedia

optional patch there