Changing the default schema in Oracle

The application I was working on assumed that the user would always be in the default schema. I ran into a snag when I had to connect to the staging environment where the read only user that I was provided with didn’t have all the objects in his schema.

The solution was to make a call to change the default schema using the ALTER SESSION call with this code.

        /// <summary>
        /// Sets the schema to use if one is configured.
        /// </summary>
        public static void SetSchema()
        {
            var schema = ConfigurationManager.AppSettings["SchemaName"];
            if (string.IsNullOrEmpty(schema)) return;
 
 
            using (var connection = GetConnection())
            {
                using (var command = new OracleCommand("alter session set current_schema=" + schema))
                {
                    connection.Open();
                    command.Connection = connection;
                    command.ExecuteNonQuery();
                }
            }
        }
Changing the default schema in Oracle
  • Shame you can’t set this through the connection string, but this is a very nice workaround.

  • Kerry

    @Steven Algieri

    Could you tell me how to set schema in oracle connection string?

  • Thanks!

  • Juba

    How to implement the GetConnection() method? Thanks!