oracle常用操作

1、创建表空间

create tablespace HSEDUSP datafile 'D:\app\dawn\oradata\JSYXORA\HSEDUSP.ORA' size 1024M autoextend on next 500M;
create temporary tablespace HSEDUTMPSP tempfile 'D:\app\dawn\oradata\JSYXORA\HSEDUTMPSP.ORA' size 500M autoextend on next 100M;

　

 

2、oracle11g有个bug，用户密码在180天后无法登录，因此需要设置用户密码永不过期

由于Oracle11G的新特性所致，经常会遇到使用sqlplus登陆oracle数据库时提示“ORA-28002: 7 天之后口令将过期”等情况。 在Oracle 11G 创建用户时缺省密码过期限制是180天， 如果超过180天用户密码未做修改则该用户无法登录，
提示“ORA-28001: the password has expired”

解决方式如下（如果是在用户创建之前可以使用下面方法，如果用户已经创建了需要再次更改每个用户密码为原来密码规避）

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as dawnweb@DAWNORA
查看密码过期时间
SQL> SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
 
PROFILE                        RESOURCE_NAME                    RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD      180
 
更改用户密码不过期
SQL> Alter PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
 
Profile altered

再次查看密码过期时间
SQL> SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
 
PROFILE                        RESOURCE_NAME                    RESOURCE_TYPE LIMIT
------------------------------ -------------------------------- ------------- ----------------------------------------
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD      UNLIMITED
 
SQL> 

　　

3、创建用户

create user dawnedu identified by dawnedudawn default tablespace HSEDUSP temporary tablespace HSEDUTMPSP quota unlimited on HSEDUSP;
grant dba, connect, resource to dawnedu;
create user dawngame identified by dawngamedawn default tablespace HSEDUSP temporary tablespace HSEDUTMPSP quota unlimited on HSEDUSP;
grant dba, connect, resource to dawngame;
create user dawnweb identified by dawnwebdawn default tablespace HSEDUSP temporary tablespace HSEDUTMPSP quota unlimited on HSEDUSP;
grant dba, connect, resource to dawnweb;
create user dawnsys identified by dawnsysdawn default tablespace HSEDUSP temporary tablespace HSEDUTMPSP quota unlimited on HSEDUSP;
grant dba, connect, resource to dawnsys;
create user dawnserv identified by dawnservdawn default tablespace HSEDUSP temporary tablespace HSEDUTMPSP quota unlimited on HSEDUSP;
grant dba, connect, resource to dawnserv;
create user qtgame identified by qtgame default tablespace HSEDUSP temporary tablespace HSEDUTMPSP quota unlimited on HSEDUSP;
grant dba, connect, resource to qtgame;

　　

4、导入数据

imp dawnweb/dawnwebdawn@dawnora  file=D:\soft\0601\web.dmp full=y
imp dawnedu/dawnedudawn@dawnora  file=D:\soft\0601\edu.dmp full=y
imp dawnsys/dawnsysdawn@dawnora  file=D:\soft\0601\sys.dmp full=y
imp dawngame/dawngamedawn@dawnora  file=D:\soft\0601\game.dmp full=y
imp dawnserv/dawnservdawn@dawnora  file=D:\soft\0601\serv.dmp full=y