oracle dba 应该熟悉的命令（7）

151 建立一个函数索引
SELECT /*+ INDEX(A A_NAME2_IND) */ * FROM A WHERE UPPER(OBJECT_NAME)='A';
152 建立一个位图索引
CREATE BITMAP INDEX A_NAME_BT ON A(OWNER);
153 查询数据库的信息
SELECT /*+ INDEX(A A_NAME_BT) */ * FROM A WHERE OWNER='SYS' OR OWNER='SYSTEM';
SELECT /*+ FULL(A) */ * FROM A WHERE OWNER='SYS' OR OWNER='SYSTEM';
154 生成一个脚本来建立索引重整
SELECT 'ALTER INDEX '||INDEX_NAME||' COALESCE;' FROM DBA_INDEXES WHERE TABLE_NAME='A' AND OWNER='SYS'
SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD ONLINE;' FROM DBA_INDEXES WHERE TABLE_NAME='A' AND OWNER='SYS'
155 显示所有用户的信息
SELECT * FROM DBA_USERS;
156 显示当前数据库的用户
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS;
160 建立一个新用户
create user edpthw identified by "123456"
default tablespace users
temporary tablespace temp
quota unlimited on users
quota 10m on mytbs2;
161 查询限额信息
select * from dba_ts_quotas;
select * from user_ts_quotas;
162 用户改密码
alter user edpthw identified by edpthw;
163 锁用户
alter user edpthw account lock;
164 使密码过期,迫使改下一次进入改密码
alter user edpthw password expire;
165 取消限额
alter user edpthw quota unlimited on mytbs2;
166 删除用户
drop user hmtong cascade;
167 授权用户
grant create session,create table,create view to edpthw;
168 分别授权
GRANT CREATE ANY TABLE TO "hmtong"
GRANT CREATE TABLE TO "hmtong"
GRANT DROP ANY TABLE TO "hmtong"
GRANT SELECT ANY TABLE TO "hmtong"
169 折消授权
REVOKE UNLIMITED TABLESPACE FROM "hmtong"
170 对象授权
GRANT SELECT ON "hmtong"."A" TO "edpthw"
171 折消对象授权
REVOKE SELECT ON "hmtong"."A" FROM "edpthw"
172 取消进入系统直接用默认密码,而一定要自己输入密码才可以进入
unix $ORACLE_HOMENETWORKADMINSQLNET.ORA
windows D:oracleproduct10.1.0Db_1NETWORKADMINSQLNET.ORA
SQLNET.AUTHENTICATION_SERVICES= (NTS,NONE)
修改$ORACLE_HOME/network/admin/sqlnet.ora文件
　　将原有的：SQLNET.AUTHENTICATION_SERVICES= (NTS)
　　改为:SQLNET.AUTHENTICATION_SERVICES= (NONE)
　　或者直接注释：#SQLNET.AUTHENTICATION_SERVICES= (NTS)
173 查询Pwfile中存放的用户信息
select * from v$pwfile_users;
174 授权sysdba给指定用户
grant sysdba to hmtong;
175 取消指定用户的sysdba权限
revoke sysdba from hmtong;
176 修改系统的授权的属性
alter system set remote_login_passwordfile=exclusive; 能sysdba登录,能授权
alter system set remote_login_passwordfile=shared scope=spfile; 只能sysdba登录,不能授权
alter system set remote_login_passwordfile=NONE; 取消
177 重建口令文件
orapwd file=PWDmydb.ora password=itpub
178 创建一个用户
create user hmtong identified by abcdefg;
179 授权连接给指定用户
GRANT "CONNECT" TO "hmtong";
GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO HMTONG;
180 创建表
create table t1(a int);
create table t2(a int);