Oracle基本命令

Oracle基本命令
进入Oracle
sqlplus /nolog
sysdba身份建立链接(@后面为所配置的Net服务名localhost)
conn /as sysdba;
conn sys/123 as sysdba
或 conn sys/123@localhost as sysdba

01.创建表空间（可以再建一个索引表空间jiandanidx）
create tablespace jiandanspace datafile 'D:\oracle\product\10.1.0\oradata\orcl\jiandanspace01.dbf' size 20m autoextend on;
修改表空间大小
alter database datafile'D:\oracle\product\10.1.0\oradata\orcl\jiandanspace01.dbf' resize 30m;

02.创建用户(密码不能全部数字)
create user jiaqiufeng identified by jia;
或 create user jiaqiufeng identified by jia default tablespace jiandanspace temporary tablespace temp;

03.给用户授权(grant 权限 to 用户;)
grant dba to jiaqiufeng;
grant create tablespace to jiaqiufeng;
grant connect,resource,create any table,drop any table,create sequence,
     alter any trigger, create any trigger, drop any trigger,
     alter any type, create any type, drop any type,
     create any view, drop any view,
     create procedure,imp_full_database,exp_full_database,
     create any directory ,debug connect session,
     create session to jiaqiufeng;

取消权限(revoke 权限 from 用户名;)
revoke dba from jiaqiufeng;

04.创建序列
create sequence s_workid
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20

05.创建表
create table t_workinfo
(
  workid       number(20) not null,
  workcode     varchar2(70) not null,
  workpwd      varchar2(140) not null
)tablespace jiandanspace
/
-- add comments to the table
comment on table t_workinfo
is '管理员信息表'
/
--add comments to the columns
comment on column t_workinfo.workid
is '管理员编号'
/
comment on column t_workinfo.workcode
is '管理员账号'
/
comment on column t_workinfo.workpwd
is '管理员密码'
/
--添加主键
alter table t_workinfo
add constraint pk_t_workinfo primary key (workid) using index tablespace jiandanidx
/
--添加唯一索引
create unique index ix_admininfo_code on t_admininfo (workcode) tablespace jiandanidx
/

-- 若主键有多个，可加"，"分开，如下：
alter table t_corpinfo
  add constraint primarykey_corpinfo_id primary key (workid, workcode)
  using index
  tablespace jiandanidx

06.创建函数，存储过程
create or replace function f_login_pwd_check
(
    i_in_operateid        in     number,  --操作员账号
    i_in_operatortype     in     number,  --操作员类型(1.系统管理员 2.铃音制作商 3.集团管理员)
    i_in_loginPwdTimeout  in     number   --配置项中密码有效期
                                          --返回值 0过期 1没过期
) return integer

is
    i_l_count   integer;
    i_l_lastpwdtime integer;
    dt_l_createtime date;
begin
    select count(1) into i_l_count
      from t_operatorinfo
     where operateid = i_in_operateid and operatortype = i_in_operatortype;
 
    if (i_in_operatortype = 1) then
        select a.createtime into dt_l_createtime from t_admininfo a where a.adminid = i_in_operateid;
    end if;
    if (i_in_operatortype = 2) then
        select s.createtime into dt_l_createtime from t_spinfo s where s.spid = i_in_operateid;
    end if;
    if (i_in_operatortype = 3) then
        select c.createtime into dt_l_createtime from t_corpinfo c where c.corpid = i_in_operateid;
    end if;

    if (i_l_count = 0) then
        insert into t_operatorinfo
                    (
                    operateinfoid,
                    operateid,
                    operatortype,
                    status,
                    lastlogined,
                    failnum,
                    lastpwdtime
                    )
             values
                    (
                    s_operatorinfoid.nextval,
                    i_in_operateid,
                    i_in_operatortype,
                    1,
                    sysdate,
                    0,
                    dt_l_createtime
                    );
                  
        commit;
    end if;  

    select (sysdate - lastpwdtime) * 24 * 60 * 60 into i_l_lastpwdtime
      from t_operatorinfo
     where operateid = i_in_operateid and operatortype = i_in_operatortype;

    if (i_l_lastpwdtime < i_in_loginPwdTimeout*24*60*60) then
        return 1;
    end if;
    return 0;
exception
    when others then
      --记录异常信息
    --回滚
    rollback;
    return -1;
end f_login_pwd_check;

07.初始化数据
insert into t_workinfo(workid,workcode,workpwd)
values (s_workid.nextval,'xiaoming','123456');



显示当前用户
show user;

退出Oracle
exit;

用户建立连接,也可直接切换用户(conn 用户名/密码;)
conn jiaqiufeng/jia;
锁定用户(alter user 用户名 account lock;)
alter user jiaqiufeng account lock;
给用户解锁(alter user 用户名 account unlock;)
alter user jiaqiufeng account unlock;
修改用户密码(alter user 用户名 identified by 密码;)
alter user jiaqiufeng identified by feng;
修改用户属性(alter user 用户名 属性 属性值)
alter user jiaqiufeng default tablespace xiaojingzispace;
删除用户(drop user 用户名 cascade)
drop user xiaojingzi cascade;
改变表空间的读写状态(alter tablespace 表空间名 状态;)
alter tablespace jiandanspace read only;//只读
alter tablespace jiandanspace read write;//可读写
删除表空间(drop tablespace 表空间名;)
drop tablespace jiandanspace;
drop tablespace jiandanspace including contents;//表空间中含有数据
建表(create table 表名(属性名1 类型1,属性名2 类型2,...);)
create table worker(id varchar2(10),name varchar2(20));