Advertisements

Change an Oracle database user password

This is a simple post for how to change the password for an Oracle database user.  Every now and again we need to do this, but may forget how to or forget the exact syntax needed.

When you need to change the password for an Oracle database user, you can use the following command after logging into the database using SQL Developer, Toad, or SQL*Plus for example.

alter user [user_name] identified by "[new password]" replace "[old password]";

Put the double quotes around the password values, particularly if your password includes special characters.

You may also use a GUI option in SQL Developer. In SQL Developer, right-click the appropriate database and select “Reset Password” from the menu.
SQL_Developer_Reset_Password

And then enter the user, old and new passwords in the “Enter New Password” dialog.
SQL_Developer_ChangePassword_Dialog

I am updating this post to add another method for changing a user’s Oracle password.

In SQL Developer, when connect to the database, enter and run the command “password”.  This will then pop-up a “New password” dialog in which you can enter the new password.
OracleDatabase_ChangePassword_passwordcommand

Thanks for reading!

Advertisements

How to determine your database version for various RDBMS’: Oracle, SQL Server, MySQL, DB2

Occasionally you may need to check one of your database’s version for the purpose of creating a ticket with the software vendor, for checking compatibility with other software, preparing for upgrades, getting database client software, and other reasons.

Below are commands for identifying the version of your database for a few of the more popular RDBMS’s.  Please keep in mind that these may or may not work on your version of database or type of operating system.

 

ORACLE

  • SELECT * FROM V$VERSION;

Your output will be something like this …
version_output_oracle

SQL SERVER

Try one of the following:

  • Select “Help -> About” from the SQL Server Management Studio menu.
  • select @@version
  • You may also connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of SQL Server.

 

MYSQL

Try one of the following:

  • shell> mysql –version
  • mysql> SHOW VARIABLES LIKE ‘%version%’;
  • mysqladmin version  -or- mysqladmin –v

 

DB2

Try one of the following:

  • SELECT * FROM TABLE(SYSPROC.ENV_GET_INST_INFO());
  • SELECT GETVARIABLE(‘SYSIBM.VERSION’) FROM SYSIBM.SYSDUMMY1;

 

OBIEE data source types and data retrieval methods

OBIEE is capable of connecting to and retrieving data from a variety of data sources.  The type of data sources that OBIEE can connect to are OLTP, OLAP, Data Warehouses (ROLAP), and Files.

  • OLTP databases – these include the normalized-design databases including ERP, CRM and other LOB systems.
    – The relational databases supported are: Oracle databases, Microsoft SQL Server, IBM DB2, and Teradata Warehouse.
    – And the ERP/CRM sources supported are: Oracle E-Business Suite, Oracle Peoplesoft, Oracle Siebel CRM, Oracle JD Edwards, and SAP.  Note: any ERP/CRM system running on one the databases mentioned above can be supported, but those mentioned here are special ERP/CRM sources.
  • OLAP databases – these include dimensional-databases including applications based on dimensional databses.
    – The OLAP datases supported are: Oracle Essbase, Oracle OLAP, Microsoft Analysis Services, and SAP Netweaver BI.
    – And the OLAP applications sources supported are: Oracle Hyperion Planning and Oracle Hyperion Financial Management.
  • Dimensionally-modeled data warehouses – these are relational databases designed with a star-schema / dimensional model, on one of the 4 supported relational databases mentioned above.
  • Files – Microsoft Excel, XML files, Flat files.

The data retrieval methods used to connect to these sources are:

  • OLTP – SQL
  • OLAP – MDX
  • Data Warehouse – SQL
  • Files – ODBC

OBIEE has the ability to connect to multiple of these data sources at the same time, and the data sources can be of the same or different types.  So, for example, an OBIEE Server can source data from an Oracle 11g Data Warehouse, and from an Oracle Essbase 11g OLAP cube at the same time, and join the data together for user consumption.
Similarly, file datasources can also be added to provide additional information, for example from an external source, and joined to data from the other sources mentioned above.  This “joining” of data is handled by the OBIEE BI Repository and BI Server.

To the end user accessing the data from a front-end tool (Analysis Editor / Answers), it seems like a single data source. That is one of the features that makes OBIEE such a great tool particularly for heterogeneous database environments.

Error Message when try to perform actions in Oracle Enterprise Manager 11g on Windows XP

If you get the following error when attempting to perform database actions in Oracle 11g Enterprise Manager on Windows XP …

RemoteOperationException: ERROR: Invalid username and/or password

… then, assuming your Username/password combination is correct … and you can verify this by logging into SQLPLUS … follow these steps to resolve:

1. Open Control Panel
2. Administrative Tools -> Local Security Policy (Local Security Setting window opens)
3. Local Policies -> User Rights Assignment
4. Double click ‘Log on as a batch job’ (Properties window opens)
5. Click on ‘Add user or group’ (Select user/group window opens)
6. Add the username(s) including the domain name
7. Click ‘Check names’ to verify what you have added
8. Save (click OK, click OK)

Hope this helps.