Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2011
    Posts
    11

    Unanswered: Exporting database

    I'm using Oracle Database 10g Express Edition. I create all table and data are nicely inserted. In other word, it's ready to be exported for my lecturer. But how do I export it? I exported it in .sql format, but it always doesn't come with the data (only table). How do I export everything. TY

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle, unlike MYSQL, does not generate INSERT statements with all data.
    Oracle provide the export utility, exp

    Do as below from Operating System command line

    exp help=yes
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2011
    Posts
    11

    Re:Export

    Do you mean the .sql file that i exported already contain the data and table? Sorry if i'm sooo bad in that since I just learn it for a few weeks. TY

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ncode View Post
    Do you mean the .sql file that i exported already contain the data and table? Sorry if i'm sooo bad in that since I just learn it for a few weeks. TY
    Maybe it would help for better understanding, if you exactly described what "exported to sql" means.
    Which environment (sqlplus, SQL Developer, TOAD, ...) are you using?
    Which command or user menu sequence did you run?

    In Oracle, I am not aware of standard export to sql. Standard export (exp utility) exports to binary (.dmp) file - although it contains SQL and it is possible to spot it there.

  5. #5
    Join Date
    Sep 2011
    Posts
    11

    Re:

    I just doesn't sure what am i using. My lecturer just tole me to download Oracle Database 10g Express Edition. I just know that i put all my command in the black command box. Plz help! TY
    Click image for larger version. 

Name:	ScreenShot046.jpg 
Views:	8 
Size:	45.2 KB 
ID:	12281

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ncode View Post
    I just doesn't sure what am i using. My lecturer just tole me to download Oracle Database 10g Express Edition. I just know that i put all my command in the black command box. Plz help! TY
    Click image for larger version. 

