oracle mysql操作_oracle数据库简单操作

导入某用户所有表和数据：

imp sgp/sgp@192.168.0.99:1521/orcl file=sgp20161025.dmp full=y

导出指定表及数据：

exp sgp/sgp@192.168.0.107:1521/orcl file=20160921sgp_table_sysmodule_sysdict.dmp tables=(sgp_sysmodule, sgp_sysdict)

导入指定表及数据：

imp sgp/sgp@192.168.0.99:1521/orcl file=20160921sgp_table_sysmodule_sysdict.dmp tables=(sgp_sysmodule, sgp_sysdict)

Linux环境下(已安装好oracle数据库和客户端)：

#登陆linux服务器，切换到oracle用户下

su - oracle

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

sqlplus /nolog

#以操作系统权限认证的oracle sys管理员登陆

SQL>connect sys/sys123456@NMGDB2 as sysdba

#创建表空间(ASM模式)

SQL>

create tablespace geodata(表空间名) datafile '+data' size 500m AUTOEXTEND ON next 64m maxsize unlimited;

Tablespace created.

#创建用户，并指定表空间

SQL> create user geouser(用户名) identified by geouseradmin(密码) default tablespace geodata(表空间名);

User created.

#给用户授予权限

SQL> grant connect,resource,dba(Oracle最大权限) to geouser(用户名);

Grant succeeded.

ALTER USER username ACCOUNT UNLOCK;

获取表的列信息

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(表名);

/*** 查看表中数据 ***/

SQL> select * from oper_data;

/*** 查看当前登录的用户名 ***/

SQL> select user from dual;

/*** 查看当前用户拥有的角色和权限 ***/

SQL> select * from user_role_privs;

SQL> select * from session_privs;

/*** 查看所有的默认表空间 ***/

select tablespace_name from dba_tablespaces;

/*** 查看指定用户的默认表空间 ***/

select default_tablespace.username from dba_users;

/*** 重命名表空间名称 ***/

alter tablespace oldname rename to newname;

/*** 修改表空间的读写状态 ***/

alter tablespace tablespace_name read {only|write};

/*** 设置表空间的可用状态 ***/

