oracle常识-常用的知识

用户SYS AS SYSDBA登陆
　　1、系统用户：
查询用户
　SELECT USERNAME FROM DBA_USERS;
创建用户
CREATE USER SCOTT IDENTIFIED BY TIGER
删除用户
DROP USER USER_NAME CASCADE;
　　改口令
　　　ALTER USER SCOTT IDENTIFIED BY TIGER;
解锁
ALTER USER SCOTT SCCOUNT UNLOCK。
授权
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO SCOTT
　　2、表空间：
查询表空间　　　
SELECT * FROM DBA_DATA_FILES;
　　　SELECT * FROM DBA_TABLESPACES;//表空间

　　　SELECT TABLESPACE_NAME,SUM(BYTES), SUM(BLOCKS)
　　　　FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;//空闲表空间

　　　SELECT * FROM DBA_DATA_FILES
　　　　WHERE TABLESPACE_NAME='RBS';//表空间对应的数据文件

　　　SELECT * FROM DBA_SEGMENTS
　　　　WHERE TABLESPACE_NAME='INDEXS';

删除表空间

DROP TABLESPACE DATA01 INCLUDING CONTENTS AND DATAFILES;

建立表空间

1．建立表空间

CREATE TABLESPACE DATA01
DATAFILE '/ORACLE/ORADATA/DB/DATA01.DBF' SIZE 500M
UNIFORM. SIZE 128K; #指定区尺寸为128K,如不指定，区尺寸默认为64K

2．建立UNDO表空间

CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE '/ORACLE/ORADATA/DB/UNDOTBS02.DBF' SIZE 50M

#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到

该表空间:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02;

3．建立临时表空间

CREATE TEMPORARY TABLESPACE TEMP_DATA
TEMPFILE '/ORACLE/ORADATA/DB/TEMP_DATA.DBF' SIZE 50M

改变表空间状态

1.使表空间脱机

ALTER TABLESPACE GAME OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项

ALTER TABLESPACE GAME OFFLINE FOR RECOVER;

2.使表空间联机

ALTER TABLESPACE GAME ONLINE;


3.使数据文件脱机

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机

ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读

ALTER TABLESPACE GAME READ ONLY;

6.使表空间可读写

ALTER TABLESPACE GAME READ WRITE;

扩展表空间

