Useful Oracle Queries and Commands

In this blog post i would try to cover few of the import queries or commands of oracle.

Export/Import database

If you want to export/import database then you can use below commands from sql command line.

To start sql command from command prompt hit sqlplus command. You need to login with valid credentials before you execute any commands.



By following way you can use export command.

exp userid=system/password@XE schemas=SCHEMA_NAME dumpfile=DUMP_NAME.dmp logfile=LOG_FILE_NAME.log

or

exp userid=system/password@XE owner=SCHEMA_NAME file=DUMP_NAME.dmp


To import you can use below commands.

impdp system/root@XE schemas=SCHEMA_NAME dumpfile=SCHEMA_NAME_DMP.dmp logfile=client.log

or

imp userid=system/root@XE full=N IGNORE=Y FILE=C:/Users/SCHEMA_NAME_DMP.dmp

To know difference between exp/imp and expdp/impdp please follow below link.

Original Export and Import Versus Data Pump Export and Import


Unlock system user in oracle

To unlock system user run following commands.

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 26 15:53:22 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> alter user system account unlock;

User altered.

SQL>

Change password for system user

SQL> connect / as sysdba;

SQL> alter user system identified by <new_password>;

Reset password

If password is expired then using below commands you can extend the default limit for password.

Login as a system user and execute below query.

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;