1.创建表空间
create tablespace NW_DATA logging datafile 'F:\oracle\product\10.2.0\oradata\nwdb\NW_DATA.dbf' size 64m autoextend on next 64m maxsize 20480m extent management local;
2. 创建用户并指定表空间
create user test identified by 123 default tablespace NW_DATA;
grant dba to test;
如果选择使用默认的表空间,直接创建用户: create user a identified by a ;
3.删除表空间
已经导入数据的:
drop tablespace NW_DATA including contents and datafiles;
没有数据的直接删除:
drop tablespace NW_DATA
4.删除用户
如果用户指定了表空间,并且表空间有数据,只能删除表空间只有,才可以删除用户
drop user test cascade;
5.ora-01940 无法删除当前连接的用户,plsql
select username,sid,serial# from v$session;
alter system kill session '67,94';
alter system kill session '196,58';
alter system kill session '197,108';
alter system kill session '14,258';
alter system kill session '133,282';
alter system kill session '12,5161';
drop user t_test cascade;
6.导入dmp文件,cmd
重复导入会报错,表已经存在,方便起见,删除用户已经相关的表,重新导入
导入的方法:
imp t_test/t_test@orcl fromuser=sxg touser=t_test file=f:\z.dmp ignore=y ;
imp t_test/t_test@orcl file=f:\z.dmp full=y;
第二种方式不管从哪个用户导出的,导入到哪个用户,都可以用;
7.更改用户密码
一、密码修改
修改密码方法:
1.SQL> grant connect to USRNAME identified by USRPWD ;
2.SQL> alter user USRNAME identified by USRPWD ;
3.SQL> password USRNAME ;
更改 USRNMAE 的口令
旧口令:××××××
新口令:××××××
重新键入新口令:××××××
====================================================
oracle 忘记sys 密码后,更改方法
D:\oracle\ora92\bin>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 8月 16 11:32:22 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba
已连接。
SQL> alter user system identified by password;
用户已更改。
SQL> alter user sys identified by password;
用户已更改。
SQL> alter user system identified by manger;
用户已更改。
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开
D:\oracle\ora92\bin>sqlplus
SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 8月 16 11:40:37 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
请输入用户名: system
请输入口令:
连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl
manger
[Oracle初始口令]
internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp
to_date('09-07-2015 11:18:09', 'dd-mm-yyyy hh24:mi:ss')