Oracle常用命令

重启Oracle

sqlplus /nolog
conn sys / as sysdba
shutdow immediate;
startup;

 

修改Oracle字符集

Administrator>sqlplus /nolog 
 SQL>conn / as sysdba
 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 UTF8;

 

 

--密码修改
sqlplus /nolog
connect /as sysdba
alter user username identified by password;

--解锁
alter user username account unlock ;

--加锁
alter user username account lock;

--删除用户
--1.删除用户和用户所有的模式对象
--2.删除用户
drop user username cascade;
drop user username;

-- 查看所有用户
select * from all_users;

oracle创建表空间
/*分为四步 */

/*第1步：创建临时表空间  */

create temporary tablespace chenyanshan_temp 

tempfile 'D:\JAVA\oracle_11g\win64_11gR2_database\app\Administrator\oradata\oracle11g\chenyanshan\chenyanshan_temp.dbf'

size 50m 

autoextend on 

next 50m maxsize 20480m 

extent management local; 

 

/*第2步：创建数据表空间  */

create tablespace chenyanshan_data 

logging 

datafile 'D:\JAVA\oracle_11g\win64_11gR2_database\app\Administrator\oradata\oracle11g\chenyanshan\chenyanshan_data.dbf'

size 100m 

autoextend on 

next 100m maxsize 40960m 

extent management local; 

 

/*第3步：创建用户并指定表空间  */

create user username identified by password 

default tablespace chenyanshan_data 

temporary tablespace chenyanshan_temp; 

--查看表空间的文件名字
select name from v$datafile;

/*第4步：给用户授予权限  */

grant connect,resource,dba to username;

  --授权用户登陆数据库权限(system名下)
    grant connect to username;
  --授权用户创建表权限(system名下)
    grant resource to username;
    grant dba to username;
 
 

--修改密码
alter user username identified by orcle;

--创建表(表名以字母 _ 开头,长度小于128个字符,不用SQL保留字)
create table tablename
(
  heroId int,
  heroName varchar(50),
  heroNickName varchar(50),
  sex char(4),
  salary int 
);

--查看编码格式
select userenv('language') from dual;

--删除一张表(把标的结构和表的数据一起删除)
--drop table tablename ;

---SQL语句查询数据
select * from tablename

--删除全部数据
delete from tablename ;

--删除全部数据
delete from tablename where 字段名1=? ;
delete from tablename where 字段名 is null;
delete from tablename where 字段名1=? and 字段名2= ?;
delete from tablename where (字段名1=? and 字段名2= ?) or 字段名3= ? ;

--修改语句
update tablename set 字段名1= ?, 字段名2= ? where 字段名3 < ? ;
update tablename set 字段名1= ?, 字段名2= ? where 字段名3 is null ;