Activities

April 2011
M T W T F S S
« Mar   Jun »
 123
45678910
11121314151617
18192021222324
252627282930  

Oracle Express edition installs and basic user administration

After a long time, I’m working with oracle database. I need setup oracle server installed on development server. Installation is not much complicated . Download the rpm respect to your OS distros and run ” /etc/init.d/oracle-xe configure” after the installs.

There few things you may concern while installing OracleXE.
1. Choose the Custom port for managing web administration.

2. It does support only 4GB user data storage and we can’t change the oracle data path. It should be in /usr/lib/oracle/xe/app/oracle.
3. You need to setup Oracle environmental variables (ORACLE_HOME and ORACLE_SID) I placed a file oracl.sh in “/etc/profile.d/”
/etc/profile.d/oracl.sh have the following contents

[root@rc-025 ~]# cat  /etc/profile.d/oracl.sh
   ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server; export ORACLE_HOME
   ORACLE_SID=XE; export ORACLE_SID
   PATH=$PATH:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin; export PATH
  [root@rc-025 ~]#

Then execute

 [root@rc-025 ~]# source /etc/profile.d/oracl.sh;

Once you have completed installation, you may either use Web UI or commandline to administrate the database. In Oracle there is no multiple database concept as we are working with MySQl or Postgresql. There is a single database (in EX) and the term “tablespace” is equivalent to the “database” concept. So we need to create tablespace for each projects. tables space is just allocating reserved space for all the tables created in that area.

Oracle administration portal doesn’t permit to create “table space” So I uses commandline to create it and execute it over the sql window from the Administration portal.

Login to oracle sql prompt

You may need to add the oracle binary path in system path. Execute it from the terminal.

PATH=$PATH:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin; export PATH

Type sqlplus from the terminal

[root@rc-025 ~]# sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 25 10:34:28 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter user-name: system
Enter password:
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL>

1. Creating tablespace for a user

create tablespace
temple
datafile
 ‘/usr/lib/oracle/xe/oradata/XE/temple_users_01.dbf’
size
50m
autoextend on
next 10m
maxsize 100m;

Here autoextend will allow to extend the tablespace when it over 100MB which is a recommended method in production mode.

1. How do I create a new user

 >create user temple identified by nH3iS7DnS default tablespace temple;

3. Grant limited access to that user

grant CREATE TABLE,CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE SEQUENCE, resource, connect to temple

4. How do I view all the privileged tables I created.

 select table_name from all_tables; or SELECT * from tabs;  or SELECT * FROM cat;

5. Backup: Export the dump owned by the user ‘admin’

 [root@rc-025 ~]#exp <user>/<password> FIlE=/opt/backup/temple.dmp OWNER=temple

6. Export all the databases

[root@rc-025 ~]#exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y

7. If you want to backup only specific tables

 [root@rc-025 ~]# exp <user>/<password> FILE=tables_bkp.dmp TABLES=(table1,table2)

8. Import the tables owned by the user admin to a new user ‘temple’

[root@rc-025 ~]#imp <user>/<password>  FILE=temple.dmp FROMUSER=admin  TOUSER=temple

9. Import all the database to a new server

[root@rc-025 ~]#imp SYSTEM/password FULL=y FILE=alldatabses.dmp

10. To import few tables from the backup schema

[root@rc-025 ~]#imp SYSTEM/password FILE=tables.dmp FROMUSER=temple TABLES=(table1,table2)

11. How do extend the user quota when it reaches the limit

   ALTER USER <user name> quota unlimited on <table space name >;

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>