Re: Importing and exporting

From: Bert Bear <bertbear_at_NOSPAMbertbear.net>
Date: Sat, 30 Nov 2002 18:24:54 GMT
Message-ID: <Wn7G9.475$vU3.488531090_at_newssvr12.news.prodigy.com>

Robert,

I have a client who has a similar issue on transferring schema/data/users. You use the term "databases (users)." They also use this term as their first database of choice is MS-SQL 2000, where a database is an user. When one of their customers needs something the customer sends them an export of the Oracle schema (e.g. Oracle username). My client then wanted to import the "database" (user) and do their work against their client's live data.

Is this something like what you are wanting to do? (Except you are also looking for an automated way to export.)

What I ended up doing was developing a REXX script (with SQL/REXX) to allow them to import the data into the Oracle server. Why not simply have them issue the import command manually? Several reasons:

1. Their customer would not send the "from user" field. They would have to discover this on their own. When I arrived, they would discover this name by editing with WordPad the dumped data and manually searching for it.
2. All the customer data was in one table space (30 GB or so). This presented several problems, including telling old from new data.
3. They wanted something simple to follow, either simple OEM instructions or a simple command. I choose the command route with good documentation.

The script works on 8.1.7.4 (and simply misses using some 9i features to be 9i enabled). Each client (imported schema) gets their own username and their own tablespace, which helps with clean-up, being orderly, and performance.

An outline of steps follows:

Outline of steps:

0) Template setup

1. Process passed parameter (config filename, if other than the default).
2. Process the config file (client_input.cfg default or any name passed by the end user).
3. Process the dump file.
4. Unzip dump file, as necessary.
5. Count = 1 unless existing listing/log file exists, then take next available number.
6. Test dump file and capture fromuser value

     imp userid=sys/tester
         show=y
         full=y
         file=<dump_filename>
         grants=no
         log=<username>_<date('s')_<count>.listing

  D) Extract fromuser from the listing file

4) Create a clean/new <client name> username and tablespace. If one already exists, the drop the object and its contents.

1. Connect as an user with DBA privileges
2. Collect system information for report
3. Database name ii) Database version
4. select username from dba_users where username = ':username';

     if no rows then go to step 7
      else drop ':username' cascade;

  D) select ts.name, df.name
       from v$tablespace ts,
            v$database df
      where ts.ts# = df.ts#
        and ts.name = ':username';

      if no rows then go to step 8
        else do
          drop tablespace ':username';
          end

  E) select ts.name, df.name
       from v$tablespace ts,
            v$datfile df
      where ts.ts# = df.ts#
        and ts.name = 'SYSTEM';

This is to obtain the location (drive, path) of where to put the tablespace. (Not good for performance but performance is NOT the point here.)

  F) oradataDRIVE = filespec('Drive',':df.name')
     oradataPATH  = filespec('Path',':df.name')
     oradata      = oradataDRIVE    oradataPATH

  G) create tablespace ':username'
       logging
       datafile ':oradataSID    :username    .ora' size 5M REUSE
       autoextend on next 1M
       maxsize unlimited
       extent management local;

  H) create user ':username' identified by ':username'
        profile default
        default tablespace ':username'
      temporary tablespace TEMP
           quota unlimited on ':username'
           quota unlimited on TEMP
         account unlock;

  I) Provide necessary grants to the Client username
     i)  connect

    ii) create procedure
   iii) create trigger

  J) Disconnect

5) Import the client data to the newly created username (schema) and tablespace.

     imp userid=sys/tester
         file=<dump_filename>
         log=<username>_date('s')_<count>.log
         fromuser=<fromuser>
         touser=<username>
         grants=no
         recalculate_statistics=y

6) Obtain number of tables inported to the client schema.

1. Connect username/username
2. select count(table_name) from user_tables
3. Disconnect
4. Template termination

As I say, this program exists but (unfortunately) I'm not at liberty to share it. I would be willing to consult (including writing an export/import version for you).

Whatever you decide, have fun at it!! (I found doing a script like this very FUN!!)

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net

"Robert Vabo" <rvabo_at_msn.com> wrote in message news:3de721e9$1_at_news.wineasy.se...
> What is the best way to import and export oracle databases (users) via a
> program ?
> Is it to use shell commands and imp/exp or is there some api call to use ?
> --
> Regards
> Robert Vabo
> Gecko AS
> www.gecko.no
>
>
Received on Sat Nov 30 2002 - 19:24:54 CET