Oracle常用命令大全

ed 把当前敲进去的输出到文件， 关闭文件后会把内容再次输入到屏幕，然后敲入 /, 然后回车就可以再次执行
把window的Oracle服务设置成手动，然后写一个bat文件
把这段文字写进去 ， 每次到目录下去执行这个文件便可以启动监听器和Oracle服务
lsnrctl start
oradim -starup -sid portal

停止的脚本
lsnrctl stop
oradim -shutdown -sid portal

－－总结启动命令如下：
lsnrctl [start|stop|status]                  －－启动监听器，停止监听器，查看监听器的状态；
net [start|stop] oracleserviceorcl            -- 启动Oracle服务

启动监听
lsnrctl start

启动实例
oradim -starup -sid orcl
net [start|stop] oracleserviceorcl

连接：
conn system/qqqqqq@oradb as sysdba
sqlplus rocky/password
sqlplus / as sysdba

3个默认用户
 sys chang_on_install [as sysdba]
 system manager
 scott tiger
查看当前用户
 show user

设置屏幕缓冲区大小
 set linesize 300;

创建用户
 create user Rocky identified by password
 -- Create the user
 create user rockyUser
   identified by rockypassword
   default tablespace USERS
   temporary tablespace TEMP;
 -- Grant/Revoke object privileges
 grant select, insert, update, delete, references, alter, index on T1 to rockyUser;
 -- Grant/Revoke system privileges
 grant create table to rockyUser;

系统权限
 grant create session to Rocky;
 grant create table to Rocky;
 grant unlimited tablespace to Rocky;

 revoke create session to Rocky;
 revoke create table to Rocky;
 revoke unlimited tablespace to Rocky;

 grant create session to public;

 select * from user_sys_privs;

对象权限
 grant select on mytable to Rocky;
 grant all on mytable  to Rocky;

 select * from user_tab_privs;

对象权限可以控制到列
 grant update(name) on mytable to Rocky;
 grant insert(id) on my tab to Rocky;

 select * from user_col_privs;
 

注意：查询和删除不能控制到列.

权限传递
 grant alert any table to rocky with admin option;
 grant select on A to rocky with grant option;

角色
 create role myrole;
 drop role myrole;
 grant create session to myrole;
 grant myrole to rocky;
 注意：
  unlimited tablespace 不能授予角色
  create table create any table
  [alert table] alert any table
  [drop table] drop any table
  表是属于用户的。
  角色不论谁创建的，不属于任何一个用户，是公用的

更改密码
 alert user rocky identifier by password;

增加数据库表的列
 -- Add/modify columns
 alter table T1 add ADDRESS varchar2(200) default BEIJING not null;
 -- Add comments to the columns
 comment on column T1.ADDRESS  is 'ADRESS';
删除列
 alter table T2 drop column F_ID;

修改数据库表字段的声明类型
 -- Add/modify columns
 alter table T1 modify NAME VARCHAR2(250);
 alter table T2 modify ID timestamp; 改类型
 alter table T2 modify NAME not null; 不空
 alter table T2 modify NAME null; 设为可空
 alter table T2 rename column NAME to NAME_EN; 改名

增加主键：
 -- Create/Recreate primary, unique and foreign key constraints
 alter table T1  add constraint PK_ID1 primary key (ID);

 -- Create/Recreate primary, unique and foreign key constraints
 主键：  alter table T2  add constraint PK_ID primary key (ID);
 唯一性约束： alter table T2  add constraint UK_NAME unique (NAME_EN);
 外键：         alter table T2  add constraint FK_ID foreign key (F_ID)  references t1 (ID) on delete cascade;其中 FK_ID是名字，括号里的是字段， t1是表

 删除， 直接删除其名字就可以
 -- Drop primary, unique and foreign key constraints
 alter table T2  drop constraint PK_ID cascade;
 alter table T2  drop constraint UK_NAME cascade;
 alter table T2  drop constraint FK_ID;

 
 -- Grant/Revoke object privileges
 grant select, insert, update, delete, references, alter, index on T2 to test; T2是表名，test是另一个用户
 grant select, insert, update, delete, alter on T2 to scott with grant option; 带有grant的

更改口令文件
把原来口令文件删除
D:/oracle/product/10.2.0/db_1/database/PWDportal.ora
orapwd file=D:/oracle/product/10.2.0/db_1/database/PWDportal.ora password=sys enties=10;

select * from v$pwfile_users;

 

 

-- Create the user
create user SCOTT
  identified by ""
  default tablespace USERS
  temporary tablespace TEMP
  profile DEFAULT
  password expire
  account lock;
-- Grant/Revoke role privileges
grant connect to SCOTT;
grant resource to SCOTT;
-- Grant/Revoke system privileges
grant unlimited tablespace to SCOTT;

 

-- Create the user
create user SYSTEM
  identified by ""
  default tablespace SYSTEM
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke object privileges
grant execute on CHECK_UPGRADE to SYSTEM;
grant execute on DBMS_ALERT to SYSTEM;
grant execute on DBMS_AQ to SYSTEM with grant option;
grant execute on DBMS_AQADM to SYSTEM with grant option;
grant execute on DBMS_AQELM to SYSTEM with grant option;
grant execute on DBMS_AQ_IMPORT_INTERNAL to SYSTEM with grant option;
grant execute on DBMS_DEFER_IMPORT_INTERNAL to SYSTEM;
grant execute on DBMS_REPCAT to SYSTEM;
grant execute on DBMS_RULE_EXIMP to SYSTEM with grant option;
grant execute on DBMS_SYS_ERROR to SYSTEM;
grant execute on DBMS_TRANSFORM_EXIMP to SYSTEM with grant option;
grant select, insert, update, delete, references, alter, index on INCEXP to SYSTEM;
grant select, insert, update, delete, references, alter, index on INCFIL to SYSTEM;
grant select, insert, update, delete, references, alter, index on INCVID to SYSTEM;
grant execute on SET_TABLESPACE to SYSTEM;
grant execute on SYS_GROUP to SYSTEM;
-- Grant/Revoke role privileges
grant aq_administrator_role to SYSTEM with admin option;
grant dba to SYSTEM with admin option;
grant mgmt_user to SYSTEM;
-- Grant/Revoke system privileges
grant create materialized view to SYSTEM;
grant create table to SYSTEM;
grant global query rewrite to SYSTEM;
grant select any table to SYSTEM;
grant unlimited tablespace to SYSTEM with admin option;