Name:	ScreenShot046.jpg 
Views:	8 
Size:	45.2 KB 
ID:	12281
    And the commands are? Am I supossed to guess them?
    Maybe it is time to consult your lecturer, as he/she is the only one (no, nobody in this forum does it from your posts) who knows these commands. Or, maybe, wait until he/she enters this forum. Happy time meanwhile.

    By the way, if you looked in the left top corner, you would get the name of that tool (hint: I already included it in the list).

  7. #7
    Join Date
    Sep 2011
    Posts
    11

    Lost

    Example
    INSERT INTO stock (sto_id, sto_quantity, sto_mfgdate, sto_expdate, fd_id)
    VALUES ('K0001', '36', '04-Jan-2011', '05-Jan-2013', 'f0001')

    aw....really lost now.......especially exporting........

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK then, here's how it goes.

    First you connected to SQL*Plus (using Windows CMD window), created a table and inserted some records:
    Code:
    C:\temp>sqlplus scott/tiger
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Sub Lis 15 20:29:53 2011
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    
    SQL> create table test
      2  (id    number,
      3   name  varchar2(20));
    
    Table created.
    
    SQL> insert all
      2    into test (id, name) values (1, 'Little')
      3    into test (id, name) values (2, 'Foot')
      4  select * from dual;
    
    2 rows created.
    
    SQL> select * from test;
    
            ID NAME
    ---------- --------------------
             1 Little
             2 Foot
    
    SQL>
    Now it's time to export the table for your teacher.

    Still being in Windows' CMD window, but no longer in SQL*Plus. EXP is a utility that runs from the operating system's command prompt:
    Code:
    SQL> commit;
    
    Commit complete.
    
    SQL> exit
    Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    
    C:\temp>exp scott/tiger file=for_my_teacher.dmp tables=test log=export.log
    
    Export: Release 10.2.0.1.0 - Production on Sub Lis 15 20:33:35 2011
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)
    
    About to export specified tables via Conventional Path ...
    . . exporting table                           TEST          2 rows exported
    Export terminated successfully without warnings.
    
    C:\temp>dir for*.dmp
     Volume in drive C has no label.
     Volume Serial Number is F892-B9F1
    
     Directory of C:\temp
    
    15.10.2011  20:33             4.096 for_my_teacher.dmp
                   1 File(s)          4.096 bytes
                   0 Dir(s)   9.256.226.816 bytes free
    
    C:\temp>
    As you can see, 2 rows were exported into the DMP file. Now you only have to give it to your teacher. If you are supposed to FTP it, use BINARY mode. Otherwise, just e-mail it as an attachment, put it on a USB memory stick or whatever you want.

  9. #9
    Join Date
    Sep 2011
    Posts
    11

    Re:

    Thank you guys.I did it. I redo everything in a notepad and save it in .sql format. <----Really stupid? Anyway, thank you so much!

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Stupid? Probably not at this stage. However, if you had millions of records, that would be rather stupid. EXP (and in recent versions EXPDP - Data pump export) have been designed for such a purpose - export your tables, transfer them elsewhere.

    But OK - if your teacher is happy with bunch of SQL statements, OK with me. I hope you wrote them properly. For example: there's a table:
    Code:
    SQL> create table test
      2    (idc   varchar2(20),
      3     name  varchar2(20),
      4     edate date
      5    );
    
    Table created.
    I'll set NLS_DATE_FORMAT and insert a record:
    Code:
    SQL> alter session set nls_date_format = 'ddmmyyyy';
    
    Session altered.
    
    SQL> insert into test values (1, 'Little', '01022003');
    
    1 row created.
    Seems to be OK? Yes, but it is not, really.
    • INSERT should have a proper format - specify a complete column list (many columns? Too much typing? So what?). Suppose your teacher's table looks like this:
      Code:
      SQL> create table test
        2    (edate   date,
        3     name    varchar2(20),
        4     idc     varchar2(20));
      
      Table created.
      
      SQL> insert into test values (1, 'Little', '01022003');
      insert into test values (1, 'Little', '01022003')
                               *
      ERROR at line 1:
      ORA-00932: inconsistent datatypes: expected DATE got NUMBER
      Not really OK. INSERT should have been
      Code:
      SQL> insert into test (idc, name, edate) values (1, 'Little', '01022003');
      
      1 row created.
    • IDC is a VARCHAR2 column - Oracle implicitly converted 1 into '1' (but that's wrong - don't rely on implicit conversion)
    • Date value is '01022003', which would be the 1st of February 2003. Now suppose your teacher has a different NLS_DATE_FORMAT and tries to insert this record:
      Code:
      SQL> alter session set nls_date_format = 'yyyymmdd';
      
      Session altered.
      
      SQL> insert into test values (1, 'Little', '01022003');
      insert into test values (1, 'Little', '01022003')
                                            *
      ERROR at line 1:
      ORA-01843: not a valid month
      Ooops! I tried to insert 3rd of 20th month of 102nd year. Not a valid month. Don't rely on someone's default date format. Oracle tries to implicitly convert your STRING (yes, '01022003' is a string, it is not a date) into a DATE, but fails. Always specify date format!

    Finally, a correctly written statement - regarding the table description - would be
    Code:
    SQL> insert into test (idc, name, edate)
      2    values ('1', 'Little', to_date('01022003', 'ddmmyyyy'));
    
    1 row created.
    It is obvious what datatypes are provided, in which format. So, if your INSERT statements don't look like the last one, maybe you should consider fixing them (or simply use EXP utility).

  11. #11
    Join Date
    Sep 2011
    Posts
    11

    Re

    Thanks. In fact, my teacher wanted my assignment in .sql only. ya, it look very long time to complete it. Gonna ask her why dont use EXP. Thank you for your guidance. Appreciate it so much!

  12. #12
    Join Date
    Mar 2007
    Posts
    623
    I would say it is very wise to do create scripts of your work in pure SQL - it is very close to real development practice. Although, for importing millions of rows, it would be more convenient to use SQL Loader (sqlldr) tool. You may also find more transparent to split the code into more files (e.g. one for each table, separate DDL from INSERT statements, ...) and call each of them from the main script in correct order. It is hard to recommend this without knowing the required format - one file may be fine for a small project.

    Database is not the storage of code - it shall be written in scripts and stored in any revision control tool, especially when multiple people are working on project. And it happens often - making software is a team work.

    Even with your code - just imagine that, after finishing this assignment, you will get another task based on your previous work. If you do not have access to your current data (accidentally dropping it/disk damage/working on different place/whatever), it is a matter of a while to restore it from scripts. Definitely much better than reimplementing it again. The same matters for fixing of previous code.

    EXP/IMP is fine for one-shot export/import data, however not suitable for saving of your (partial) result of work. For any change in it, you would have to run IMP, examine what was created, correct it in database and run EXP again. For testing, again run IMP to propagate changes into database and so on... Need to synchronize multiple developers even when working on different parts of the application.
    In SQL scripts (if transparently written), you may spot everything in the text, make required changes directly in scripts and run it then only for test purpose.
    Yes, if those objects/data exist, those scripts will fail. Most probably, IMP will fail too. This is another wide area of application maintenance; for simplicity you may assume always running that scripts in the newly created user.

    As I spotted in your sqlplus session, you are logging in as SYSTEM. It is not recommended - you should create new user (like e.g. HR), grant him needed privileges (CREATE SESSION, quota on USERS tablespace, CREATE TABLE, ...) and use that new account for your assignment. It will be simple to drop that user, create it again as SYSTEM (it may be put into a SQL script too) and run your script to check its correctness.

    Without storing your code outside database, you might end like LogicGuru in this thread (rewriting your work from the beginning):
    http://www.dbforums.com/oracle/12036...rocedures.html

    As these terms may be new to you, you may ask your lecturer for deeper describing them (or wait until you start studying those topics).

  13. #13
    Join Date
    Sep 2011
    Posts
    11

    Re

    Thank you for your advice.

Posting Permissions

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