alter tablespace tablespace_name { online|offline[normal|temporary|immediate];

/*** 创建大文件表空间 ***/

CREATE BIGFILE TABLESPACE tablespacename DATAFILE filename SIZE size;

/*** 删除表空间 ***/

drop tablespace tablespace_name [include contents] [cascade constrain

ts];

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

su - oracle

sqlplus /nolog

SQL> connect geouser/geouseradmin

PROFILE是Oracle中的概要文件，在PROFILE中主要存放的就是数据库中的系统资源或者

数据库使用限制的一些内容。

1.查看所有用户名：

select username from dba_users;

select * from all_users;

select * from user_users;

2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：

select * from dba_sys_privs;

select * from user_sys_privs;

3.查看角色(只能查看登陆用户拥有的角色)所包含的权限

sql>select * from role_sys_privs;

4.查看用户对象权限：

select * from dba_tab_privs;

select * from all_tab_privs;

select * from user_tab_privs; //返回当前用户所有的对象权限

5.查看所有角色：

select * from dba_roles;

6.查看用户或角色所拥有的角色：

select * from dba_role_privs;

select * from user_role_privs;

7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

select * from V$PWFILE_USERS

8、把某个表mytab的所有权限都赋予给用户test：

grant all on mytab to test；

撤销所有权限

revoke all on mytab to test；

9、对象权限可以控制到列(查询和删除不能控制到列)

grant update(name) on mytab to test;

grant insert(id) on mytab to test;

10、删除审计表

TRUNCATE TABLE SYS.AUD$;

window环境DOS下：

给用户sys增加导入导出数据的权限：

grant EXP_FULL_DATABASE, IMP_FULL_DATABASE TO SDE;

导出操作

1、将数据库用户名sys 密码 *** 用户空间所有表和数据导出

exp username/userpassword file=份文件路径及文件名 owner=username

2、将数据库中sde用户的表导出

exp \"sys/sys123456@NMGDB2 as sysdba\" file=f:\备份\export.dump owner=sde

3、将数据库中的表 SYS.AUD$ 导出

exp \"sys/sys123456@NMGDB2 as sysdba\" file=f:\备份\export.dump tables=(table1,table2)

4、导出审计表

exp nmgqyrk/nmgqyrk@NMGDB2 file=exp.dmp tables=SYS.AUD$

导入操作：

1、将f:\备份\export.dump中的数据导入到NMGDB2中

imp \"sys/sys123456@NMGDB2 as sysdba\" file=f:\备份\export.dump

2、将f:\备份\export.dump中的表tableA导入(先删除原表)

imp \"sys/sys123456@NMGDB2 as sysdba\" file=f:\备份\export.dump tables=(table1,table2)

window环境下(已经安装好oracle数据库和客户端)：

1：首先运行SQLPLUS

C:\Documents and Settings\Administrator>sqlplus /nolog(运行cmd后可直接运行SQLPLUS)

SQL*Plus: Release 10.1.0.2.0 - Production on 星期一 9月 3 15:15:27 2007

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

2：以sysdba的权限连接数据库

SQL> connect /as sysdba;

3：创建表空间

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

表空间名为cms，数据库文件目录D:\oracle\10.1.0\oradata\orcl\cms.dbf

4：把表空间改为自动扩展存储空间大小

SQL> alter database datafile 'G:\Oracle\Administrator\oradata\orcl\sgp.dbf' autoextend on;

5：创建用户

SQL> create user cms identified by cms default tablespace cms;

注：用户名cms 密码cms 默认表空间cms;

6：为用户授权

SQL> grant connect ,resource,dba to cms;

把连接管理以dba的权限授权给cms;

1.查看用户与用户对应的标空间

select username,default_tablespace from dba_users;

2.查看表空间与数据文件的对应关系

select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts#=t2.ts#;

该数据库导出文件的编码类型为GBK，可能会与导入的数据库编码类型不一致，从而产生乱码。

乱码解决办法如下：

修改数据库字符集为：ZHS16GBK

查看服务器端字符集

SQL > select * from V$NLS_PARAMETERS; NLS_CHARACTERSET ZHS16GBK

修改：

$sqlplus /nolog

SQL>conn /as sysdba

若此时数据库服务器已启动，则先执行 SHUTDOWN IMMEDIATE 命令关闭数据库服务器，然后执行以下命令:

数据库关闭： 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;

数据库启动：SQL> startup

11g oracle导出表时会默认不导出数据为空

1、Oracle11g默认对空表不分配segment，故使用exp导出Oracle11g数据库时，空表不会导出。

2、设置deferred_segment_creation 参数为FALSE后，无论是空表还是非空表，都分配segment。

SQL>show parameter deferred_segment_creation;

SQL>alter system set deferred_segment_creation=false;

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

获取当前时间

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual

导出表和数据(包括空表)方法：

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

执行完以后，你会得到一个文件：sgp_deal_tablenull_107.sql，

把这个文件里面的非sql语句删掉，只保留alter table的那些sql，

然后执行一下这些sql

执行完后再导出：exp sgp/sgp file=文件名 owner=sgp

导入数据(不附带创建表和表空间限制,前提条件是表名称一致)

imp scm/scm@192.168.0.99:1521/orcl file=sgp99dept.dmp tables=scm_dept ignore=y

--将以下sql语句查询结果导出，即可随时进行注释的恢复与删除

--查询(导出)表注释

select 'comment on table '||t.table_name||' is '''||t.comments||''';' from user_tab_comments t;

--查询(导出)列注释

select 'comment on column '||c.table_name||'.'||c.column_name||' is '''||c.comments||''';' from user_col_comments c;

----------------------------------------------------------------------------------

--删除表注释

1、select 'comment on table '||t.table_name||' is '''';' from user_tab_comments t; 获取表注释结果

2、导出到cvs文件中，使用execl打开，拷贝到plsql中执行即可删除

--删除列注释

1、select 'comment on column '||c.table_name||'.'||c.column_name||' is '''';' from user_col_comments c; 获取表注释结果

2、导出到cvs文件中，使用execl打开，拷贝到plsql中执行即可删除