ORACLE问题，每天10问（一）
ORACLE问题，每天10问（一）
大家在应用ORACLE的时候可能会遇到很多看起来不难的问题, 特别对新手来说, 今天我简单把它总结一下, 发布给大家, 希望对大家有帮助! 和大家一起探讨, 共同进步!
对ORACLE高手来说是不用看的。
1. Oracle安装完成后的初始口令?
internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp
2. ORACLE9IAS WEB CACHE的初始默认用户和密码？
administrator/administrator
3. oracle 8.0.5怎幺创建数据库?
用orainst。如果有motif界面，可以用orainst /m
4. oracle 8.1.7怎幺创建数据库?
dbassist
5. oracle 9i 怎幺创建数据库?
dbca
6. oracle中的裸设备指的是什幺?
裸设备就是绕过文件系统直接访问的储存空间
7. oracle如何区分 64-bit/32bit 版本？？？
$ sqlplus '/ AS SYSDBA'
SQL*Plus: Release 9.0.1.0.0 - Production on Mon Jul 14 17:01:09 2003
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
PL/SQL Release 9.0.1.0.0 - Production
CORE 9.0.1.0.0 Production
TNS for Solaris: Version 9.0.1.0.0 - Production
NLSRTL Version 9.0.1.0.0 - Production
SQL>
8. SVRMGR什幺意思？
svrmgrl，Server Manager.
9i下没有，已经改为用SQLPLUS了
sqlplus /nolog
变为归档日志型的
9. 请问如何分辨某个用户是从哪台机器登陆ORACLE的?
SELECT machine , terminal FROM V$SESSION;
10. 用什幺语句查询字段呢？
desc table_name 可以查询表的结构
select field_name,... from ... 可以查询字段的值
select * from all_tables where table_name like '%'
select * from all_tab_columns where table_name='??'
查看ORACLE数据库信息的一些SQL
1、查看表空间的名称及大小
set linesize 140;
set pages 200;
column tablespace_name format a30;
select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;
select tablespace_name,initial_extent,next_extent,contents,logging,extent_management,allocation_type
from dba_tablespaces
order by tablespace_name;
2、查看表空间物理文件的名称及大小
column db_block_size new_value blksz noprint
select value db_block_size from v$parameter where name='db_block_size';
column tablespace_name format a16;
column file_name format a60;
set linesize 160;
select file_name,round(bytes/(1024*1024),0) total_space,autoextensible,increment_by*&blksz/(1024*1024) as incement,maxbytes/(1024*1024) as maxsize from dba_data_files order by tablespace_name;
3、查看回滚段名称及大小
COLUMN roll_name FORMAT a13 HEADING 'Rollback Name'
COLUMN tablespace FORMAT a11 HEADING 'Tablspace'
COLUMN in_extents FORMAT a20 HEADING 'Init/Next Extents'
COLUMN m_extents FORMAT a10 HEADING 'Min/Max Extents'
COLUMN status FORMAT a8 HEADING 'Status'
COLUMN wraps FORMAT 999 HEADING 'Wraps'
COLUMN shrinks FORMAT 999 HEADING 'Shrinks'
COLUMN opt FORMAT 999,999,999 HEADING 'Opt. Size'
COLUMN bytes FORMAT 999,999,999 HEADING 'Bytes'
COLUMN extents FORMAT 999 HEADING 'Extents'
SELECT
a.owner || '.' || a.segment_name roll_name
, a.tablespace_name tablespace
, TO_CHAR(a.initial_extent) || ' / ' ||
TO_CHAR(a.next_extent) in_extents
, TO_CHAR(a.min_extents) || ' / ' ||
TO_CHAR(a.max_extents) m_extents
, a.status status
, b.bytes bytes
, b.extents extents
, d.shrinks shrinks
, d.wraps wraps
, d.optsize opt
FROM
dba_rollback_segs a
, dba_segments b
, v$rollname c
, v$rollstat d
WHERE
a.segment_name = b.segment_name
AND a.segment_name = c.name (+)
AND c.usn = d.usn (+)
ORDER BY a.segment_name;
4、查看控制文件
select name from v$controlfile;
5、查看日志文件
select member from v$logfile;
6、查看表空间的使用情况
select * from( select sum(bytes)/(1024*1024) as "free_space(M)",tablespace_name
from dba_free_space
group by tablespace_name) order by "free_space(M)";
7、查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects
group by owner,object_type,status;
8、查看数据库的版本
select * from v$version;
9、查看数据库的创建日期和归档方式
select created,log_mode,log_mode from v$database;
10、查看临时数据库文件
select STATUS, ENABLED, NAME from v$tempfile;
