Oracle Database Simple operation

Import all tables and data for a user:
Imp sgp/[email PROTECTED]:1521/ORCL file=sgp20161025.dmp full=y

To export the specified tables and data:
Exp Sgp/[email PROTECTED]:1521/ORCL file=20160921sgp_table_sysmodule_sysdict.dmp tables= (Sgp_sysmodule, sgp_sysdict )

Import the specified tables and data:
Imp sgp/[email PROTECTED]:1521/ORCL file=20160921sgp_table_sysmodule_sysdict.dmp tables= (Sgp_sysmodule, sgp_sysdict )

Linux Environment (Oracle database and client installed):

#登陆linux服务器, switch to Oracle user
Su-oracle

#不在cmd或者terminal当中暴露密码的登陆方式
Sqlplus/nolog

#以操作系统权限认证的oracle SYS Administrator Login
Sql>connect Sys/[email protected] as Sysdba

#创建表空间 (ASM mode)
Sql>
Create Tablespace geodata (table space name) datafile ' +data ' size 500m autoextend on next 64m MaxSize Unlimited;
Tablespace created.

#创建用户, and specify Tablespace
sql> Create user geouser (user name) identified by geouseradmin (password) default tablespace geodata (table space name);
User created.

#给用户授予权限
Sql> Grant CONNECT,RESOURCE,DBA (Oracle maximum permissions) to Geouser (username);
Grant succeeded.

ALTER USER username account UNLOCK;

Get column information for a table
SELECT * FROM user_tab_columns WHERE table_name = ' Cms_t_view_record '

#查看当前用户的缺省表空间
Sql>select username,default_tablespace from User_users;

#查看已建好的表空间
Sql> select File_name,tablespace_name from Dba_data_files;

#查看当前用户的表空间和有哪些表
Sql> select Table_name,tablespace_name from User_tables;

#查看某个表空间下有哪些表
Sql> SELECT * from All_tables where tablespace_name= ' SDE ';

#表空间的数据文件属性改为自动扩展
sql> alter TABLESPACE system autoextend on;

#查看当前连接的数据库IP地址
Sql> select utl_inaddr.get_host_address from dual;

#查看当前数据库
Sql> select name from V$database;

#查看数据库实例名称
Select instance_name from V$instance;

#查看表结构
Sql> describe oper_data (table name);

/*** viewing data in a table ***/
Sql> select * from Oper_data;

/*** View the currently logged on user name ***/
Sql> Select User from dual;

/*** View the roles and permissions that the current user has ***/
Sql> select * from User_role_privs;
Sql> select * from Session_privs;

/*** View all the default tablespace ***/
Select Tablespace_name from Dba_tablespaces;

/*** View the default tablespace for a specified user ***/
Select Default_tablespace.username from Dba_users;

/*** renaming the tablespace name ***/
Alter tablespace oldname rename to NewName;

/*** Modify read-write Status of table space ***/
Alter Tablespace tablespace_name read {only|write};

