Welcome to the MiDatabase Service. Here are some important steps designed to assist you with the initial access and administration of your database.
Connect to the Oracle Database
NOTE: You can only connect to an Oracle database within the U-M Virtual Private Network (VPN).
After connecting to the U-M VPN, you can begin working with your database through the following options:
- Use an Oracle client or command line SQL utility such as Oracle SQL Plus. Download Oracle's instant client here.
- Access the SQLPlus Utility on the ITS managed Linux server, whisper.web.itd.umich.edu. You can SSH to whisper.web.its.umich.edu authenticating with your uniqname and UMICH password to start using the Oracle SQL Plus command line tool.
- Use Oracle SQL Developer, a free graphical Java tool distributed by Oracle. This tool can be installed on your desktop computer (Windows, Linux, or Mac) or on one of your servers. After SQL Developer is installed, use it to connect to your source and target databases. Download Oracle SQL Developer here.
Change Your Password
After you connect to the database for the first time, you will need to change your password. It is advised that you change your password every few months.
sqlplus > alter user username identified by newpassword;
Manage Permissions
Shared Database customers can create database objects (e.g. tables, indexes, views, procedures, functions, etc.) and will be assigned 10 GB of disk space for these objects. If more space is needed, up to 50GB can be requested. If this amount is exceeded, it is recommended you move to a dedicated database instance.
Creating Users and Granting Permissions
Shared database customers can create database objects (e.g. tables, indexes, views, procedures, functions, etc.) and will be assigned requested disk space for these objects.
Only dedicated database customers can create users and grant permissions.
If additional user profiles need to be associated with your account, contact the ITS Service Center or submit a service request online.
Dedicated Database customers can connect as a privileged user account to create tablespace and users:
Create tablespace myusers
Datafile '/u01/app/oracle/oradata/myusers_o1.dbf'
Size 50m maxsize 110m uniform size 10M autoextend on;Create user appuser identified by <pwd>
Default tablespace myusers
quota 100M on myusers'Grant connect, resource to appuser; (allows the user you created to connect to the database)
For more information on account management, visit the Oracle site.
Export/Import Data
Oracle's export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database.
These utilities can be used to move data between different machines, databases, or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. You cannot export data and expect to import it into a non-Oracle database.
Various parameters are available to control what objects are exported or imported. To get a list of available parameters, run the exp or imp utilities with the help=yes parameter.
Look for the "imp" and "exp" executables in your $ORACLE_HOME/bin directory. You can run them interactively, using command line parameters, or using parameter files. Look at the imp/exp parameters before starting. These parameters can be listed by executing the following commands: "exp help=yes" or "imp help=yes".
The following examples demonstrate how the imp/exp utilities can be used:
exp scott/tiger@targetdb file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger@targetdb file=emp.dmp tables=(emp,dept)
imp scott/tiger@targetdb file=emp.dmp full=yes
imp scott/tiger@targetdb file=emp.dmp fromuser=scott touser=scott tables=dept
Yes, you can use the QUERY= export parameter to selectively unload a subset of the data from a table. You may need to escape special chars on the command line, for example: query=\"where deptno=10\".
For example:
exp scott/tiger@targetdb tables=emp query="where deptno=10"
exp scott/tiger@targetdb file=abc.dmp tables=abc query=\"where gender=\'f\'\" rows=yes
ORA-00001: Unique constraint (...) violated
You are importing duplicate rows. Use IGNORE=YES to skip tables that already exist (imp will give an error if the object is re-created).
ORA-01555: Snapshot too old
Ask your users to STOP working while you are exporting or try using parameter CONSISTENT=NO.
ORA-01562: Failed to extend rollback segment
Create bigger rollback segments or set parameter COMMIT=Y while importing.
IMP-00015: Statement failed ... object already exists...
Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.
- What is import/export and why do I need it?
- How do I use the import/export utilities?
- Can I export a subset of a table?
- What are the common import/export problems?
For more information on Oracle's export and import utilities, visit the Oracle site.
Join the MCommunity Group
To ensure that you are aware of all system maintenance issues and announcements, please join the MiDatabase Oracle Notify MCommunity group.
Additional Resources
Getting Help
For help, please contact the ITS Service Center:
- 4-HELP (734-764-4357)
- Submit a Service Request Online