四、对object的操作，这些操作应该包扩对table,index,constrait,view,synonyms,sequence,function,procedure等的操作
1 Table:
数据字典管理模式下创建表(create table as data dictionary managed)
create table tablename(column1,column2……)
storage(initial 200k next 200k pctincrease 0 minextents 1 maxextents 5)
tablespace data;
创建临时表
create global temporary table hr.employees_temp as
select * from hr.employees
改变表的存储
alter table hr.employees
pctfree 30
pctused 50
storage (next 500k minextents 2 maxextents 100)
移动表到另一个表空间
alter table hr.employees move tablespace data1;
清空表但不写入日志
truncate table hr.employees;
删除表
drop table hr.dempartment cascade constraints
删除表的某一个字段
alter table hr.employees drop column comments dascade constraints checkpoint 1000
获得表的信息
select table_name from dba_tables where owner = 'HR';
select object_name ,create from dba_objects
where object_name like 'employees' and owner = 'HR';
其它
select * from user_tables;
select object_name,object_id from user_objects where instr(object_name,'LOG')>0
select sum(bytes)/(1024*1024) from user_segments
where segment_name='table_name'
select table_name,cache from user_tables where instr(cache'Y') > 0
2 Index
create normal b-tree indexes
create index hr.employees_last_name_idx on hr.employees(last_name)
pctfree 30
storage(initial 200k next 200k pctincrease 0 maxextents 50)
tablespace indx;
create bitmap indexes
create bitmap index orders_region_id_idx on orders(region_id)
pctfree 30
storage(initial 200k next 200k pctincrease 0 maxextents 50)
tablespace indx
changing storage parameters for indexes
alter index employees_last_name_idx storage(next 400k maxextents 100);
alter index orders_region_id_idx
allocate extent (size 200k datafile '/disk/indx01.dbf');
alter index orders_id_idx deallocate unused;
重组index
alter index orders_region_id_idx rebuild tablespace indx02
online rebuild index
alter index orders_id_idx rebuild online
coalescing index
alter index orders_id_idx coalesce;
删除索引
drop index hr.departments_name_id;
其它
select index_name,index_type,table_name from user_indexes order by table_name;
select * from user_ind_columns where index_name = 'index_name';
select sum(bytes)/(1024*1024) from user_segments where segment_name = 'index_name'
3 constraits:
Defining constraints while creating a table
create table hr.employee(id number(7) constraint employee_id_pk primary key
deferrable using index storage(initial 100k next 100k) tablespace indx,
last_name varchar2(25)
constraint employee_last_name_nn not null,
dept_id number(7))
tablespace users;
查看某表的约束条件
select c.constraint_name,c.constraint_type,cc.column_name
from dba_constraints c,dba_cons_columns cc
where c.owner = 'HR'
and c.table_name='EMPLOYEE'
and c.owner = cc.owner
and c.constraint_name = cc.constraint_name
order by cc.position;
查看外健约束
select c.constraint_name as "Foreign Key",
p.constraint_name as "Referenced Key",
p.constraint_type,p.owner,p.table_name
from dba_constraints c,dba_constraints p
where c.owner = 'HR'
and c.table_name = 'EMPLOYEE'
and c.constraint_type='R'
and c.r_owner=p.owner
and c.r_constraint_name = p.constraint_name;
查看约束信息
select constraint_name,constraint_type,deferrable,derered,validated
from dba_constraints
where owner = 'HR' and table_name= 'EMPLOYEE'
select constraint_name,constraint_type,search_condition,r_constraint_name
from user_constraints where table_name = 'table_name'
4 View
查看视图名称
select view_name from user_views
查看创建视图的select语句
select text from user_views where view_name=upper('&view_name')
5 Sequence
查看序列号
select * from user_sequences;
创建序列号
create sequence 名称 increment by 1 start with 1 maxvalue 999999 cycle nocache
使用
序列号名称.nextval
6 同义词
查看同义词名称
select * from user_synonyms
7 存储过程和函数
查看函数和过程的状态
select object_name,status from user_object where object_type='FUNCTION'
查看函数和过程的内容
select text from all_source where owner=user and name=upper('&plsql_name')
8 使用数据字典的dictionary 和dict_columns表
select * from dictionary where instr(comments,'index')>0
select column_name,comments from dict_columns where table_name='USER_INDEXES'
五 对用户的操作，应改包括用户，角色，权限，Session的操作
1 Profile
创建一个Profile
create profile grace_5 limit
failed_login_attempts 3
pasword_lock_time unlimited
password_life_time 30
password_reuse_time 30
password_verify_function verify_function
password_grace_time 5
更改一个Profile
alter profile default
failed_login_attempts 3
password_life_time 60
password_grace_time 10
删除一个Profile
drop profile developer_prof
drop profile developer_prof cascade
启用resource limits
设定resource_limit，或使用alter system命令
alter system set resource_limit=true
创建一个Profile，使用Resource Limit
create profile developer_prof limit
sessions_per_user 2
cpu_per_session 10000
idle_time 60
connect_time 480
查看用户信息
select username,password,account_status from dba_users
查看profile信息
select * from dba_profiles where resource_type='PASSWORD' and profile='GRACE_5'
2 User
创建一个新用户，使用数据库验证
create user aaron
identified by soccer
default tablespace data
temporary tablespace temp
quota 15m on data
password expire;
创建一个新用户，使用系统验证
os_authent_prefix参数指名用户名格式
如果os_authent_prefix=ops$，这是默认的参数
create user ops$user identified by password ……
如过os_authent_prefix='',则是这样创建
create user aaron
identified externally
default tablespace users
temporary tablespace temp
quota 15M on data
password expire;
登入是直接在Shell里输入sqlplus /
remote_os_authent=true指明了用户可以从远程操作系统登入
改变用户的quota
alter user aaron quota 0 on users
删除用户
drop user aaron
drop user cascade
查看所有用户的默认表空间
select username,default_tablespace from dba_users
3 权限
分发系统权限
grant {system_privilege|role}[,{system_privilege|role}]
to {user|role|public}[,{user|role|public}]
[with admin option}
with admin option,使用这个参数可以把这个用户或得的权限分配给下一个人
回收系统权限
revoke {system_privilege|role}[,{system_privilege|role}]
from {user|role|public}[,{user|role|public}]
grant object privileges
grant { object_privileges [(column_list)][,object_privilege[(column_list)]]……
|all [privileges]}
on [schema.] object
to {user|role|public}[,{user|role|public}]……
[with grant option]
使用with grant option ，可以把这个用户或得的权限分配给下一个人
Revoking Object Privileges
revoke{ object_privilege[,object_privilege]……
|AlL [privileges]}
on [schema.]object
from {user|role|public}[,{user|role|public}
[cascade constraints]
其它
查看当前用户的缺省表空间
select username,default_tablespace from user_users;
查看当前用户的角色
select * from user_role_privs
查看当前用户的系统权限和表级权限
select * from user_sys_privs
select * from user_tab_privs
六、网络
监视器的管理
lsnrctl start
lsnrctl start my_lsnr
lsnrctl status
lsnrctl status hq
检查监视器进程
ps -ef | grep tnslsnr
在监视器内操作
set password lsnr_password
start
service
stop
七、备份与恢复，命令很多，简单列举几个，详细的自已查书吧
1 逻辑备份
输出的例子
exp system/manager file=expdat.dmp owner=(HR,USER)
exp system/manager file=hr.dmp owner=HR indexes=Y compress=Y
exp system/manager file=expdat.dmp table=(HR.employees)
exp system/manager file=expdat.dmp tables(hr.employees:part1)
输入的例子
imp system/manager file=expdat.dmp
imp system/manager file=expdat.dmp buffer=64000 commit=Y
imp system/manager file=expdat.dmp fromuser=HR touser=HS rows=y indexes=y
完全备份
exp_comp.sh
rq=`date +"%m%d"`
su -oracle -c "exp system/manager full=y inctype=complete file=/oracle/export/db_comp$rq.dmp"
exp_cumu.sh
rq=`date +"%m%d"`
su -oracle -c "exp system/manager full=y inctype=cumulative file=/oralc/export/db_cumu$rq.dmp"
exp_incr.sh
rq=`data +"%m%d"`
su -oracle -c "exp system/manager full=y inctype=incremental file=/oracle/export/db_incr$rq.dmp"
crontab:
0 2 1 * * /oracle/exp_comp.sh
30 2 * * 0-5 /oracle/exp_incr.sh
45 2 * * 6 /oracle/exp_cumu.sh
2 RMAN
备份与回复的一般格式
run {
allocate channel c1 type disk
format = '/db01/backup/user0520.bak';
backup datafile '/db01/oradata/users01.dbf';
}
change the default device type:
configure default device type to sbt;
Configure parallelism for automatic channels:
configure device type disk parallelism 3;
Configure automatic channel options:
configure channel device type disk
format =`/backup/rman/$U';
configure channel device type disk maxpiecessize 2G
查看默认的配置
show all
list backups of all files in the database:
list backup of database
查看要schema
report schema
