56、关于数据库进程的问题。
(1).查看相关进程在数据库中的会话
Select a.sid,a.serial#,a.program, a.status ,
substr(a.machine,1,20), a.terminal,b.spid
from v$session a, v$process b
where a.paddr=b.addr
and b.spid = &spid;
(2).查看数据库中被锁住的对象和相关会话
select a.sid,a.serial#,a.username,a.program,
c.owner, c.object_name
from v$session a, v$locked_object b, all_objects c
where a.sid=b.session_id and
c.object_id = b.object_id;
(3).查看相关会话正在执行的SQL
select sql_text from v$sqlarea where address =
( select sql_address from v$session where sid = &sid );
57、查看IP地址
select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;
58、运行SQLPLUS时不用输入用户名和密码,进入之后使用CONNECT
SQLPLUS /NOLOG
SQL>CONNECT SCOTT/TIGER
59、查看当前会话
userenv() 函数
select userenv('language') from dual 字符集
select userenv('isdba') from dual 是否DBA
select userenv('sessionid') from dual sessionid
select userenv('TERMINAL') from dual 客户端名字
select userenv('INSTANCE') from dual 实例数
SYS_CONTEXT() 函数
select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') from dual; 当前模式
select SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') from dual; 当前模式ID
select SYS_CONTEXT('USERENV','CURRENT_USER') from dual; 当前用户
select SYS_CONTEXT('USERENV','DB_NAME') from dual; 数据库
select SYS_CONTEXT('USERENV','HOST') from dual; 主机
..........
60、删除重复列的方法
(1) DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
(2) create table table2 as select distinct * from table1;
drop table1;
rename table2 to table1;
(3) Delete from mytable where rowid not in(
select max(rowid) from mytable
group by column_name );
(4) delete from mytable t1
where exists (select 'x' from my_table t2
where t2.key_value1 = t1.key_value1
and t2.key_value2 = t1.key_value2
...
and t2.rowid > t1.rowid);
61、ORA-12571: TNSscreen.width/2)this.style.width=screen.width/2;" border="0" />acket writer failure(包写入程序失败)
(1) 这个错误在客户端遇到过,通常重新连接一下服务器就好了。
服务器重新启动的时候,在client也会遇到该错误。
这个错误你是在server还是client上遇到的?最常用的办法就是加上跟踪,查看一下 跟踪记录,分析分析错误的原因。
网络问题也会出现该错误,比如网络路由没有配置好。
(2) 安装的杀毒软件导致的
(3) 服务器端的IP是否被改动
(4) 最后不行的话,重新创建监听器
62、ORACLE服务不能自动启动的解决办法
把ORACLEHOMEnetworkADMINsqlnet.ora
文件中的 sqlnet.authentication_service=(nts)
注释掉就可以了
63、不完全的时间点恢复
shutdown immediate
copy 备份文件到需要恢复的目录下
startup mount
recover database until time '2002-12-26 09:00:00'
alter database open resetlogs
自己仔细检查一下,不会发生这样的问题的。
64、oracle如何设置查询超时
select /*+ timeout 30*/ * from veryLargeTable
65、修改字符集
(1)、ALTER DATABAE CHARACTER SET SIMPLIFIED CHINESE_CHINA.ZHS16GBK ;
(2)、update props$ set value$='ZHS16CGB231280'
where name='NLS_CHARACTERSET';
update props$ set value$='ZHS16CGB231280'
where name='NLS_NCHAR_CHARACTERSET';
建议不使用(2)
注意:
(1)、执行ALTER DATABASE CHARACTER SET必须有SYSDBA权限,并且在STARTUP RESTRICT模式下执行
(2)、原字符集必须是目标字符集的一个真子集(就是浪子所说的只能从WE8ISO8859P1转到ZHS16GBK的原因)
(3)、CLOB字段装换可能有问题,建议在转换以前把有CLOB字段的表导出后DROP,转换以后再导回
(4)、该转换不可逆,所以在做这个操作以前建议做数据库全备份
66、修改数据库名字
(1)、启动svrmgrl,以文本方式备份控制文件
oracle>svrmgrl
svrmgrl>connect internal
svrmgrl>alter system backup controlfile to trace
(2)、编辑产生的跟踪文件,在udump目录下
改CREATE CONTROLFILE REUSE DATABASE "CTC" NORESETLOGS ARCHIVELOG
中的REUSE为SET
然后把create controlfile这段语句拷出
(3)、正常宕库,后启动到nomount下
svrmgrl>shutdown immediate
svrmgrl>startup nomount
(4)、执行create controlfile那段语句
(5)、打开数据库
svrmgrl>alter database open
如提示用resetlogs选项则使用
svrmgrl>alter database open resetlogs
(8)、相应修改初始化参数
67、rownum的用法
select * from (select t.*,rownum id from dept t)
where id between 1 and 20
68、oracle的内部参数
SELECT a.ksppinm NAME,
b.ksppstdf default_val,
a.ksppdesc DESCRIPTION
FROM x$ksppi a,
x$ksppcv b
WHERE a.indx=b.indx
AND substr(a.ksppinm,1,1)='_'
ORDER BY a.ksppinm
69、9i安装时报areasqueries错误的解决办法
包括IAS 和 IDS
把安装源文件目录全部改为英文字母或数字
注意:不能是中文的路径
70、我如何知道一个表空间还有多少可以用
(1)、
SELECT upper(f.tablespace_name) 表空间名,
d.Tot_grootte_Mb "表空间大小(M)",
d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",
round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "使用比",
f.total_bytes "空闲空间(M)",
f.max_bytes "最大块(M)"
FROM
(SELECT tablespace_name,
round(SUM(bytes)/(1024*1024),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC
(2)、select tablespace_name,round(sum(bytes)/1024/1024,2) "M" from dba_free_space
group by tablespace_name
71、creck pl/sql developer 的方法
(1)、安装pl/sql developer
(2)、用UltraEdit将程序PLSQLDev.exe打开
(3)、将UltraEdit设置为16进制模式
(4)、查找串:BA 1E 00 00 00 2B D0
修改:2B D0 为:4A 90
(5)、存盘退出
(6)、运行PLSQLDev.exe,如果提示你还有29天的时间可用,那就恭喜你了!
72、使索引无效
ALTER INDEX idx UNUSABLE;
ALTER INDEX idx_acctno DISABLE;(only to a function based index)
73、在SQLPLUS中给指定用户进行 set autotrace on/off
以SCOTT用户为例:
SQL>CONNECT scott/tiger
connected.
SQL>@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL
Table created.
SQL>CONNECT / AS SYSDBA
connected.
SQL>@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL
drop role plustrace;
Role dropped.
create role plustrace;
Role created.
.
grant plustrace to dba with admin option;
Grant succeeded.
SQL>GRANT PLUSTRACE TO SCOTT;
Grant succeeded.
SQL>CONNECT SCOTT/TIGER
connected.
SQL>set autotrace on
SQL>
74、关于约束的四种状态
Disabled novalidate:当约束使不能时,约束的规则不能强制在列
(包含在约束中)的数据之上。但约束的定义保存在数据字典中。
在执行数据仓库卷起(rollup)或装载且要加快装载过程时该方式
是有用的。
Enabled novalidate:是能无效,该状态的表可以包含非法
的数据,但不可能加入新的非法数据。
Enabled validate:使能有效,一个使能的约束是强制的,表的数据检查
有效
75、在SQLPLUS中调用存储过程
SET SERVEROUTPUT ON
declare
out_param varchar2(100);
begin
your_proc(1,out_param);
dbms_output.put_line(out_param);
end;
/
SET SERVEROUTPUT OFF
75、生成系统表和存储过程的三个文件。
cat*.sql
dbms*.sql
utl*.sql
76、JOB中日期的使用
每个月1号:
last_day(sysdate)+1
每个季度的第一天:
to_date(decode(to_char(sysdate,'q'),'1',to_char(sysdate,'yyyy')||'0101',
'2',to_char(sysdate,'yyyy')||'0401','3',to_char(sysdate,'yyyy')||'0701',
'4',to_char(sysdate,'yyyy')||'1001'),'yyyymmdd')
每天:
sysdate+1
每个星期几:
decode(to_char(sysdate,'w'),'1',sysdate+7,
to_char(sysdate,'w'),'2',sysdate+6,to_char(sysdate,'w'),'3',sysdate+5,
to_char(sysdate,'w'),'4',sysdate+4,to_char(sysdate,'w'),'5',sysdate+3,
to_char(sysdate,'w'),'6',sysdate+2,to_char(sysdate,'w'),'7',sysdate+1)
每个星期x下午三点:interval(21, 'next_day(trunc(sysdate),x+1)+15/24');
每个季度的第一个星期x:
interval(21, 'next_day(trunc(sysdate,''Q''),3),5)');
77、使用execute immediate 的问题
8i以上才支持execute immediate
8.05只能用dbms_sql
最好使用execute immediate
78、ORACLE9i中删除表空间中数据文件的方法
drop tablespace tbsname including contents
79、找出串中的数字
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')
"Translate example"
FROM DUAL
/
2229
--全是数字的:
select * from 你的表 where translate(你的列,'0123456789',' ')='';
select * from 你的表 where trim(ltrim(rtrim(replace(col_name,'0123456789',' ')))) is null
80、分析表
analyze table mzbs.db_code ESTIMATE STATISTICS SAMPLE 20 PERCENT;
81、表空间管理和用户管理
--查看表空间和数据文件
select file_name,tablespace_name,autoextensible from dba_data_files;
--数据表空间
CREATE TABLESPACE USER_DATA
LOGGING
DATAFILE 'D:ORACLEORADATAORCLtest.DBF' SIZE 50m REUSE ,
'c:USERS01112.DBF' SIZE 50m REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL
--临时表空间
CREATE TEMPORARY
TABLESPACE USER_DATA_TEMP TEMPFILE 'D:TEMP0111.DBF'
SIZE 50M REUSE AUTOEXTEND
ON NEXT 1024K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K
--增加数据文件
ALTER TABLESPACE USER_DATA
ADD DATAFILE 'c:USERS01113.DBF' SIZE 50M;
ALTER TABLESPACE USER_DATA
ADD DATAFILE 'c:USERS01114.DBF' SIZE 50M
AUTOEXTEND ON
;
--删除表空间
DROP TABLESPACE USER_DATA INCLUDING CONTENTS;
--修改数据文件大小
ALTER DATABASE
DATAFILE 'c:USERS01113.DBF' RESIZE 40M;
--创建用户、赋予权限
CREATE USER USER_DATA PROFILE DEFAULT IDENTIFIED BY USER_DATA
DEFAULT
TABLESPACE USER_DATA TEMPORARY
TABLESPACE USER_DATA ACCOUNT UNLOCK;
GRANT CONNECT TO USER_DATA;
GRANT RESOURCE TO USER_DATA;
--把表移到另一个表空间
ALTER TABLE TABLENAME MOVE TABLESPACE TABLESPACENAME;
--创建索引
CREATE INDEX INDEXNAME ON TABLENAME(COLUMNNAME);
CREATE INDEX INDEXNAME ON TABLENAME(COLUMNNAME) TABLESPACE TABLESPACENAME;
--重新建立索引
ALTER INDEX INDEXNAME REBUILD TABLESPACE TABLESPACE;
--创建表
CREAE TABLE TABLENAME
(COLUMN1 COLUTYPE DEFAULT(value) NOT NULL)
(COLUMN2 COLUTYPE DEFAULT(value) NOT NULL);
--建表的索引存储分配
CREATE TABLE summit.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 data;
--建立主键
ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY(COLUMN1,COLUMN2)
--使约束无效
ALTER TABLE TABLENAME ENABLE NOVALIDATE CONSTRAINT CONSTRANAME;
ALTER TABLE TABLENAME ENABLE VALIDATE CONSTRAINT CONSTRANAME;
--删除约束
ALTER TABLE TABLENAME DROP CONSTRAINT constraintname;
DROP TABLE TABLENAEM CASCADE CONSTRAINTS;(删除表后将所用的外键删除)
--给表增加列
ALTER TABLE TABLENAME
ADD COLUMN COLUTYPE DEFAULT(value) NOT NULL;
--给列增加缺省值
ALTER TABLE TABLENAME
MODIFY COLUMNNAME DEFAULT(value) NOT NULL;
--给表增加外键
ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME
FOREIGN KEY(COLUMN) REFERENCES TABLE1NAME(COLUMN1);
1、分析表
analyze table mzbs.db_code ESTIMATE STATISTICS SAMPLE 20 PERCENT;
2、表空间管理和用户管理
--查看表空间和数据文件
select file_name,tablespace_name,autoextensible from dba_data_files;
--数据表空间
CREATE TABLESPACE USER_DATA
LOGGING
DATAFILE 'D:ORACLEORADATAORCLtest.DBF' SIZE 50m REUSE ,
'c:USERS01112.DBF' SIZE 50m REUSE
AUTOEXTEND
ON NEXT 1280K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL
--修改表空间数据文件的路径
ALTER TABLESPACE app_data
RENAME
DATAFILE '/DISK4/app_data_01.dbf'
TO '/DISK5/app_data_01.dbf';
ALTER DATABASE
RENAME FILE '/DISK1/system_01.dbf'
TO '/DISK2/system_01.dbf';
--临时表空间
CREATE TEMPORARY
TABLESPACE USER_DATA_TEMP TEMPFILE 'D:TEMP0111.DBF'
SIZE 50M REUSE AUTOEXTEND
ON NEXT 1024K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K
--增加数据文件
ALTER TABLESPACE USER_DATA
ADD DATAFILE 'c:USERS01113.DBF' SIZE 50M;
ALTER TABLESPACE USER_DATA
ADD DATAFILE 'c:USERS01114.DBF' SIZE 50M
AUTOEXTEND ON
;
--删除表空间
DROP TABLESPACE USER_DATA INCLUDING CONTENTS;
--修改表空间的存储参数
ALTER TABLESPACE tablespacename
MINIMUM EXTENT 2M;
ALTER TABLESPACE tablespacename
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MAXEXTENTS 999 );
--表空间联机/脱机/只读
ALTER TABLESPACE tablespacename OFFLINE/ONLINE/READ ONLY;
--修改数据文件大小
ALTER DATABASE
DATAFILE 'c:USERS01113.DBF' RESIZE 40M;
--创建用户、赋予权限
CREATE USER USER_DATA PROFILE DEFAULT IDENTIFIED BY USER_DATA
DEFAULT
TABLESPACE USER_DATA TEMPORARY
TABLESPACE USER_DATA ACCOUNT UNLOCK;
GRANT CONNECT TO USER_DATA;
GRANT RESOURCE TO USER_DATA;
3、表的管理
--创建表
CREAE TABLE TABLENAME
(COLUMN1 COLUTYPE DEFAULT(value) NOT NULL)
(COLUMN2 COLUTYPE DEFAULT(value) NOT NULL);
--建表的索引存储分配
CREATE TABLE summit.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 data;
--修改表的存储分配
ALTER TABLE tablename
PCTFREE 30
PCTUSED 50
STORAGE(NEXT 500K
MINEXTENTS 2
MAXEXTENTS 100);
ALTER TABLE tablename
ALLOCATE EXTENT(SIZE 500K
DATAFILE '/DISK3/DATA01.DBF');
--把表移到另一个表空间
ALTER TABLE TABLENAME MOVE TABLESPACE TABLESPACENAME;
--回收空闲的空间(回收到High-water mark)
全部回收需要TRUNCATE TABLE tablename
ALTER TABLE tablename
DEALLOCATE UNUSED;
--删除表(连同所用constraint)
DROP TABLE tablename
CASCADE CONSTRAINTS;
--给表增加列
ALTER TABLE TABLENAME
ADD COLUMN COLUTYPE DEFAULT(value) NOT NULL;
--删除表中的列
ALTER TABLE tablename
DROP COLUMN columnname;
ALTER TABLE tablename
DROP COLUMN columnname
CASCADE CONSTRAINTS CHECKPOINT 1000;
--标记列不可用
ALTER TABLE tablename
SET UNUSED COLUMN columnname
CASCADE CONSTRAINTS;
--删除标记为不可用的列
ALTER TABLE tablename
DROP UNUSED COLUMNS CHECKPOINT 1000;
--继续删除列选项
ALTER TABLE tablename
DROP COLUMNS CONTINUE CHECKPOINT 1000;
--把表放到BUFFER_POOL中去
ALTER TABLE tablename
STORAGE (BUFFER_POOL RECYCLE);
--避免动态分配EXTENT
ALTER TABLE tablename ALLOCATE EXTENT;
--把表放到CACHE中去
ALTER TABLE tablename ALLOCATE CACHE/NOCACHE;
4、索引管理
--创建索引
CREATE INDEX indexname ON TABLENAME(COLUMNNAME);
CREATE INDEX indexname ON TABLENAME(COLUMNNAME) TABLESPACE TABLESPACENAME;
--重新建立索引
ALTER INDEX indexname REBUILD TABLESPACE TABLESPACE;
--索引分配参数
ALTER INDEX indexname
STORAGE(NEXT 400K
MAXEXTENTS 100);
--释放索引空间
ALTER INDEX indexname
ALLOCATE EXTENT (SIZE 200K
DATAFILE '/DISK6/indx01.dbf');
ALTER INDEX indexname
DEALLOCATE UNUSED;
--重新整理索引表空间碎片
ALTER INDEX indexname COALESCE;
--删除索引
DROP INDEX indexname
--把索引放到BUFFER_POOL中
ALTER INDEX cust_name_idx
REBUILD
STORAGE (BUFFER_POOL KEEP);
5、约束管理
--建立主键
ALTER TABLE TABLENAME
ADD CONSTRAINT CONSTRAINTNAME PRIMARY KEY(COLUMN1,COLUMN2)
--使约束无效
ALTER TABLE TABLENAME ENABLE NOVALIDATE CONSTRAINT constraintname;
ALTER TABLE TABLENAME ENABLE VALIDATE CONSTRAINT constraintname;
--删除约束
ALTER TABLE tablename DROP CONSTRAINT constraintname;
DROP TABLE tablename CASCADE CONSTRAINTS;(删除表后将所用的外键删除)
--给列增加缺省值
ALTER TABLE TABLENAME
MODIFY columnname DEFAULT(value) NOT NULL;
--给表增加外键
ALTER TABLE tablename
ADD CONSTRAINT constraintname
FOREIGN KEY(column) REFERENCES table1name(column1);
6、安全策略
--加密传输
把客户端环境变量ora_encrypt_login设为true
把服务器端参数dblink_encypt_login设为true
--数据库管理员安全策略
a、建库后立即修改SYS/SYSTEM的口令(9.2后必须修改其口令)
b、只有数据库管理员才能以SYSDBA登录系统
c、建立不同角色的管理员,分配不同的权限
比如:对象创建于维护
数据库的调整与维护
创建用户分配角色
启动关闭
恢复备份
--应用开发者的安全策略
a、开发者的特权只能在测试开发的数据库中赋予权限
b、自由开发者、受控开发者
自由开发者:create tableindexprocedurepackage
受控开发者:没有以上权限
7、日志文件管理
--切换日志文件
ALTER SYSTEM SWITCH LOGFILE;
--增加日志文件
ALTER DATABASE ADD LOGFILE
('/DISK3/log3a.rdo',
'/DISK4/log3b.rdo') size 1M;
--增加日志成员
ALTER DATABASE ADD LOGFILE MEMBER
'/DISK4/log1b.rdo' TO GROUP 1
'/DISK4/log2b.rdo' TO GROUP 2;
--删除日志文件
ALTER DATABASE DROP LOGFILE GROUP 3;
--删除日志成员
ALTER DATABASE DROP LOGFILE MEMBER '/DISK4/log2b.dbf';
--清除日志文件内容
ALTER DATABASE CLEAR LOGFILE '/DISK3/log2a.rdo';[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/503782/viewspace-983261/,如需转载,请注明出处,否则将追究法律责任。