首先查看表空间的名字和所属文件

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME,
ROUND(BYTES/(1024*1024),0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;

1.增加数据文件
ALTER TABLESPACE GAME
ADD DATAFILE '/ORACLE/ORADATA/DB/GAME02.DBF' SIZE 1000M;

2.手动增加数据文件尺寸
ALTER DATABASE DATAFILE '/ORACLE/ORADATA/DB/GAME.DBF'
RESIZE 4000M;

3.设定数据文件自动扩展
ALTER DATABASE DATAFILE '/ORACLE/ORADATA/DB/GAME.DBF
AUTOEXTEND ON NEXT 100M
MAXSIZE 10000M;

设定后查看表空间信息

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE
　　3、数据库对象：
　　　SELECT * FROM DBA_OBJECTS;
　　　CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、
　　　PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
　　4、表：
　　　SELECT * FROM DBA_TABLES;
　　　ANALYZE MY_TABLE COMPUTE STATISTICS;->DBA_TABLES后6列
　　　SELECT EXTENT_ID,BYTES FROM DBA_EXTENTS
　　　WHERE SEGMENT_NAME='CUSTOMERS' AND SEGMENT_TYPE='TABLE'
　　　ORDER BY EXTENT_ID;//表使用的EXTENT的信息。SEGMENT_TYPE='ROLLBACK'查看回滚段的空间分配信息
　　　列信息：
　　　　SELECT DISTINCT TABLE_NAME
　　　　FROM USER_TAB_COLUMNS
　　　　WHERE COLUMN_NAME='SO_TYPE_ID';
　　5、索引：　
　　　SELECT * FROM DBA_INDEXES;//索引，包括主键索引
　　　SELECT * FROM DBA_IND_COLUMNS;//索引列
　　　SELECT I.INDEX_NAME,I.UNIQUENESS,C.COLUMN_NAME
　　　　FROM USER_INDEXES I,USER_IND_COLUMNS C
　　　　　WHERE I.INDEX_NAME=C.INDEX_NAME
　　　　　AND I.TABLE_NAME ='ACC_NBR';//联接使用
　　6、序列：
　　　SELECT * FROM DBA_SEQUENCES;
　　7、视图：
　　　SELECT * FROM DBA_VIEWS;
　　　SELECT * FROM ALL_VIEWS;
　　TEXT 可用于查询视图生成的脚本
　　8、聚簇：
　　　SELECT * FROM DBA_CLUSTERS;
　　9、快照：
　　　SELECT * FROM DBA_SNAPSHOTS;
　　快照、分区应存在相应的表空间。
　　10、同义词：
　　　SELECT * FROM DBA_SYNONYMS
　　　　WHERE TABLE_OWNER='SPGROUP';
　　　　//IF OWNER IS PUBLIC,THEN THE SYNONYMS IS A PUBLIC SYNONYM.
　　　　　IF OWNER IS ONE OF USERS,THEN THE SYNONYMS IS A PRIVATE SYNONYM.
　　11、数据库链：
　　　SELECT * FROM DBA_DB_LINKS;
　　在SPBASE下建数据库链
　　　CREATE DATABASE LINK DBL_SPNEW
　　　CONNECT TO SPNEW IDENTIFIED BY SPNEW USING 'JHHX';
　　　INSERT INTO ACC_NBR@DBL_SPNEW
　　　SELECT * FROM ACC_NBR WHERE NXX_NBR='237' AND LINE_NBR='8888';
　　12、触发器：
　　　SELECT * FROM DBA_TRIGERS;
　　存储过程，函数从DBA_OBJECTS查找。
　　其文本：SELECT TEXT FROM USER_SOURCE WHERE NAME='BOOK_SP_EXAMPLE';
　　建立出错：SELECT * FROM USER_ERRORS;
　　ORACLE总是将存储过程，函数等软件放在SYSTEM表空间。
　　13、约束：
　　（1）约束是和表关联的，可在CREATE TABLE或ALTER TABLE TABLE_NAME ADD/DROP/MODIFY来建立、修改、删除约束。
　　可以临时禁止约束，如：
　　　ALTER TABLE BOOK_EXAMPLE
　　　DISABLE CONSTRAINT BOOK_EXAMPLE_1;
　　　ALTER TABLE BOOK_EXAMPLE
　　　ENABLE CONSTRAINT BOOK_EXAMPLE_1;
　　（2）主键和外键被称为表约束，而NOT NULL和UNIQUE之类的约束被称为列约束。通常将主键和外键作为单独的命名约束放在字段列表下面，而列约束可放在列定义的同一行，这样更具有可读性。
　　（3）列约束可从表定义看出，即DESCRIBE;表约束即主键和外键，可从DBA_CONSTRAINTS和DBA_CONS_COLUMNS 查。
　　　SELECT * FROM USER_CONSTRAINTS
　　　WHERE TABLE_NAME='BOOK_EXAMPLE';
　　　SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME
　　　　FROM USER_CONSTRAINTS
　　　　WHERE CONSTRAINT_TYPE='R'
　　　　ORDER BY TABLE_NAME;
　　（4）定义约束可以无名（系统自动生成约束名）和自己定义约束名（特别是主键、外键）
　　如：CREATE TABLE BOOK_EXAMPLE
　　　　(IDENTIFIER NUMBER NOT NULL);
　　　　CREATE TABLE BOOK_EXAMPLE
　　　　(IDENTIFIER NUMBER CONSTRANIT BOOK_EXAMPLE_1 NOT NULL);
　　14、常用表：

DBA_开头.....

DBA_USERS 数据库用户信息

DBA_SEGMENTS 表段信息

DBA_EXTENTS 数据区信息

DBA_OBJECTS 数据库对象信息

DBA_TABLESPACES 数据库表空间信息

DBA_DATA_FILES 数据文件设置信息

DBA_TEMP_FILES 临时数据文件信息

DBA_ROLLBACK_SEGS 回滚段信息

DBA_TS_QUOTAS 用户表空间配额信息

DBA_FREE_SPACE 数据库空闲空间信息

DBA_PROFILES 数据库用户资源限制信息

DBA_SYS_PRIVS 用户的系统权限信息

DBA_TAB_PRIVS 用户具有的对象权限信息

DBA_COL_PRIVS 用户具有的列对象权限信息

DBA_ROLE_PRIVS 用户具有的角色信息

DBA_AUDIT_TRAIL 审计跟踪记录信息

DBA_STMT_AUDIT_OPTS 审计设置信息

DBA_AUDIT_OBJECT 对象审计结果信息

DBA_AUDIT_SESSION 会话审计结果信息

DBA_INDEXES 用户模式的索引信息

USER_开头

USER_OBJECTS 用户对象信息

USER_SOURCE 数据库用户的所有资源对象信息

USER_SEGMENTS 用户的表段信息

USER_TABLES 用户的表对象信息

USER_TAB_COLUMNS 用户的表列信息

USER_CONSTRAINTS 用户的对象约束信息

USER_SYS_PRIVS 当前用户的系统权限信息

USER_TAB_PRIVS 当前用户的对象权限信息

USER_COL_PRIVS 当前用户的表列权限信息

USER_ROLE_PRIVS 当前用户的角色权限信息

USER_INDEXES 用户的索引信息

USER_IND_COLUMNS 用户的索引对应的表列信息

USER_CONS_COLUMNS 用户的约束对应的表列信息

USER_CLUSTERS 用户的所有簇信息

USER_CLU_COLUMNS 用户的簇所包含的内容信息

USER_CLUSTER_HASH_EXPRESSIONS 散列簇的信息

V$开头

V$DATABASE 数据库信息

V$DATAFILE 数据文件信息

V$CONTROLFILE 控制文件信息

V$LOGFILE 重做日志信息

V$INSTANCE 数据库实例信息

V$LOG 日志组信息

V$LOGHIST 日志历史信息

V$SGA 数据库SGA信息

V$PARAMETER 初始化参数信息

V$PROCESS 数据库服务器进程信息

V$BGPROCESS 数据库后台进程信息

V$CONTROLFILE_RECORD_SECTION 控制文件记载的各部分信息

V$THREAD 线程信息

V$DATAFILE_HEADER 数据文件头所记载的信息

V$ARCHIVED_LOG 归档日志信息

V$ARCHIVE_DEST 归档日志的设置信息

V$LOGMNR_CONTENTS 归档日志分析的DML DDL结果信息

V$LOGMNR_DICTIONARY 日志分析的字典文件信息

V$LOGMNR_LOGS 日志分析的日志列表信息

V$TABLESPACE 表空间信息

V$TEMPFILE 临时文件信息

V$FILESTAT 数据文件的I/O统计信息

V$UNDOSTAT UNDO数据信息

V$ROLLNAME 在线回滚段信息

V$SESSION 会话信息

V$TRANSACTION 事务信息

V$ROLLSTAT 回滚段统计信息

V$PWFILE_USERS 特权用户信息

V$SQLAREA 当前查询过的SQL语句访问过的资源及相关的信息

V$SQL 与V$SQLAREA基本相同的相关信息

V$SYSSTAT 数据库系统状态信息

ALL_开头

ALL_USERS 数据库所有用户的信息

ALL_OBJECTS 数据库所有的对象的信息

ALL_DEF_AUDIT_OPTS 所有默认的审计设置信息

ALL_TABLES 所有的表对象信息

ALL_INDEXES 所有的数据库对象索引的信息

SESSION_开头

SESSION_ROLES 会话的角色信息

SESSION_PRIVS 会话的权限信息

INDEX_开头

INDEX_STATS 索引的设置和存储信息

伪表

DUAL 系统伪列表信息ORACLE基于SQL几个常用的几个系统表

1、SYSOBJECTS

　　系统对象表。 保存当前数据库的对象，如约束、默认值、日志、规则、存储过程等

　　SYSOBJECTS 重要字段解释：

　　SYSOBJECTS （
　　NAME SYSNAME， ——OBJECT 名称
　　ID INT， ——OBJECT ID
　　XTYPE CHAR（2）， —— OBJECT 类型
　　TYPE CHAR（2）， —— OBJECT 类型（与XTYPE 似乎一模一样？ 有点郁闷…）
　　UID SMALLINT， —— OBJECT 所有者的ID
　　…… ——其他的字段不常用到。
　　）

　　注：需要解释的是 XTYPE 和TYPE 是一模一样的，他的数据为：
　　C = CHECK 约束
　　D = 默认值或 DEFAULT 约束
　　F = FOREIGN KEY 约束
　　FN = 标量函数
　　IF = 内嵌表函数
　　K = PRIMARY KEY 或 UNIQUE 约束
　　L = 日志
　　P = 存储过程
　　R = 规则
　　RF = 复制筛选存储过程
　　S = 系统表
　　TF = 表函数
　　TR = 触发器
　　U = 用户表
　　V = 视图
　　X = 扩展存储过程
　　该表中包含该数据库中的所有对象，如有那些表 存储过程 视图 等信息

　　2、SYSCOLUMNS 数据库字段表。 当前数据库的所有字段都保留在里面。

　　重要字段解释：
　　SYSCOLUMNS （
　　NAME SYSNAME， ——字段名称
　　ID INT， ——该字段所属的表的ID
　　XTYPE TINYINT， ——该字段类型，关联SYSTYPES表
　　LENGTH SMALLINT， ——该字段物理存储长度
　　……
　　）
　　比如要查询某一个张表的有那些字段和这些字段的长度

　　3、SYSUSERS
　　当前数据库的系统组，以及用户。
　　SYSUSERS（
　　UID SMALLINT， ——用户ID
　　NAME SMALLINT ， ——名称
　　UID VARBINARY（85） ， ——属于一个登陆
　　……
　　）
　　对数据库的用户进行管理

　　4、SYSDENPENDS
　　当前数据库的依赖关系。 比如我要修改某一张的结构时，怕修改后会影响到其它的视图 函数 存储过程 ，这是在修改之前可以查询一下。那些视图 函数 存储过程调用了这个表的

　　这样在修改后就可以修改的视图 函数 存储过程，

　　一般程序员用到的系统表，基本也就这几个 其他的特殊的系统表（主要都在MASTER 或者 TEMPDB ）里面了

一个有用的例子:(查询表的创建者)

SELECT OWNER FROM DBA_ALL_TABLES WHERE TABLE_NAME=UPPER('表名');
　　15、系统参数：
ORACLE数据库的系统参数都存储在数据库中，可以通过SQLPLUS，以用户SYSYTEM进行查询。
　　1.几个重要的表或者视图如下：
　　V$CONTROLFILE：控制文件的信息；
　　V$DATAFILE：数据文件的信息；
　　V$LOG：日志文件的信息；
　　V$PROCESS：处理器的信息；
　　V$SESSION：会话信息；
　　V$TRANSACTION：事务信息；
　　V$RESOURCE：资源信息；
　　V$SGA：系统全局区的信息。
　　上面的视图名中的‘V$’,只是视图名字中的字符。类似于上面的视图或表还有很多，位于：
　　$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL文件中。
这些视图或表可以在SQLPLUS中用SELECT语句进行查询。
　　2.数据字典视图
　　表和列
　　DBA_TABLES、ALL_TABLES和USER_TABLES显示了有关数据库表的一般信息。
　　DBA_TAB_COLUMNS、ALL_TAB_COLUMNS和USER_TAB_COLUMNS显示了每个数据库表的列的信息。
　　注意：DBA_OBJECTS、ALL_OBJECTS和USER_OBJECTS显示了模式对象的信息，包括表。
　　完整性约束
　　DBA_CONSTRAINTS、ALL_CONSTRAINTS和USER_CONSTRAINST显示有关约束的一般信息。
　　DBA_CONS_COL


附件和这个内容一样，可以下载看