Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to import or export a single table from Oracle database under Linux. In daily operation, I believe many people have doubts about how to import or export a single table from Oracle database under Linux. I have consulted all kinds of materials and sorted out simple and easy operation methods. I hope to answer your doubts about how to import or export a single table from Oracle database under Linux. Next, please follow the small series to learn together!

The whole story is like this: the user's library, to carry out label replacement in a table, when writing sql, the carelessness, did not pay attention to character escape, the result of the execution of sql, an accident, will be a content table information, all replaced with empty ~ fortunately there is a backup;

The plan is like this: backup the current database first, then restore the backup of the previous night, export the data in the table, restore the current backup, replace the current problem table with the backup of last night's single table, so as to ensure the relative integrity of the data, because when the accident occurred, it was already after 5:00 after work. In one day, the user had improved a lot of information, but the data in the table could only be yesterday's, and it is estimated that several pieces of data will be lost. There is no way ~ start the whole;

First, perform the latest database backup, using exp, the database will be a backup;

The second step, get the backup file dmp file last night,(my project has regular automatic backup, this is very necessary, every night at 2:30 automatic backup library, keep the latest 5 days backup) this time, my approach is to stop the current library directly, rebuild the library, because you use imp recovery, your current library is a table, execution imp will fail;

Practice: stop listening, stop the library, then put oracle mv as oracle_date, paste the code,

su - oracle //login oraclelsnrctl stop //stop listening sqlplus "/as sysdba" //enter database shutdown immediate //stop database exit //exit oraclecd /oracle //enter mv oracle oracle_20170119 //rename old oracletar-zxvf oracle11g.tar.gz //extract package

After decompression, log in again and modify the configuration file

su - oracle //login oraclevi .bash_profile //modify configuration file # .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then . ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/binexport PATHunset USERNAMEexport ORACLE_BASE=/opt/oracleexport ORACLE_HOME=/opt/oracle/product/11.1.0/db_1export ORACLE_SID= orlexport ORACLE_TERM=xtermexport NLS_LANG=AMERICANexport ORACLE_NLS33 =$PATH_HOME/ocommon/nls/admin/dataLD_LIBRARY_start =$ORACLE_export/lib:/lib:/usr/lib: /usr/libexport PATH=$PATH_HOME/bin:$PATH after saving source .bash_profilelsnrctl //start listening sqlplus "/as sysdba" //enter database startup //Start the database, and then execute the following four commands to create instances, one by one execute create tablespace oracle datafile '/oracle/oradata/orcl/oracle.dbf' size 2048M;create user oracle identified by oracle default tablespace oracle;grant connect,resource to oracle;grant dba to oracle;

The above four lines of command, mainly to create a new table space, and then create a user, my account password are oracle, the database is oracle

When the execution is completed, exit to oracle user, because all the previous executions are sql, which are performed under sql>;

cp Backup the previous day to the current directory, and then import;

imp oracle/oracle file=oracle_20170118.dmp fromuser=oracle touser=oracle rows=y

After importing, you can start the web service to see if the library reading is normal. Normal means that the operation just now is successful. Next, the most important step is to export a single table. I want to export the cms_gk table.

exp oracle/oracle file=/oracle/backup/oracle_20170118_cms_gk.dmp tables=CMS_GK

Here, using the tables attribute, you can directly export a single table;

After that, get the cms_gk table and restore the database to the afternoon when the problem occurred, because except for this table, the data in other tables are all the latest; Continue to stop listening, stop the database, change the previous oracle mv to oracle_20170118, change the previous oracle_date folder mv to oracle, because the previous database, nothing has been moved, and the tables in it are complete, so after the oracle is restored, you can directly start the database. After getting up, log in to the database and perform import in sql>;

imp oracle/oracle file=oracle_20170118_cms_gk.dmp fromuser=oracle touser=oracle tables=CMS_GK

After import, you can use Navicat link database to view, or start we'b service, see if there is information, I have successfully restored the information at this time; finally the problem is solved, lost part of the data in a table, is the minimum loss!

At this point, the study of "how to import or export a single table from Oracle database under Linux" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.