Oracle Database: unexpire schema or fix ORA-28001 without changing the password

In Oracle Database, to unexpire schemas, you will need to issue the command as SYS/SYSTEM:

ALTER USER <USERNAME> IDENTIFIED BY <PASSWORD>

For this to happen you will need to know the password of the user you are trying to "unexpire". There are situations however where we do not know the password of the schema we are trying to unexpire.


A work around is retrieving the hashed password of the schema and issue the ALTER USER statement in slightly different manner:

Using the SCOTT/tiger example below: 

STEP 1: Retrieve the hashed password of the expired schema

Connect as SYSDBA and run the query: 

SQL> select password from sys.user$ where name = 'SCOTT';

PASSWORD
------------------------------
F894844C34402B67

STEP 2: Run the ALTER USER command as below:

SQL> ALTER USER SCOTT IDENTIFIED BY VALUES 'F894844C34402B67';

If account is locked you may need to run the following as well:

ALTER USER SCOTT ACCOUNT UNLOCK;

Oracle - Move schema from one tablespace to another

Starting from 11G, Oracle offers a much simpler way to migrate schema for tablespaces with Data Pump utility. Previously you would have to export the schema and then drop the user re-import the schema and then rebuild all the indexes.

With Data Pump the process of switching tablespace is much simpler.

STEP 1: Export the schema using datapump

expdp system/system_password SCHEMAS=MY_SCHEMA DIRECTORY=DATA_PUMP_DIR DUMPFILE=MY_SCHEMA.dmp LOGFILE=expdp.log

Review the log to ensure the export is done properly.

STEP 2: Drop the user from database

 DROP USER MY_SCHEMA CASCADE;

STEP 3: Import the schema with REMAP_TABLESPACE

impdp system/my_schema SCHEMAS=MY_SCHEMA REMAP_TABLESPACE=SYSTEM:MY_SCHEMA_TBSPACE DIRECTORY=DATA_PUMP_DIR DUMPFILE=MY_SCHEMA.dmp LOGFILE=impdp.log

STEP 4 : Verify Tablespace Change & Validity of Objects

Check for default tablespace by running:

select username, default_tablespace from dba_users;

Also check if all objects are valid, if not compile them

SQL> select owner, status from dba_objects where upper(owner)='MY_SCHEMA';

Check the indexes:

select index_name, status from all_indexes where status ='UNUSABLE';

For more information, check out the white paper on Data Pump:

Quick Start Guide:
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/datapump11g2009-quickstart-128718.pdf

Documentation:
http://docs.oracle.com/cd/B12037_01/server.101/b10825/dp_import.htm

Reverse Engineering Oracle Database Schema to ER Diagram

In this post, I'll show how to use Oracle SQL Developer to reverse engineer a schema into a relational diagram. You will need SQL Developer v3.x for this.


1) Go to File >> Import >> Data Dictionary

2) This will open up the Import Wizard. Click on Add to connect to the Database which contains the schema you wish to reverse engineer.

3) Once you have successfully tested connection click OK.

4) Select the newly created connection and click on Next

5) You will be prompted for password again, enter and click OK. 

6) Now we select the schema. In my case I selected a schema called Tutorial that comes bundled with Oracle APEX.

7) Next we select the tables. You may select all tables or a handful to see the relationships between them. Here I wanted to see the relationship between the tables prefixed by OEHR (again comes with APEX).

8) Next you will get a view summary. Verify and click Finish

9) You will be asked for your logins for 3rd time in this process. Enter and click OK.

10) Review the log and close to view your ER diagram.

11) To print your ER diagram to image/PDF. Go to Data Modeler >> Print Diagram >> To Image File.

Useful Oracle SQL commands

This blog entry will be updated as I come across more common useful SQL queries.

View current schema:

select sys_context('userenv','current_schema') from dual;




Change Schema: alter session set current_schema=name_of_your_schema;



Grant access to tablespace to user (before a user created is allowed to create a table). grant all on all_tables to sys;



List of users who have privilege to create database linkSELECT * FROM dba_sys_privs WHERE privilege=’CREATE DATABASE LINK’
and admin_option=’YES’;


Grant permission to create database link privilegeGRANT CREATE DATABASE LINK TO sys WITH ADMIN OPTIONS



Unlock a schema or user accountALTER USER schema_name account unlock; 

Importing Database from a DMP file

imp schema_name/schema_password IGNORE=Y FILE=EXP_SCHEMA_NAME_SCHEMA.DMP LOG=IMPORT_LOG_FILE.log

Get a count of all objects in your schema

Pre-requisite: Must have access to schema you are querying on.

select owner,
count(object_type) "count", object_type
from ALL_OBJECTS
where owner = 'schema_name'
group by owner, object_type;


----

Replace schema_name with the name of your schema (in upper case). 

Lost APEX ADMIN password?

You need to locate and run the file C:\oraclexe\app\oracle\product\11.2.0\server\apex\apxchpwd.sql

If you are running your database in Linux, find the apxchpwd.sql file by

$ locate apxchpwd.sql

Then go to that directory and run SQLPlus.

Follow these instructions to recover your ADMIN password. Note this is not for reseting your workspace ADMIN account password. Its for resetting the password for ADMIN account for your entire APEX installation.

1. Open command prompt and cd to C:\oraclexe\app\oracle\product\11.2.0\server\apex
2. Run sqlplus from command prompt.
3. Run the command alter session set current_schema='apex_040000';

Run the command @apxchpwd.sql and enter your password.

You should now be able to log into http://localhost:8080/apex/apex_admin. APEX will ask you change  your password again after logging in.

Exporting Schema in Oracle Apex

You can export Schema from Oracle APEX by following these steps:

1) Head over to SQL Workshop >> Utilities >> Generate DDL

2) Click on Create Script

3) Pick the Schema

4) Check schema objects you wish to import or check all

5) Click on Genereate DDL