1.启动监听器:
lsnrctl start./stop
2.启动企业管理数据库控制台:
emctl start/stop dbconsole.
3.访问企业管理控制台:
http://localhost:1158/em
4.使用sqlplus访问ORACLE数据库:
sqlplus /nolog
connect / as sysdba
(sqlplus
user: sys as sysdba
password:)
5.查看某个表空间中哪些用户拥有哪些表:
select owner,table from dba_tables where table_name='EXAMPLE';
6.自动清除归档日志
/*autodelete.bat:
set oracle_sid=orcl
rman target / log c:/%date:~0,10%.log cmdfile c:/autodelete.rman
autodelete.rman
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
RUN {
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
backup archivelog all format 'f:/database_backup/%U' delete all input;
backup current controlfile format 'f:/database_backup/%U';
delete backup of archivelog all completed before 'sysdate-1';
delete obsolete;
}*/
7.DATABASE LINK的创建
CREATE DATABASE LINK <Link Name>
CONNECT TO<Schema Name>
IDENTIFIED BY<PWD>
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <Host Name>)(PORT = <port#>))
)
(CONNECT_DATA =
(SERVICE_NAME = <Database Name>)
)
)' ;
或者
CREATE DATABASE LINK <Link Name>
CONNECT TO<Schema Name>
IDENTIFIED BY<PWD>
USING connection_string;
8.NO_DATA_FOUND和%NOTFOUND的区别
一般初学者对Oracle的NO_DATA_FOUND和%NOTFOUND两个保留字的用法不够了解,主要是它们从意义上是有些类似。其实它们的用法是有区别的,小结如下:
SELECT . . . INTO 语句触发 NO_DATA_FOUND;
当一个显示光标的 where 子句未找到时 触发 %NOTFOUND;
当UPDATE或DELETE 语句的where 子句未找到时 触发 SQL%NOTFOUND;
在光标的提取(Fetch)循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件,不要用 NO_DATA_FOUND。
9.关于dba_constraints/dba_ind_columns和user_constraints/user_ind_columns表的数据存储及用户权限管理
用connect / as sysdba登录,select distinct owener from dba_constraints包含所有的拥有者(用户),而此时查询select distinct owner from user_constraints的结果中有且只有sys,因为此时你是用系统用户sys登录的,那么user_constraints表中的owner有且只有一个用户sys。
同理查询dba_ind_columns和user_ind_columns系统表中的table_owner也是一个道理。
用connct 用户/密码登录, select distinct owener from dba_constraints会提示“表或视图不存在”,除非系统用户为当前用户给了查询此系统表的权限!当你查询select distinct owner from user_constraints的结果中有且只有当前用户,不会有其他用户在里面,从中可以看出,ORACLE对用户的权限管理的是非常严格的。
同理查询dba_ind_columns和user_ind_columns系统表中的table_owner也是一个道理。
dba(user)_ind_columns和dba(user)_constraints两个表中存储的数据也是不一样的。
dba(user)_constraints中仅存储约束(主键等),而dba(user)_ind_columns中不仅存储索引(主键也是一种索引,叫唯一索引)还存储有“列是否非空”等约束信息。
10.系统表DBA_USERS
Column | Datatype | NULL | Description |
---|---|---|---|
USERNAME | VARCHAR2(30) | NOT NULL | Name of the user |
USER_ID | NUMBER | NOT NULL | ID number of the user |
PASSWORD | VARCHAR2(30) | Encrypted password | |
ACCOUNT_STATUS | VARCHAR2(32) | NOT NULL | Account status:
|
LOCK_DATE | DATE | Date the account was locked if account status was LOCKED | |
EXPIRY_DATE | DATE | Date of expiration of the account | |
DEFAULT_TABLESPACE | VARCHAR2(30) | NOT NULL | Default tablespace for data |
TEMPORARY_TABLESPACE | VARCHAR2(30) | NOT NULL | Name of the default tablespace for temporary tables or the name of a tablespace group |
CREATED | DATE | NOT NULL | User creation date |
PROFILE | VARCHAR2(30) | NOT NULL | User resource profile name |
INITIAL_RSRC_CONSUMER_GROUP | VARCHAR2(30) | Initial resource consumer group for the user | |
EXTERNAL_NAME | VARCHAR2(4000) | User external name |
start with ... connect by prior的用法举例
start with deptno = '0431000000'
connect by prior deptno = updeptno
2 0431043100 长春市 2 0431000000 0
3 0431043200 吉林市 2 0431000000 0
4 0431043300 延边朝鲜族自治州 2 0431000000 0
5 0431043400 四平市 2 0431000000 0
6 0431043500 通化市 2 0431000000 0
7 0431043600 白城市 2 0431000000 0
8 0431043700 辽源市 2 0431000000 0
9 0431043800 松原市 2 0431000000 0
10 0431043900 白山市 2 0431000000 0
11 0431043911 白山市下面的县市 3 0431043900 1
start with deptno = '0431000000'
connect by prior updeptno = deptno
2 0000000000 全国总机构 0 9999999999 0
存取路径分为全表扫描和索引扫描、快速索引全扫描