操作oracle

来源：互联网发布：今创集团这个知乎编辑：程序博客网时间：2024/05/16 17:00

sys /nolog

conn sys/hkmz as sysdba;

//创建表临时空间

create temporary tablespace MCA 4

tempfile 'C:\oracle\mac.dbf'

size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

//创建表空间

create tablespace mca_data

logging

datafile 'C:\oracle\mac_data.dbf'

size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

//创建用户mca，密码mac

create user mca identified by mca

default tablespace MCA

temporary tablespace MCA;

//授权

grant connect,resource to mca;

//删除用户

drop user mac cascade;

imp yqc/yqc@YANGQICONG file=c:\mca2010.07.15.dmp full=y

创建表空间

create tablespace mca datafile 'C:\oracle\mcadb.dbf' size 200m autoextend on next 50m maxsize unlimited;

alter database datafile 'C:\oracle\mcadb.dbf' autoextend on;

//删除表空间

DROP TABLESPACE histdb INCLUDING CONTENTS AND DATAFILES;

//创建用户

create user lqz identified by lqz default tablespace mca temporary tablespace temp;

select username,default_tablespace,temporary_tablespace

from dba_users

where username='mca';

grant connect,resource to lqz;

grant SYSDBA to mca;

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

Microsoft Windows XP [版本 5.1.2600]

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 1月 10 10:55:36 2011

请输入用户名: sys

输入口令:

ERROR:

ORA-01005: 给出空口令; 登录被拒绝

请输入用户名: sample

输入口令:

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> create user yqc identified by yqc default tablespace sample;

create user yqc identified by yqc default tablespace sample

第 1 行出现错误:

ORA-00959: 表空间 'SAMPLE' 不存在

SQL> create user yqc identified by yqc default tablespace mca;

用户已创建。

SQL> exit

从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options 断开

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 1月 10 10:59:14 2011

请输入用户名: yqc

输入口令:

ERROR:

ORA-01045: user YQC lacks CREATE SESSION privilege; logon denied

请输入用户名: sample

输入口令:

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> grant dba to yqc;

授权成功。

SQL> exit

从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options 断开

C:\Documents and Settings\Administrator>yqc

'yqc' 不是内部或外部命令，也不是可运行的程序

或批处理文件。

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 1月 10 10:59:57 2011

请输入用户名: yqc

输入口令:

连接到:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> exit

从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options 断开

C:\Documents and Settings\Administrator>imp yqc/yqc@YANGQICONG file=C:\sample.dm

p full=y;

Import: Release 10.2.0.1.0 - Production on 星期一 1月 10 11:03:57 2011

IMP-00058: 遇到 ORACLE 错误 12154

ORA-12154: TNS: 无法解析指定的连接标识符

IMP-00000: 未成功终止导入

C:\Documents and Settings\Administrator>imp yqc/yqc@orcl file=C:\sample.dmp full

=y;

Import: Release 10.2.0.1.0 - Production on 星期一 1月 10 11:04:18 2011

IMP-00058: 遇到 ORACLE 错误 12154

ORA-12154: TNS: 无法解析指定的连接标识符

IMP-00000: 未成功终止导入

C:\Documents and Settings\Administrator>imp yqc/yqc@127 file=C:\sample.dmp full=

Import: Release 10.2.0.1.0 - Production on 星期一 1月 10 11:04:38 2011

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

警告: 这些对象由 SAMPLE 导出, 而不是当前用户

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

. 正在将 SAMPLE 的对象导入到 YQC

. . 正在导入表 ....

....

即将启用约束条件...

成功终止导入, 没有出现警告。

C:\Documents and Settings\Administrator>

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

create tablespace mca6

logging

datafile 'C:\oracle\product\10.2.0\oradata\yangqico\mac6_data.dbf'

size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

create user sample identified by a11 default tablespace mca;

grant dba to sample;

imp sample/a11@YANGQICO file=d:\exp.dmp full=y;

5.exp sample/11 owner=sample rows=y indexes=n compress=n buffer=65536 feedback=100000 file=c:\exp.dmp log=exp_icdmain_yyyymmdd.log

备份服务器数据库到本地

exp system/密码@服务名 full=y file=备份放的路径 log=日志放的路径

exp mca1/11@10.0.0.110 owner=mca1 rows=y indexes=n compress=n buffer=65536 feedback=100000 file=c:\mca1.dmp log=exp_icdmain_yyyymmdd.log

1.建立表空间

create tablespace mca6

logging

datafile 'E:\oracle\product\10.2.0\oradata\yangqico\mac6_data.dbf'

size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

2.创建用户，分配表空间

create user mca6 identified by a11 default tablespace mca6;

3.授权

grant dba to mca6;

imp mca6/a11@YANGQICO file=c:\mca6.dmp full=y;

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

请输入用户名: sys/tiger as sysdba

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create tablespace yqc_data
  2 logging
  3 datafile 'D:\oracle\product\10.2.0\oradata\orcl\yqc_data.dbf'
  4 size 32m
  5 autoextend on
  6 next 32m maxsize 2048m
  7 extent management local;

表空间已创建。

SQL> create user jpademo identified by ps11 default tablespace yqc_data;

用户已创建。

SQL> grant dba to jpademo;

授权成功。

SQL> -- 创建表空间
SQL> create tablespace STOCKS_DATA
  2 logging
  3 datafile 'D:\oracle\product\10.2.0\oradata\orcl\STOCKS_DATA.dbf'
  4 size 32m
  5 autoextend on
  6 next 32m maxsize 2048m
  7 extent management local;

表空间已创建。

SQL>
SQL> -- 创建用户，分配表空间
SQL>
SQL> create user STK_ADM identified by ps11 default tablespace STOCKS_DATA;

用户已创建。

SQL>
SQL> -- 授权
SQL>
SQL> grant dba to STK_ADM;

授权成功。

SQL>