Oracle Database 11g and TFS (2010) integration

oracle

I am currently in a team of 4 database developers, working with Oracle Database 11g Enterprise Edition. We use quite a bit of SQL/PLSQL and create many scripts. My colleagues send all their work to a specific person before they can be deployed in a production environment.

Instead of bombarding a colleague like this, we'd like to use a version control tool (Team Foundation Server 2010 in our case), pretty much as a code repository, where our work can be saved on a regular basis.

Since noone — including me — has any experience with TFS, I'd like to ask:

How can we best deploy TFS (or perhaps any other version control tool) in a development and test environment, as a way to manage our work? Any downsides/advantages, any specific points to take into consideration, any tips/pointers?

This is more of an organisational exercise, rather than a technical one. So my current focus is not the installation, etc. but to find the best way to utilize version control in a small team.

Thanks!

EDIT: Perhaps you can also answer the question by recommending several TFS-specific features that might be useful to (Oracle) DBAs and developers.

Best Answer

If TFS is in the picture then I assume Visual Studio is there also and Oracle creates development tools for Visual Studio (VS) that will allow you to VS with TFS. With this, you could then create Oracle Database Project in VS and maintain SQL scripts.

Related Solutions

How to allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects

The SELECT ANY DICTIONARY privilege (or, in earlier versions the SELECT_CATALOG_ROLE role) gives a user privileges to select from any data dictionary table.

The SELECT ANY DICTIONARY privilege would give a developer privileges to write whatever queries they'd like against DBA_SOURCE to see the source for any object, DBA_VIEWS to see view definitions, etc. But there is no guarantee that a particular front-end would actually leverage those privileges correctly. Far too many GUIs simply select from the ALL_* data dictionary tables to display the objects that a user has object privileges on rather than recognizing that the user has permission to select from the DBA_* data dictionary tables. Personally, I'm more than happy to query the DBA_SOURCE view (or use the DBMS_METADATA package) when I want to look at object source in a production database but lots of folks get antsy without the GUI schema browser.

Oracle 11g – Create Database fails

One of the key differences between these platforms is that a database in MySQL is a schema in Oracle. Since a schema in Oracle is also a user, the command you are looking for is CREATE USER. I highly recommend that you read the Oracle Concepts Guide before continuing. You may also find this documentation on MySQL>>Oracle migrations using SQL Developer useful. Here is the part relevant to your situation:

When migrating MySQL databases to Oracle, SQL Developer maps each MySQL database to a tablespace in Oracle. Database objects, such as tables, indexes and views are stored in the respective tablespaces and are referenced from the Oracle schema for the user that owns them.

From the error message perspective, the salient part to pull out is "ORA-01100: database already mounted". This indicates that a database has already been created and mounted. It may also be open. Here is an example reproducing your issue, opening the database (which may not be necessary if it is already open) and creating a django user.

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted


SQL> SELECT status FROM v$instance;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT status FROM v$instance;

STATUS
------------
OPEN

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted


SQL> CREATE USER django IDENTIFIED BY "No More ORA-001100.";

User created.

You should also assign permissions and create a django tablespace setting it as the default tablespace for django.

Related Question