Oracle creation and deletion of users, roles, tablespaces, import and export,... command Summary

Oracle creates and deletes users, roles, tablespaces, imports and exports ,... command summary // create temporary tablespace www.2cto.com create temporary tablespace zfmi_temptempfile 'd:/oracle/oradata/zfmi/zfmi_temp.dbf 'size 32 mautoextend onnext 32 m maxsize 2048 mextent management local; // The tempfile parameter must contain www.2cto.com // create tablespace zfmiloggingdatafile 'd:/oracle/oradata/zfmi. dbf 'size 100 mautoextend onnext 32 m maxsize 2048 mextent management local; // The datafile parameter must have w Ww.2cto.com // delete a user and all objects of the user. The drop user zfmi cascade parameter deletes all objects of the user in cascade mode, if you have an object but do not add this parameter, you may not be able to delete it. Therefore, you are expected to add this parameter before you delete the tablespace: before deleting a tablespace, make sure that the tablespace is not used by other users. Then delete the drop tablespace zfmi including contents and datafiles cascade onstraints. // including contents deletes the content in the tablespace, if there is content in the tablespace before the tablespace is deleted, but this parameter is not added, the tablespace cannot be deleted, therefore, we often add this parameter // including datafiles to delete the data files in the tablespace // cascade constraints and delete the Foreign keys of the tables in tablespace. If the tablespace files are deleted before the tablespace is deleted, the solution is as follows: if Before a tablespace is created, the data file corresponding to the tablespace is deleted, which causes the database to fail to start or close properly. You can use the following method to restore data (this method has been verified in oracle9i): In the following process, filename is a data file that has been deleted. If there are multiple, it needs to be executed multiple times; tablespace_name is the name of the corresponding tablespace. $ Sqlplus/nologSQL> conn/as sysdba; if the database has been started, run the following line: SQL> shutdown abortSQL> startup mountSQL> alter database datafile 'filename' offline drop; SQL> alter database open; SQL> drop tablespace tablespace_name including contents; // create a user and specify the tablespace create user zfmi identified by zfmidefault tablespace zfmi temporary tablespace zfmi_temp; // The identified by parameter must have all the permissions granted TO the message user DBA role grant dba to zfmi; // Grant the permission grant connect, resource to zfmi; (db2: Specify all permissions) Import and Export command: Oracle Data Import and Export imp/exp is equivalent to oracle data restoration and backup. The exp command can export data from the remote database server to the local dmp file, and the imp command can import the dmp file from the local to the distant database server. This function can be used to build two identical databases, one for testing and the other for formal use. Execution Environment: In SQLPLUS. execute in EXE or DOS (command line). When DOS can be executed, because the installation directory ora81BIN in oracle 8i is set to a global path, there is EXP in this directory. EXE and IMP. the EXE file is used for import and export. Oracle is written in java. SQLPLUS. EXE, EXP. EXE, and IMP. EXE files may be packaged class files. SQLPLUS. EXE calls the classes encapsulated by EXP. EXE and IMP. EXE to complete the Import and Export function. The following describes the Import and Export instances. Data export: 1 completely export the database TEST, and the user name system password manager is exported to D: daochu. exp system/manager @ TEST file = d: daochu in dmp. dmp full = y2 export the tables of system users and sys users in the database exp system/manager @ TEST file = d: daochu. dmp owner = (system, sys) 3 export the database tables inner_policy and policy_staff_relat to exp aichannel/aichannel @ TESTDB2 file = d: datanewsmgnt. dmp tables = (inner_policy, policy_staff_relat) 4. Export the filed1 field in table 1 in the database with the data Header "00" exp system/manager @ TEST file = d: d Aochu. dmp tables = (table1) query = "where filed1 like '000000'" is commonly used for export. For compression, you can use winzip to compress dmp files. You can also add compress = y to the command above. Import data 1 import the data in D: daochu. dmp to the TEST database. Imp system/manager @ TEST file = d: daochu. dmp imp aichannel/aichannel @ HUST full = y file = d: datanewsmgnt. dmp ignore = y may be faulty because some tables already exist and an error is reported. Add ignore = y to the end. 2. Importing table 1 in d: daochu. dmp to imp system/manager @ TEST file = d: daochu. dmp tables = (table1) is sufficient. In many cases, you must first completely delete the table and then import it. Note: If the operator has sufficient permissions, a prompt is displayed. Databases can be connected. You can use tnsping TEST to obtain whether the database TEST can be connected. Appendix 1: add data import permissions to users first, start SQL * puls second, log on to system/manager third, create user username IDENTIFIED BY password (if you have already created a user, this step can be omitted) 4. grant create user, drop user, alter user, create any view, drop any view, EXP_FULL_DATABASE, IMP_FULL_DATABASE, DBA, CONNECT, RESOURCE, create session to username 5th, run-cmd-Enter the directory where the dmp file is located, imp userid = system/manager full = y file = *. dmp or imp userid = system/manager full = y file = filename. dmp execution example: F: WorkOracle_D Atabackup> imp userid = test/test full = y file = inner_policy.dmp screen display Import: Release 8.1.7.0.0-Production on Thursday February 16 16:50:05 2006 (c) Copyright 2000 Oracle Corporation. all rights reserved. connect to: Oracle8i Enterprise Edition Release 8.1.7.0.0-ProductionWith the Partitioning optionJServer Release 8.1.7.0.0-Production EXPORT by EXPORT: the file created in V08.01.07 has completed the import and export of the ZHS16GBK Character Set and the ZHS16GBK NCHAR character set on the server using UTF8 NCHA R character set (possible ncharset conversion ). importing AICHANNEL object to AICHANNEL .. importing table "inner_policy" 4 rows are being imported to prepare to enable constraints... import is terminated successfully, but a warning is displayed. Appendix 2: Oracle does not allow direct change of the table owner. Export/Import can be used for this purpose. create import9.par first. Then, run the following command to use import9.par: imp parfile =/filepath/import9.par. The content of import9.par is as follows: FROMUSER = tgpms touser = TGPMS2 (note: change the table owner from FROMUSER to TOUSER. Users of FROMUSER and TOUSER can be different) ROWS = y indexes = y grants = y constraints = y buffer = 409600 file =/backup/ctgpc_20030623.dmp log =/backup/import_20030623.log