Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    24

    Unanswered: Export the database schema / structure to a file server

    I would really appreciate if any of you can share your experience on the following:

    Database-1: Oracle 8i/9i and
    Database-2: SQL Server v65/7/2000

    I am looking for a solution to extract all the database schema/structure to a pre-defined location onto a file server. I wanted to configure this thru CRON job (for unix) and Microsoft Task Scheduler (for Windows). Also this solution should work for all databases (must work with Oracle, SQL Server). The exported file should not be just a dump file (.dmp), assuming only oracle can read .dmp file.

    What are the possible ways to accomplish this.

    I am sure, several tools would be available to accomplish this (like Embarcadero Change Manager), but solution may not be cost effective. Can we write any program to accomplish the same?

    I would really appreciate your inputs.
    Thanks, Madhavi

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    For what it's worth ... The times I have done this type of thing, it's been
    for a "realitly small" number of tables ...
    .. In Oracle, run SQLPlus and write the rows to a spooled text file that is TAB delimited (or comma delimited) ... Spool a file, issue select, close spool
    ... Repeat this for as many tables as needed ... This tab delimited file can be brought into SQLServer from DTS

    EXAMPLE:
    set linesize 120
    set header off
    set pagesize 0
    set feedback off
    set trimspool on

    spool c:\test.txt

    ... SQLServer - Create a DTS job that will write a tab or comma delimited text file for the tables needed ...

    HTH
    Gregg


    select username||chr(9)||sid||chr(9)||serial#||chr(9)||pr ogram from v$session;

    spool off

  3. #3
    Join Date
    Aug 2004
    Posts
    24
    Thanks brabham!

    Can I write a stored procedure with all these statements?

    How can I configure this thru CRON job (for unix) / Microsoft Task Scheduler (for Windows)? I am looking for ORACLE solution.

    This job has to run daily at some point of time.

    I would really appreciate your response.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    create a CRON job to execute

    SQLPLUS @TEST.SQL

    ---- TEST.SQL file ....
    connect username/password@service
    set linesize 120
    set header off
    set pagesize 0
    set feedback off
    set trimspool on

    spool c:\test1.txt
    select username||chr(9)||sid||chr(9)||serial#||chr(9)||pr ogram from v$session;
    spool off

    spool c:\test2.txt
    select username||chr(9)||sid||chr(9)||serial#||chr(9)||pr ogram from v$session;
    spool off

    exit

    HTH
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •