8 Replies Latest reply: Mar 11, 2013 8:27 AM by Srini Chavali-Oracle RSS

    unable to export empty tables

    736935
      I use this command:

      exp userid=clix_db file=clix.dmp log=dump.log consistent=y

      to export all tables from user "clix_db".
      But when I take a look into the log file, I notice that not all tables have been exported. All empty tables are missed.
      e.g. the table "LIC_REPORTLOG" does exists! OK, the table is empty.
      But it is not exported. When I import the clix.dmp into another empty database, there is no table "LIC_REPORTLOG"
      When I export only this table with

      exp userid=clix_db file=clix_lic.dmp log=dump_lic.log consistent=y tables=LIC_REPORTLOG

      I get an error message:
      EXP-00011: CLIX_DB.LIC_REPORTLOG does not exist

      If I take a look into the table "all_tables", everything is fine:

      Enter user-name: clix_db
      Enter password:
      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      SQL> select table_name from all_tables where table_name='LIC_REPORTLOG';

      TABLE_NAME
      ------------------------------
      LIC_REPORTLOG

      SQL> select * from CLIX_DB.LIC_REPORTLOG;

      no rows selected

      Any ideas?
        • 1. Re: unable to export empty tables
          Srini Chavali-Oracle
          Welcome to the forums !

          Pl post your OS and database versions. Has this ever worked before ? If so, what has changed since the last time it worked ?

          HTH
          Srini
          • 2. Re: unable to export empty tables
            Hemant K Chitale
            "ALL_TABLES" shows tables on which the account "CLIX_DB" has access --- thus the views also includes tables owned by other database accounts but on which acccess (eg SELECT or SELECT, INSERT, UPDATE, DELETE) has been granted to "CLIX_DB".

            "USER_TABLES" shows tables owned by "CLIX_DB" only.

            So, first query "USER_TABLES" to get the "CLIX_DB" tables. Query "ALL_TABLES" and get the OWNER of the other tables. You'd have to export those other tables from those respective owners.


            Hemant K Chitale
            • 3. Re: unable to export empty tables
              736935
              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
              linux redhat
              On a second server it works fine. Empty tables are exported, too.


              clix_db is the owner of table LIC_REPORTLOG

              I compared the table LIC_REPORTLOG with the table LIC_LICENSE which is exported correctly by exp:

              select * from ALL_TABLES WHERE TABLE_NAME='LIC_LICENSE' union
              select * from ALL_TABLES WHERE TABLE_NAME='LIC_REPORTLOG'

              OWNER     TABLE_NAME     TABLESPACE_NAME
              CLIX_DB     LIC_LICENSE     CLIX_DATA
              CLIX_DB     LIC_REPORTLOG     CLIX_DATA

              select * from USER_TABLES WHERE TABLE_NAME='LIC_LICENSE' union
              select * from USER_TABLES WHERE TABLE_NAME='LIC_REPORTLOG'

              TABLE_NAME     TABLESPACE_NAME
              LIC_LICENSE     CLIX_DATA
              LIC_REPORTLOG     CLIX_DATA
              • 4. Re: unable to export empty tables
                Lubiez Jean-Valentin
                Hello,


                I think that with such a new release you should use DataPump (expdp/impdb) to
                export Tables.

                For exporting a complete Schema you may use the following syntax:
                expdp {color:red}+user+{color}/{color:red}+password+{color} PARFILE=pfexport.txt
                _With pfexport.txt as bellow:_
                SCHEMAS={color:red}+schema_name+{color}
                FLASHBACK_TIME="TO_TIMESTAMP(to_char(SYSDATE,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')"
                CONTENT=ALL
                DIRECTORY=EXP_DIR
                DUMPFILE={color:red}+dump_file_name+{color}
                LOGFILE={color:red}+log_file_name+{color}
                Then, in this example, you'll get the "dump file" and the "log file" into the EXP_DIR Oracle directory (if it exists).

                You can check your Oracle Directories with the following query:
                select * from dba_directories;
                Then, you can use one of these Directories or create a new one with the following statement
                CREATE OR REPLACE DIRECTORY {color:red}+directory_name+{color} AS '{color:red}+directory_path+{color}';
                GRANT READ,WRITE ON DIRECTORY {color:red}+directory_name+{color} TO {color:red}+user_name+{color};
                Hope it can help,
                Best regards,
                Jean-Valentin

                Edited by: Lubiez Jean-Valentin on Nov 28, 2009 12:08 PM
                • 5. Re: unable to export empty tables
                  664593
                  The cause is a new feature of Oracle 11.2: deferred segment creation.
                  All newly created tables that has no rows in it (ever!), has no corresponding row in DBA_SEGMENTS, so somewhy won't get exported with (deprecated) exp utility. Use expdp instead.
                  • 6. Re: unable to export empty tables
                    916290
                    I'm leaning toward segment creation property as well. However, I can understand that it is not possible to use datapump for every one. Here is what you need to do before export
                    - Identify all tables that do not have rows, there may not be extents allocated.
                    - Alter table to allocate extents

                    Note: allocating extents increases the storage space.

                    Here is my script....
                    -- Below script gets me alter table command for all tables they do not have extents

                    select 'alter table '||table_name||' allocate extent;' from user_tables
                    where segment_created = 'NO'


                    Copy the out put and execute it so extent will be created.

                    In my case I had EXP-3 errors.
                    • 7. Re: unable to export empty tables
                      bhatt t
                      Thanks a lot Praveen,

                      It did the job.

                      One more thing sir, as you know expdp/impdp is not working here and show error VDE-00018 i.e. Incompatibilty Version.

                      Can I use EXPDP/IMPDP in future with any settings? If not then, should I continue with EXP/IMP in 11g?

                      I used IMP/EXP in 8i many times, with never any problem in backup/restoring my data.

                      Please guide.

                      Regards.
                      • 8. Re: unable to export empty tables
                        Srini Chavali-Oracle
                        Pl post a new thread for your questions instead of hijacking an old thread

                        Srini