/*** set the available state of the tablespace ***/
Alter Tablespace tablespace_name {online|offline[normal|temporary|immediate];

/*** creating large file Tablespace ***/
CREATE bigfile tablespace tablespacename datafile filename size size;

/*** to delete a table space ***/
Drop tablespace tablespace_name [include contents] [Cascade Constrain
TS];

/***************************************

Su-oracle
Sqlplus/nolog
Sql> Connect Geouser/geouseradmin

Profile is a profiling file in Oracle that is primarily stored in the system resources in the database or
Some content of database usage restrictions.

1. View all user names:
Select username from dba_users;
SELECT * from All_users;
SELECT * from User_users;

2. View user or role system permissions (System permissions that are directly assigned to a user or role):
SELECT * from Dba_sys_privs;
SELECT * from User_sys_privs;

3. View the permissions that are included in the role (can only view the roles owned by the logged-on user)
Sql>select * from Role_sys_privs;

4. View User Object permissions:
SELECT * from Dba_tab_privs;
SELECT * from All_tab_privs;
SELECT * from User_tab_privs; Returns all object permissions for the current user

5. View all roles:
SELECT * from Dba_roles;

6. View the roles owned by the user or role:
SELECT * from Dba_role_privs;
SELECT * from User_role_privs;

7. See which users have SYSDBA or Sysoper system permissions (requires appropriate permissions when querying)
SELECT * FROM V$pwfile_users

8. Assign all permissions of a table Mytab to the user test:
Grant all on Mytab to test;
Revoke all permissions
Revoke all on mytab to test;

9. Object permissions can be controlled to columns (queries and deletions cannot be controlled to columns)
Grant update (name) on Mytab to test;
Grant Insert (ID) on mytab to test;

10. Delete the audit table
TRUNCATE TABLE SYS. aud$;

Windows environment DOS:

Add permission to import and export data to user sys:
Grant Exp_full_database, imp_full_database to SDE;

Export operation
1, the database user name sys password * * * User space all tables and data export
Exp Username/userpassword file= file path and filename owner=username

2. Export the table of the SDE user in the database
EXP \ "Sys/[email protected] as sysdba\" file=f:\ backup \export.dump owner=sde

3. Set the table in the database to SYS. aud$ Export
EXP \ "Sys/[email protected] as sysdba\" file=f:\ backup \export.dump tables= (table1,table2)

4. Export the Audit table
Exp Nmgqyrk/[email protected] file=exp.dmp Tables=sys. aud$

Import Operation:
1. Import data from f:\ backup \export.dump into NMGDB2
IMP \ "Sys/[email protected] as sysdba\" File=f:\ backup \export.dump

2. Import the table TableA in f:\ backup \export.dump (delete the original table first)
IMP \ "Sys/[email protected] as sysdba\" file=f:\ backup \export.dump tables= (table1,table2)

Window environment (The Oracle database and client are already installed):

1: Run Sqlplus first

C:\Documents and Settings\administrator>sqlplus/nolog (can run Sqlplus directly after running cmd)

Sql*plus:release 10.1.0.2.0-production on Monday September 3 15:15:27 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

2: Connect the database with SYSDBA permissions

Sql> Connect/as sysdba;

3: Create TABLE Space

sql> create tablespace cms datafile ' G:\Oracle\Administrator\oradata\orcl\sgp.dbf ' size 256m uniform size 128k;

Table space named CMS, database file directory D:\oracle\10.1.0\oradata\orcl\cms.dbf

4: Change table space to auto-expand storage size

sql> ALTER DATABASE datafile ' G:\ORACLE\ADMINISTRATOR\ORADATA\ORCL\SGP.DBF ' autoextend on;

5: Create User

sql> create user CMS identified by CMS default Tablespace cms;

Note: User name CMS password cms default table space CMS;

6: Authorization for users

sql> Grant Connect, RESOURCE,DBA to CMS;

Delegate connection management to the CMS with DBA authority;

1. View the user and user's corresponding space
Select Username,default_tablespace from Dba_users;

2. View the table space and data file corresponding relationship
Select T1.name,t2.name from V$tablespace T1, V$datafile T2 where t1.ts#=t2.ts#;

The encoding type of the database export file is GBK and may be inconsistent with the imported database encoding type, resulting in garbled characters.

Garbled solution is as follows:
Modify the database character set to: ZHS16GBK
Viewing the server-side character set
SQL > select * from V$nls_parameters; Nls_characterset ZHS16GBK
Modify:
$sqlplus/nolog
Sql>conn/as SYSDBA
If the database server is now started, execute the SHUTDOWN IMMEDIATE command to shut down the database server, and then execute the following command:
Database shutdown: Sql>shutdown immediate;
Sql>startup MOUNT;
Sql>alter SYSTEM ENABLE RESTRICTED SESSION;
Sql>alter SYSTEM SET job_queue_processes=0;
Sql>alter SYSTEM SET aq_tm_processes=0;
Sql>alter DATABASE OPEN;
Sql>alter DATABASE Character Set Internal_use ZHS16GBK;
Database Startup:sql> Startup

11g Oracle Export table will default to no export data is empty
1, oracle11g default to Empty table is not assigned segment, so use EXP export oracle11g database, empty table is not exported.
2. After setting the Deferred_segment_creation parameter to False, both the empty table and the non-empty table are assigned segment.
Sql>show parameter deferred_segment_creation;
Sql>alter system set Deferred_segment_creation=false;

/**************************************************
Get current time
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual

To export tables and data (including empty tables) method:
Sql> Connect SGP/SGP
Sql> set echo off
Sql> Set Feedback off
Sql> Set pagesize 0
Sql> Set Linesize 9000
Sql> set Tab off
Sql> Spool Sgp_deal_tablenull_107.sql
Sql> select ' ALTER TABLE ' | | table_name| | ' allocate extent; ' from User_tables where num_rows=0;
Sql> Spool Off

After execution, you will get a file: Sgp_deal_tablenull_107.sql,
Delete the non-SQL statements in this file, leaving only those SQL of ALTER TABLE,
Then execute these SQL
Export after execution: exp SGP/SGP file= file name OWNER=SGP

Import data (without creating table and tablespace restrictions, if the table name is the same)
Imp scm/[email PROTECTED]:1521/ORCL file=sgp99dept.dmp tables=scm_dept ignore=y

---Export the following SQL statement query results to recover and delete annotations at any time
--Query (export) Table comments
Select ' Comment on table ' | | t.table_name| | ' is ' ' | |t.comments| | ', ' from user_tab_comments t;

--Query (export) column comments
Select ' Comment on column ' | | c.table_name| | '. ' | | c.column_name| | ' is ' ' | |c.comments| | ', ' from user_col_comments C;
----------------------------------------------------------------------------------

--Delete Table annotations
1. Select ' Comment on table ' | | t.table_name| | ' is ' ', ' from user_tab_comments t; Get Table Comment Results
2. Export to CVS file, open with Execl, copy to Plsql and delete

--Delete Column comments
1. Select ' Comment on column ' | | c.table_name| | '. ' | | c.column_name| | ' is ' ', ' from user_col_comments C; Get Table Comment Results
2. Export to CVS file, open with Execl, copy to Plsql and delete

Oracle Database Simple operation