oracle(创建数据库对象)

--创建数据库
--1.SYSDBA系统权限
startup：--启动数据库。
shutdown：--关闭数据库。
alter database[mount]|[open]|[backup]|[character set]：--修改数据库到加载状态、打开状态，备份数据库，修改数据库字符集。
create database：--创建数据库。
drop database：--删除数据库。
create spfile：--创建服务器初始化参数文件。
alter database archivelog：--修改数据库的归档模式。
alter database recover：--恢复数据库（完全恢复或者不完全恢复）。
--具有restricted session权限，可以在受限状态下连接数据库。
--2.SYSOPER系统权限
startup：--启动数据库。
shutdown：--关闭数据库。
create spfile：--创建服务器初始化参数文件。
alter database[mount]|[open]|[backup]：--修改数据库到加载状态、打开状态，备份数据库。
alter database archivelog：--修改数据库的归档模式。
alter database recover：--恢复数据库（只能进行完全恢复，不能进行不完全恢复）。
--具有restricted session权限，可以在受限状态下连接数据库。
--dba是一个角色
--%ORACLE_HOME%NETWORKADMINsqlnet.ora，
sqlnet.authentication_services=(nts)--不用密码认证
sqlnet.authentication_services=none--用密码认证
orapwd file=
    filename--在Windows系统中口令文件默认名称为PWD＜SID＞.ora，默认存放位置为%ORACLE_HOME%database
    [password=password]--设置SYS用户口令。如果没有设定，执行时系统提示设置SYS用户口令。
    [entries=numusers]--设定口令文件中可以保存的具有SYSDBA或SYSOPER权限的用户最大数量。
    [force=y|n]--明如果指定的口令文件已经存在，是否覆盖。值为Y，则覆盖
    [ignorecase=y|n]--指定存储在口令文件中的SYS用户口令是否对大小写敏感。
GRANT SYSDBA TO oe;--
select * from v$pwfile_users;
revoke sysdba from oe;--删除用户
--手动创建数据库
create database database_name
    [user sys identified by password]--设置sys用户口令
    [user system idnetified by password]--设置system用户口令
    [controlfile reuse]--按初始化参数文件中的control_files的值创建控制文件
    [maxinstances integer]--指定允许同时装载和打开的数据库实例个数
    [maxdatafiles integer]--设置控制文件中允许创建最多数据文件数量
    [maxloghistory integer]--可以自动归档的最大日志文件的数量，仅适用于RAC环境
    [maxlogfiles integer]--数据库中最大日志文件的数量
    [maxlogmembers integer]--设置每个日志文件组中最大成员日志文件数量 
    [archivelog|noarchivelog]--设置新建数据库处于归档模式
    [force logging]--数据库中所有的变化信息（除了临时表空间）都写入日志文件
    [character set charset]--设置数据库字符集
    [national character set charset]--设置国家字符集
    [set time_zone=time_zone_region]--设置数据库时区
    [set default smallfile|--设置默认创建的表空间为小文件表空间
        bigfile tablespace]--设置默认创建的表空间为大文件表空间
    --设置重做日志文件
    [logfile[[group integer]('filename1'[,…])[size integer[k|m|g|t][reuse]]],…]
    --设置 system 表空间
    [extent management local]--设置system表空间采用本地管理方式
    [datafile 'filename'
        [size integer[k|m][reuse]]
        [autoextend off
            [
                on
                [
                    next integer[k|m]
                    maxsize unlimited|[integer[k|m|g|t]]
                ]
            ]
        ],…]
    --设置 sysaux 表空间
    [sysaux datafile 'filename'[size integer[k|m|g|t][reuse]][autoextend off
    [on[next integer[k|m]maxsize[unlimited|integer[k|m|g|t]]]]],…]--为sysaux表空间创建数据文件
    --设置数据库默认表空间
    [default tablespace default_tablespace_name]--为数据库创建并指定默认表空间
    [datafile 'filename' [size integer[k|m][reuse]][autoextend off--为system表空间创建数据文件
    [on[next integer[k|m]maxsize unlimited|[integer[k|m|g|t]]]]],…]
    [extent management dictionary|[local[autoallocate|[uniform size
    integer[k|m|g|t]]]]]
    --设置数据库默认临时表空间
    [default temporary tablespace default_temp_tablespace_name]--为数据库创建并指定默认临时表空间
    [tempfile 'filename' [size integer[k|m|g|t][reuse]][autoextend off
    [on[next integer[k|m|g|t]maxsize unlimited|[integer[k|m|g|t]]]]],…]
    [extent management[dictionary|[local uniform size integer[k|m|g|t]]]]
    --设置数据库撤销表空间
    [undo tablespace undo_tablespace_name]--为数据库创建并指定默认撤销表空间
    [datafile 'filename' [size integer[k|m|g|t][resuse]][autoextend off
    [on[next integer[k|m|g|t]maxsize unlimited|[integer[k|m|g|t]]]]],…]
---------------------------------------------------------------------------------------------------------
--手动创建数据库实例
--1.确定数据库名称与数据库实例名称
--数据库名称（db_name），数据库实例名称（instance_name）。
--2.保证必需的环境变量的设置
--将ORACLE_HOMEbin路径信息写入PATH环境变量等。
--SET ORACLE_SID=MYNEWDB
--3.确定数据库管理员的认证方式
--操作系统认证方式，口令文件认证方式
orapwd--创建口令文件
file=d:appadministratorproduct11.2.0dbhome_1databasepwdmynewdb.ora
password=mynewdb entries=10
--4.创建文本初始化参数文件
%ORACLE_HOME%databaseinit＜SID＞.ora
--其内容为：
#设置全局数据库名
db_domain=''
db_name='MYNEWDB'
#设置快速恢复区
db_recovery_file_dest='d:appAdministratorflash_recovery_area'
db_recovery_file_dest_size=2G
#设置控制文件
control_files=(
'D:appAdministratororadatamynewdbcontrol01.ctl',
'D:appAdministratororadatamynewdbcontrol02.ctl',
'D:appAdministratororadatamynewdbcontrol03.ctl')
#设置标准数据块大小
db_block_size=8192
#设置内存管理
memory_target=1G
#设置最大进程数
processes=150
#设置撤销空间管理
undo_management=AUTO
undo_tablespace=UNDOTBS
#设置兼容性
compatible='11.2.0'
#设置共享服务器
dispatchers='(PROTOCOL=TCP)(SERVICE=MYNEWDBXDB)'
#设置安全与审计
audit_file_dest='D:appAdministratoradminmynewdbadump'
audit_trail='db'
#口令文件管理
remote_login_passwordfile=EXCLUSIVE
#设置游标
open_cursors=300
#设置诊断与跟踪信息
diagnostic_dest='D:appAdministrator'
#设置作业队列
job_queue_processes=10
--注意　初始化参数文件中所涉及的目录需要预先手动创建。
--5.创建数据库实例
oradim-new-sid sid--表示新建一个数据库实例。SID，设置新建数据库实例的名称
    [-syspwd password]--设置SYS用户口令。
    [-startmode manual|auto]--设置数据库实例的启动模式。如果设置为AUTO，
                            --创建实例后将启动实例、加载数据库，由于此时数据库还不存在，
                            --将导致错误。因此，应设置此参数为MANUAL。
    [
        [-pfile pfile]--指定数据库实例启动时使用文本初始化参数文件。
            |spfile--指定数据库启动时使用服务器初始化参数文件。
    ]--
    [-shutmode normal|immediate|abort]--设置数据库实例关闭方式。
--例
c:＞oradim-new-sid mynewdb-startmode manual
--6.连接到数据库实例
C:＞SQLPLUS/NOLOG
SQL＞CONNECT sys/mynewdb AS SYSDBA
--其中，mynewdb是保存在口令文件中的SYS用户的口令。
--7.创建服务器初始化参数文件
SQL＞CREATE SPFILE FROM PFILE;--为MYNEWDB数据库创建服务器初始化参数文件
                              --（建议用户使用数据库服务器初始化参数文件(spfile)，
                              --而不是使用文本初始化参数文件（pfile））
--8.启动数据库实例
SQL>startup nomount
--9.执行CREATE DATABASE语句
--10.创建附加的表空间
--11.运行脚本创建数据字典视图
--在手动创建数据库时，需要手动运行脚本创建数据字典视图与同义词、创建PL/SQL包，
--以及提供对SQL*Plus工具的支持。
SQL＞CONNECT sys/mynewdb AS SYSDBA
SQL＞@D:appAdministratorproduct11.2.0dbhome_1RDBMSADMINcatalog.sql;
SQL＞@D:appAdministratorproduct11.2.0dbhome_1RDBMSADMINcatproc.sql;
SQL＞CONNECT system/mynewdb
SQL＞@D:appAdministratorproduct11.2.0dbhome_1sqlplusadminpupbld.sql
--12.运行脚本安装其他组件
--13.备份数据库
--14.修改数据库实例的启动模式
oradim-edit-sid sid
    [-startmode auto|manual]
    [-srvcstart system|demand]
    [-pfile file|-spfile]
    [-shutmode normal|immediate|abort]
    [-syspwd password]
-------------------------------------------------------------------------------------------------------------
--创建表空间
create
    [bigfile|smallfile]--指定要创建大文件表空间，还是小文件表空间。如不指定，则由数据库属性DEFAULT_TBS_TYPE指定，默认为SMALLFILE。
    [temporary|undo]tablespace tbs_name--指定要创建的表空间类型，默认为永久表空间
    datafile|tempfile path/filename size integer[k|m][reuse]--指定表空间的数据文件的位置和名称。
    [
        autoextend[off|on]next integer[k|m]--指定数据文件是否可以自动扩展。如果可以自动扩展，需要设置NEXT值指明每次扩展的大小，
        maxsize[unlimited|integer[k|m]]    --设置MAXSIZE值指明文件的最终大小。默认值为OFF，不可扩展。
    ]
    [tablespace group tablespace_group_name]--只用于临时表空间，指定该临时表空间加入某个临时表空间组。
                                            --如果指定的临时表空间组不存在，系统会自动创建该临时表空间组。
    [extent management dictionary|local]--指定表空间的管理方式，默认为LOCAL，即本地管理。
    [autoallocate|uniform size integer[k|m]]--本地管理表空间区采用自动分配方式，区的最小值为64KB。自动分配是本地管理表空间中区的默认分配方式
    [segment space management auto|manual]--本地管理表空间中段的管理方式，默认为AUTO。
    [retention guarantee|noguarantee]--用于撤销表空间，指明事务提交后，回退信息在回退段中保留时间仍然为UNDO_RETENTION，在这段时间内回退信息不会被覆盖。
    [encryption[using encrypt_algorithm]--指定表空间的加密方法。
    [identified by password][[no]salt]]--
    [minimum extent integer[k|m]]--表空间中区的最小值。这是为了与早期版本的兼容而保留的子句。
    [
        default storage(
        [initial integer[k|m]]--为第一个区的大小
        [next integer[k|m]]--第二个区的大小
        [pctincrease integer]--第三个区和以后扩展的区的增长百分率,
        [minextents integer]--为段分配的区的最小数目
        [maxextents integer|unlimited])--为段分配的区的最大数目。这是为了与早期版本的兼容而保留的子句。
    ]
    [online|offline]--新建表空间处于脱机状态还是联机状态。
    [logging|--数据库对象的创建以及数据的DML操作信息都写入重做日志文件。
        nologging]--数据库对象的创建以及数据的DML操作信息都写入重做日志文件，但是数据的加载操作不写入重做日志文件。
    [force logging]--表空间中所有对象发生的变化信息（临时段的变化信息除外）都将被写入重做日志文件中，忽略NOLOGGING子句的作用。
    [blocksize integer]--表空间所基于的数据块大小。
    [flashback on|off]--是否支持表空间的闪回操作
--说明BLOCKSIZE：表空间所基于的数据块大小。若为非标准块表空间，需要配置初始化参数DB_nK_CACHE_SIZE参数，其中nKB就是BLOCKSIZE的值。
--默认为标准块表空间，BLOCKSIZE值为初始化参数DB_BLOCK_SIZE的值。
--------------------------------------------------------------------------------------------------------
--创建数据文件
create tablespace--创建表空间同时创建数据文件
create temporary tablespace --创建临时表空间的同时创建临时数据文件 
alter tablespace...add datafile --向表空间添加数据文件 
alter tablespace...add tempfile--向临时表空间添加数据文件 
create database --创建数据库时创建数据文件 
alter database...create datefile--数据氈恢复操作时，新建一个数据文件取代出现故障的数据文件 
--例
alter tablespace users add datafile 'd:appadministratororadataorclusers02.dbf' size 10m;
--修改数据文件大小
select file_name,autoextensible from dba_data_files;--查看当前数据库所有数据文件的扩展方式。
--例
alter database datafile 'd:appadministratororadataorclusers04.dbf'size 10m autoextend on next 512k maxsize 100m;
--改变数据文件的可用性
--要进行数据文件的脱机备份时，需要先将数据文件脱机。
--需要重命名数据文件或改变数据文件的位置时，需要先将数据文件脱机。
--如果Oracle在写入某个数据文件时发生错误，系统会自动将该数据文件设置为脱机状态，并且记录在报警文件中。排除故障后，需要以手动方式重新将该数据文件恢复为联机状态。
--数据文件丢失或损坏，需要在启动数据库之前将数据文件脱机。
--归档模式下：
alter database datafile…online|offline--设置永久性数据文件的脱机与联机。
alter database tempfile…online|offline--设置临时数据文件的脱机与联机。
--将ORCL数据库设置为归档模式。
shutdown immediate--数据库己经关闭，己经卸载数据库，例程己关闭
startup mount--例程己启动
--例
alter database datafile
'd:appadministratororadataorclusers01.dbf'offline;--脱机
recover datafile
'd:appadministratororadataorclusers01.dbf';--完成介质恢复。
alter database datafile
'd:appadministratororadataorclusers01.dbf'online;--联机
--非归档模式下数据文件的脱机
--由于数据库处于非归档模式，数据文件脱机后，会导致信息的丢失，从而使该数据文件无法再联机，
--即无法使用了。因此，在非归档模式下，通常不能将
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
alter database datafile 'd:appadministratorproduct11.2.0dbhome_1databaseusers3.dbf' offline for drop;
select file_name,from dba_data_files where tablespace_name = 'users';
--注意　使用该语句只是将数据文件脱机，数据文件不可用，并没有从数据字典以及控制文件中删除该数据文件信息，也没有从磁盘上删除该数据文件。
--该文件随后可以随其所属表空间的删除而删除。
alter tablespace users datafile offline;
select name,status from v$datafile where ts# = (select ts# from  v$tablespace where name='users');
--查看users表空间各个数据文件的状态
recover tablespce users;
--改变数据文件的名称与位置
alter tablespacle…rename datafile…to--改变同一个表空间中的数据文件的名称与位置。
alter database rename file…to--改变多个表空间中的数据文件的名称与位置
--例
select file_name,bytes from dba_data_files where tablespace_name = 'users';
alter tablespace users datafile offline;
host copy d:appadministratororadataorclusers02.dbf d:disk1users02.dbf
host copy d:appadministratororadataorclusers04.dbf d:disk1users04.dbf
alter tablespace users rename datafile 
'd:appadministratororadataorclusers02.dbf',
'd:appadministratororadataorclusers04.dbf' to
'd:disk1users02.dbf',
'd:disk1users02.dbf'
alter tablespace users online;
--修改多个表空间中数据文件的名称与位置
select file_name, bytes from dba_data_files where tablespace_name in('users','example');
shutdown immediate
startup mount
host copy d:appadministratororadataorclusers01.dbf d:disk2users01.dbf
host copy d:appadministratororadataorclexample01.dbf d:disk2example01.dbf
alter database rename file_name
alter database open;
--删除数据文件
alter tablespace…drop datafile--删除永久表空间中指定的空的数据文件。
alter tablespace…drop tempfile--删除临时表空间中指定的空的临时数据文件。
--例
alter tablespace users drop datafile
'd:appadministratorproduct11.2.0dbhome_1databaseusers03.dbf';
alter tablespace temp drop tempfile
'd:appadministratororadataorcltemp02.dbf';
alter database tempfile
'd:appadministratororadataorcltemp02.dbf'drop;

------------------------------------------------------------------------------------------------------------
--创建模式
--可以使用create schema语句在一个事务中同时为当前模式创建多个表、视图，并执行多个授权操作。
--在执行时，当所有的语句都成功完成时，系统提交该事务，当其中任何一个操作失败时，系统回滚整个事务。
create schema authorization schema--指定模式名称，必须为当前模式的名称。
[create_table_statement]--创建表的语句，结尾不能加分号等结束符号。 
[create_view_statement]--创建视图的语句，结尾不能加分号等结束符号。
[grant_statement];--为用户授予对象权限或系统权限，结尾不能加分号等结束符号。
--例：在system模式下创建一个表new_product、一个视图new_product_view，并将视图new_product_view的select权限授予用户scott
connect system/sfd12345@orcl 
create schema authorization system 
create table new_product(   color varchar2(10)primary key,   quantity number  ) 
create view new_product_view  
   as   
  select color,quantity from new_product 
     where color='red' grant select 
  on new_product_view to scott;
------------------------------------------------------------------------------------------------
--创建表
--标准表、临时表、索引表、分区表、聚簇表，外部表 
create[global temporary]table[schema.]table
(
    column1 datatype[default|：=expr]--列的数据类型，设置列的默认值。
    [encrypt encryption_spec]--对列数据进行加密，设定采用的加密算法。
    [column_level_constraint]--列级约束
    [,column2[datatype]
       [generated always]as(column_expression)--创建虚拟列。
       [virtual]
       [column_level_constraint]--表级约束
    ]
    …
    [，table_level_constraint]
)
[on commit delete|preserve rows]--指定临时表的类型
[organization
    [heap[segment_attributes_clause]table_compression]--创建标准表，以堆结构组织数据
    [index[segment_attributes_clause]index_org_table_clause]--创建索引表，以平衡树（b-tree）结构组织数据。
    [external external_table_clause]--创建外部表，数据只读
]
[partition by partition_clause]--创建分区表
[cluster cluster(column)]--创建聚簇表
[tablespace tablespace]--设置表的存储位置
[logging|nologging]--指明表的创建过程是否写入重做日志文件。默认为logging。
[cache|nocache]--指明表中数据是否缓存。默认为cache
[compress|nocompress]--指明表中数据是否压缩。默认为nocompress。
[
    [noparallel]|[parallel integer]--指明是否允许并行创建表以及随后对表中数据进行并行操作。默认为noparallel。
]
[pctfree integer]--指定块中必须保留的最小空闲空间比例，用于更新数据块内存在的数据行，而其余80%是用于插入新的数据行
[pctused integer]--指定可以向块中插入数据时块已使用的最大空间比例。低于这个值才会插入数据，默认是10%
[initrans integer]--可以同时对数据块进行dml操作的事务的初始个数，用于保证数据块的最低并行操作水平。
[maxtrans integer];--可以同时对此数据块进行dml操作的事务的最多个数，用于保证数据块的最大并行操作水平
[storage--设置表的存储分配方式。若不指定，则继承表空间的存储参数设置。(如果表空间采用字典管理方式，则可以在STORAGE子句设置任何存储参)
(
    initial 65536 --指定为表段分配的第一个区的大小，以KB或MB为单位。默认值为5个数据块大小，最小值为2个数据块的大小。
    next 1048576 --指定为表段分配的第二个区的大小，以KB或MB为单位。默认值为5个数据块大小，最小值为1个数据块的大小。
    minextents 1 --指定允许为表段初始分配的区的最小数量。默认值为1，最小值为1。
    maxextents 2147483645--指定允许为表段分配的区的最大数量。默认为UNLIMITED（不受限制），最小值为1
    pctincrease 0 --指定从为表段分配的第三个区开始，区大小的增长百分比。当前区的大小为前一个区大小×（1+PCTINCREASE）。默认值为50，最小值为0。
    freelists 1 --
    freelist groups 1 --
    buffer_pool default --指定存放表数据的缓存池。表中数据存放在数据高速缓冲区中，数据高速缓冲区分为KEEP、RECYCLE和DEFAULT三个缓存池。如果没有指定BUFFER_POOL参数，BUFFER_POOL参数的默认值为DEFAULT，即数据存放在DEFAULT缓存池中。如果某个表的访问频率很高，可以将表的BUFFER_POOL参数设置为KEEP，这时该表的数据块将始终驻留在SGA中，以获得更快的访问速度。
    flash_cache default --
    cell_flash_cache default--
)]
----------------------------------------------------------------------------------------------------
--利用子查询创建表
create[global temporary]table table
(
    column1[column_level_constraint]
    [,column2[column_level_constraint]…]
    [,table_level_constraint]
)
[on commit delete|preserver rows]
[organization
    [heap[segment_attributes_clause]table_compression]
    [index[segment_attributes_clause]index_org_table_clause]
    [external external_table_clause]
]
[partition by partition_clause]
[other_parameter_list]
as
subquery;
---------------------------------------------------------------------------
--维护表
--修改表
alter table[schema.]table
    [alter_table_properties]--修改表属性参数
    [column_clauses]--修改表列
    [constraint_clauses]--修改表约束。
    [alter_table_partitioning]--修改分区表。
    [alter_external_table]--修改外部表。
    [move_table_clause]--修改表移动属性
--添加列
alter table table
add
(
    column1 datatype[default value][not null]
    [,column2 datatype generated always as(expression)[virtual]…]
);
--为表添加列时，如果表中已经有数据，那么新列不能用NOT NULL约束，
--除非为新列设置默认值。在默认情况下，新插入列的值为NULL。
--例
alter table aux_emp
add
(
    phone_number varchar2(20),
    hiredate date default sysdate not null，
    commission number(7,2)generated always as(sal*comm)
);
--删除列
alter table table
drop[column column]|[(column1,column2,…)]
    [cascade constraints];
--注意：如果被删除的列被其他表的外键引用，则必须使用CASCADE CONSTRAINTS选项，
--同时删除引用表的外键。否则，列的删除操作无法成功完成。
--将列标记为UNUSED状态，然后删除UNUSED状态列。
alter table table_name
    set unused[column column]|[(column1,column2,…)]
    [cascade constraints];
--修改列定义
alter table table
    modify column[datatype][default expression]
    [encrypt encryption_specification]|[decrypt];
--.修改列名
alter table table rename column oldname to newname;
--注意：依赖于该列的函数索引和检查约束继续保持有效状态。
--依赖于该列的视图、触发器、函数、存储过程和包变为失效状态。当这些对象下次被调用时，
--系统自动进行重新编译，使之变为有效状态；如果自动编译不成功，需要进行手动修改这些对象。
--例
alter table aux_emp rename column hiredate to hire_date;

--表结构重组
alter table aux_emp move
    storage
    (
        initial 20k
        next 40k 
        minextents 2
        maxextents 20
        pctincrease 0
    )
    tablespace tbs1;
    --1.手动分配表的存储空间
alter table tablename allocate extent[([size size][datafile 'filename'])];
--例
alter table aux_emp allocate extent (size 100k datafile'd:disk1tbs1_1.dbf');
--2.手动回收表的存储空间
dbms_space.unused_space('ehr','aux_emp','table',
total_blocks,total_bytes,unused_blocks,
unused_bytes,lastextf,last_extb,lastusedblock);
alter table 表名 deallocate  unused[keep size];--keep是还要保留的空间
--3.改变表的读写模式
alter table 表名 read only
--4.重命名表
alter table table_old_name rename to table_new_name;
--或
rename table_old_name to table_new_name;

--给表或列加注解
comment on table table is ;
comment on column table.column is ;
--删减表
delete --使用delete语句删除表中大量记录会占用较多的系统资源，包括cpu时间、重做日志存储空间、回退段存储空间等，
--同时还会触发表中定义的触发器。使用delete语句删除记录后，这部分记录占据的空间变成空闲空间，oracle并不会回收这部分存储空间，
--hwm的位置不会改变，也无法使用手动操作回收该空闲空间。
--因此，如果要删除表中所有记录，通常使用truncate或drop语句，而不使用delete语句
drop --用drop语句删除整个表，包括表结构，回收表的存储空间，然后使用create语句重新创建该表。
truncate --使用truncate语句可以快速、高效地删除表中所有的记录。truncate语句属于ddl语句，执行后立即提交，不会产生任何重做日志信息、
--回退信息，因此truncate操作不能被回滚。在执行truncate语句时不会影响与被删减表相关的数据库对象以及该表的授权，也不会触发表中定义的触发器。
--与delete操作不同，使用truncat语句删除表中数据的同时，hwm位置发生变化，分配给表的存储空间部分或全部被回收。
truncate table[schema.]table
[
    [drop[all]]|reuse storage--drop storage：默认选项，被删减表中只会保留由存储参数minextents指定的区的最小个数，其余的区都被回收
];
--drop table
drop table[schema.]table[cascade constraints][purge];
drop table aux_emp purge;--清空回收站
--分析表
dbms_stats.gather_table_stats--统计表的存储过程
(
    ownname varchar2,
    tabname varchar2,
    partname varchar2 default null,
    estimate_percent number default
    to_estimate_percent_type(get_param('estimate_percent')),
    block_sample boolean default false,
    method_opt varchar2 default get_param('method_opt'),
    degree number default to_degree_type(get_param('degree')),
    granularity varchar2 default get_param('granularity'),
    cascade boolean default to_cascade_type(get_param('cascade')),
    stattab varchar2 default null,
    statid varchar2 default null,
    statown varchar2 default null,
    no_invalidate boolean default to_no_invalidate_type(
    get_param('no_invalidate')),
    stattype varchar2 default 'data',
    force boolean default false
);
--可以查询user_tables、all_tables、dba_tables数据字典视图获取分析后的统计信息。
--可以使用analyze语句清除数据字典中表的统计信息，语法为：
analyze table[schema.]table delete[system]statistics;
analyze table employees delete statistics;--在删除表的统计信息的同时，该表上所有索引的统计信息也同时被删除了。
--验证表存储结构
analyze table[schema.]table validate structure--
[
    cascade--对表进行完整性验证的同时，也对定义在该表上的索引进行完整性验证。
    [
        complete--对表及其索引进行完全的存储结构完整性验证，默认值。
        [into[schema.]table]--将验证返回的、包含损坏数据块的记录的ROWID保存到用户自定义的表中。
        [offline--在对表进行存储结构完整性验证时不允许对表进行DML操作。默认值。
          |online]--如果对表进行存储结构完整性验证时，若有用户对该表进行DML操作，则必须使用ONLINE参数，
                  --以联机方式对表进行存储结构完整性验证。这样虽然会稍微影响DML执行性能，但为表的存储结构完整性验证提供了更大的灵活性。
    ]|fast--对表和索引进行完全的结构完整性验证需要占用大量的资源，可以使用FAST参数，采用优化验证算法的验证方法。
          --该验证方法可以快速实现对表的完整性验证，但不返回损坏数据块的详细信息。
];

@d:appadministratorproduct11.2.0dbhome_1rdbmsadminutlvalid.sql;--创建一个默认表INVALID_ROWS 
analyze table employees validate structure;--分析表
@D:appAdministratorproduct11.2.0dbhome_1RDBMSADMINUTLCHN1.SQL;--CHAINED_ROWS表
analyze table[schema.]table list chained rows[into[schema.table]];--分析行链接、行迁移情况
-------------------------------------------------------------------------------------------------------
--约束的定义方式
--1.列级约束
column_definition[constraint constraint_name]constraint_type…
--2.表级约束
[constraint constraint_name] constraint_type(column1[,column1,…])…
--约束的状态
enable--激活
disable--禁用
validate--对表中已有记录进行约束检查
novalidate--不对表中已有记录进行约束检查。
--创建与维护约束
--1.采用列级约束方式定义约束
constraint constraint--约束名称，如果用户没有为约束命名，Oracle将自动为约束命名，样式为SYS_Cn，其中n是数据库对象的唯一编号。
    [[not]null]|[unique]|[primary key]|[check(condition)]
    [
        references[schema.]object[(column)]
        [on delete cascade|[set null]]--定义级联删除的外键约束。
    ]
    [
        [not deferrable]|--约束不可延迟，默认值。
            [deferrable--约束可以延迟
                [initially immediate|--可延迟约束的立即检查。
                    deferred]--可延迟约束的延迟检查
            ]
    ]
    [enable|disable]
    [validate|novalidate]
    [using index index_clause]--设置在定义主键约束或唯一性约束时自动创建的唯一性索引的参数。
    [exceptions into[schema.]table]--将违反完整性约束的记录保存到指定的表中。
--2.采用表级约束方式定义约束
constraint constraint
    [unique(column1[,column2…])]
    [primary key(column1[,column2…])]
    [check(condition)]
    [foreign key(column1[,column2…])
        references[schema.]object[(column[,column2…])]
        [on delete cascade|[set null]]
    ]
    [
        [not deferrable]|--约束不可延迟，默认值。
            [deferrable--约束可以延迟
                [initially immediate]|--可延迟约束的立即检查。
                    deferred]--可延迟约束的延迟检查S
    ]
    [enable|disable][validate|novalidate]
    [using index index_clause]
    [exceptions into[schema.]table]
--添加、修改与删除约束
--1.添加约束alter table table
add[constraint constraint]constraint_type(column1[,column2,…])
[constraint_parameters];
alter table books modify title not null;--添加非空约束
--2.修改约束
ALTER TABLE table
MODIFY[CONSTRAINT constraint]
[PRIMARY KEY]|[UNIQUE(column1[,column2,…])]
[constraint_parameters];
--3.重命名约束
alter table table rename constraint old_name to new_name;
--4.删除约束
ALTER TABLE table
    DROP[CONSTRAINT constraint]
    [PRIMARY KEY]|[UNIQUE(column1[,column2,…])]
    [CASCADE][KEEP|DROP INDEX]
set constraints all deferred;--对所有可以延迟的约束进行延迟检查
set constraints all immediate;--恢复所有延迟检查的约束为立即检查
-----------------------------------------------------------------------------------
--创建其它表
--创建索引表
create table[schema.]table
(
    column1 datatype[default|:=expr][column_level_constraint]
    …
    [,table_level_constraint]
)
    organization index--创建索引表
    [pctthreshold percent]--指定保留在索引段的索引条目中的记录的百分比，超出该百分比的部分将保存到溢出数据段中，即一条记录分成两部分，
                          --一部分包含主键的列保存在原来的索引段中，而另一部分不包括主键的列保存到溢出数据段中。
    [including column]--该子句指定的列之前的所有列与主键列一起保存在索引段的索引条目中（数据百分比不能超过PCTTHRESHOLD设定的值），
                      --而之后的列都被存储在溢出数据段中。
    [overflow tablespace tablespace]--指定溢出数据段的存储表空间。
    --注意　创建索引表时，必须定义一个主键约束，否则将返回错误。
--创建分区表
create table table(…)
    partition by range(column1[,column2,…])--采用范围分区，分区列，可以是单列分区，也可以是多列分区。
    (
        partition partition1 values less than(literal|maxvalue)--分区设置（partition1就是个名字）。设置分区列值的上界。
        [tablespace tablespace]--设置分区所在的表空间
        [,partition partition2 values less than(literal|maxvalue)
        [tablespace tablespace],…]
    )
alter tablespace tbs1 offline;--将分区表的一分区设置为脱机状态，验证分区的可用性
--创建列表分区表
create table table(…)
    partition by list(column)--采用列表分区
    (
        partition partition1 values([literal|null]|[default])
            [tablespace tablespace]
            [,
                partition partition2 values([literal|null]|[default])
                [tablespace tablespace],…
            ]
    )
--创建散列分区表
create table table(…)
    partition by hash(column1[,column2,…])
    [(partition partition[tablespace tablespace][,…])]
    [partitions hash_partition_quantity store in(tablespace1[,…])]
    …
--------------------------------------------------------------------------------------
--PL/SQL
--一个完整的PL/SQL语句块由3个部分组成。
declare
--声明部分，定义变量、数据类型、异常、局部子程序等
begin
--执行部分，实现块的功能
exception
--异常处理部分，处理程序执行过程中产生的异常
end;
----------------------------------------------------------------------------------------
--控制结构
--1.IF语句
--利用IF语句实现选择控制的语法为：
if condition1 then statements1;
    [elsif condition2 then statements2;]
    …
    [else else_statements];
end if;
--2.CASE(等值比较)
case test_value
    when value1 then statements1;
    when value2 then statements2;
    ……
    when valuen then statementsn;
    [else else_statements;]
end case;
--2.CASE(多条件比较或搜索比较)
case
    when condition1 then statements1;
    when condition2 then statements2;
    ……
    when conditionn then statementsn;
    [else else_statements;]
end case;
--3.LOOP
loop
    sequence_of_statement;
    exit[when condition];
end loop;
--4.WHILE
while condition loop
    sequence_of_statement;
end loop;
--5.FOR
for loop_counter in[reverse]low_bound..high_bound loop
    sequence_of_statement;
end loop;
--例：
begin
    for v_counter in 1..50 loop
    insert into temp_table values(v_counter,'loop index');
    end loop;
end;
--6.GOTO
declare
    v_counter binary_integer:=1;
begin
    ＜＜label＞＞
    insert into temp_table values(v_counter,'loop index');
    v_counter:=v_counter+1;
    if v_counter ＜= 50 then
        goto label;
    end if;
end;
--游标
--1.显示游标
cursor cursor_name is select_statement;--（1）定义游标
open cursor_name;--（2）打开游标
fetch cursor_name into variable_list|record_variable;--（3）检索游标
close cursor_name;--（4）关闭游标
--例：
declare
    v_deptno hr.employees.department_id%type;
    cursor c_emp is select*from hr.employees
        where department_id=v_deptno;
    v_emp c_emp%rowtype;
begin
    v_deptno:=＆x;
    open c_emp;
    loop
        fetch c_emp into v_emp;
        exit when c_emp%notfound;
        dbms_output.put_line(v_emp.employee_id||''||v_deptno||''
        v_emp.first_name||''||v_emp.last_name||''||v_emp.salary);
    end loop;
    close c_emp;
end;
--显式游标的属性
--无论显式游标还是隐式游标，都具有%isopen、%found、%notfound、
--%rowcount、返回到目前为止从游标缓冲区检索的记录的个数。
--%bulk_rowcount数值型，用于取得FORALL语句执行批绑定操作时第i个元素所影响的行数。五个属性，
--利用游标属性可以判断当前游标状态。
--.参数化显式游标
cursor cursor_name(parameter1 datatype[,parameter2 datatype…])
    is select_statement;
--例如，查询并输出某个部门的员工信息。
declare
    cursor c_emp(p_deptno employees.department_id%type)is
    select*from hr.employees where department_id=p_deptno;
    v_emp c_emp%rowtype;
begin
    open c_emp(10);
    loop
        fetch c_emp into v_emp;
        exit when c_emp%notfound;
        dbms_output.put_line(v_emp.employee_id||v_emp.last_name);
    end loop;
    close c_emp;
    open c_emp(20);
    loop
        fetch c_emp into v_emp;
        exit when c_emp%notfound;
        dbms_output.put_line(v_emp.employee_id||v_emp.last_name);
    end loop;
    close c_emp;
end;
--1.利用简单循环检索游标的基本方式如下：
declare
    cursor cursor_name is select...;
begin
    open cursor_name;
    loop
        fetch...into...;
        exit when cursor_name%notfound;
        ...
    end loop;
    close cursor_name;
end;
--2.利用WHILE循环检索游标的基本方法如下：
declare
    cursor cursor_name is select...;
begin
    open cursor_name;
    fetch...into...;
    while cursor_name%found loop
        fetch...into...;
        …
    end loop;
    close cursor;
end;
--3.利用FOR循环检索游标
--利用FOR循环检索游标时，系统会自动打开、检索和关闭游标。
--用户只需要考虑如何处理从游标缓冲区中检索出来的数据。其方法如下：
declare
    cursor cursor_name is select...;
begin
    for loop_variable in cursor_name loop
    …
    end loop;
end;
--1.利用游标更新或删除数据的定义方法
cursor cursor_name is
    select select_list_item from table for update[of column_reference]
    [nowait];--若数据对象已经被其他会话加锁，则当前会话挂起等待（默认状态）若指定则返回ORACLE错误。
--2.利用游标更新或删除数据的方法
where current of cursor_name;
--例 
declare
    cursor c_emp is select*from hr.employees for update;
    v_increment number;
begin
    for v_emp in c_emp loop
        case v_emp.department_id
            when 10 then v_increment:=100;
            when 20 then v_increment:=140;
            when 30 then v_increment:=200;
            else v_increment:=250;
        end case;
        update hr.employees set salary=salary+v_increment
            where current of c_emp;
    end loop;
    commit;
end;
--隐式游标
--显式游标用于处理返回多行数据的SELECT查询，但所有的SQL语句都有一个执行的缓冲区，
--隐式游标就是指向该缓冲区的指针，由系统隐含地打开、处理和关闭。隐式游标又称为SQL游标。
--sql%isopen--对用户而言，该属性值始终为false，因为操作时系统自动打开，操作完后立即自动关闭
--sql%found--判断当前的操作是否会对数据库产生影响。如果有数据的插入、删除、修改或查询到数据，则返回true，否则返回false。
--sql%notfound--判断当前的操作是否对数据库产生影响。如果没有数据的插入、删除、修改或没有查询到数据，则返回true，否则返回false。
--sql%rowcount--数值型，返回当前操作所涉及的数据库中的行数。
--例 
begin
    update hr.employees set salary = salary + 100
        where employee_id=1000;
    if sql%notfound then
        insert into hr.employees(employee_id,first_name,last_name,
                                    email,hire_date,job_id,department_id)
        values(employees_seq.nextval,'san','zhang',
                            'zs@neusoft.edu.cn',sysdate,'ac_account',200);
    end if;
end;
--或
begin
    update hr.employees set salary = salary + 100
        where employee_id = 1000;
    if sql%rowcount = 0 then
        insert into hr.employees(employee_id,first_name,last_name,
                                    email,hire_date,job_id,department_id)
        values(employees_seq.nextval,'san','zhang',
                                    'zs@neusoft.edu.cn',sysdate,'ac_account',200);
    end if;
end;
--注意　当SELECT…INTO语句没有查询到任何数据时，会激发NO_DATA_FOUND异常。
--游标变量
--前面介绍的显式游标在定义时与特定的查询绑定，其结构是不变的，因此又称为静态游标。
--游标变量是一个指向多行查询结果集的指针，不与特定的查询绑定，
--因此具有非常大的灵活性，可以在打开游标变量时定义查询，可以返回不同结构的结果集。
type ref_cursor_type_name is ref cursor[return return_type]--（1）定义游标引用类型
--在Oracle 11g中，系统预定义了一个游标引用类型，称为SYS_REFCURSOR，可以直接使用它定义游标变量。
ref_cursor_type_name variable_name;--（2）声明游标变量
--例 
type emp_cursor_type is ref cursor return employees%rowtype;
type general_cursor_type is ref cursor;
v_emp emp_cursor_type;
v_general general_cursor_type;
my_cursor sys_refcursor;
OPEN cursor_variable FOR select_statement;--打开游标变量
loop--检索游标变量
    fetch cursor_variable into variable1,variable2,...;
    exit when cursor_variable%notfound;
    …
end loop;
CLOSE cursor_variable;--.关闭游标变量
--例
DECLARE
TYPE emp_cursor_type IS REF CURSOR RETURN hr.employees%ROWTYPE;
TYPE general_cursor_type IS REF CURSOR;
v_emp emp_cursor_type;
v_general general_cursor_type;
my_cursor SYS_REFCURSOR;
v_empinfo hr.employees%ROWTYPE;
v_deptinfo hr.departments%ROWTYPE;
begin
    open v_emp for select*from hr.employees;
    loop
        fetch v_emp into v_empinfo;
        exit when v_emp%notfound;
        dbms_output.put_line(v_empinfo.employee_id||''
            v_empinfo.first_name||''||v_empinfo.last_name||''
                v_empinfo.salary);
    end loop;
    close v_emp;
    open my_cursor for select*from hr.departments;
    loop
        fetch my_cursor into v_deptinfo;
        exit when my_cursor%notfound;
        dbms_output.put_line(v_deptinfo.department_id||''
            v_deptinfo.department_name);
    end loop;
    close my_cursor;
    --open v_general for select employee_id,first_name,last_name,
    --salary,department_id from hr.employees;
end;
-----------------------------------------------------------------------------------------------------
--预定义异常（略）
--非预定义异常
declare
    e_deptno_fk exception;
    pragma exception_init(e_deptno_fk,-2292);
begin
    …
exception
    …
end;
--用户自定义异常
e_exception exception;--定义异常
raise user_define_exception;--抛出异常
exception--异常的捕获及处理
    when exception1[or excetpion2...]then sequence_of_statements1;
    when exception3[or exception4...]then sequence_of_statements2;
    …
    when others then sequence_of_statementsn;
end;
------------------------------------------------------------------------------------------------------------
--创建存储过程
create[or replace]procedure procedure_name
    (parameter1_name[mode]datatype[default|:=value]
            [,parameter2_name[mode]datatype[default|:=value],...])
    as|is
    /*declarative section is here*/
begin
    /*executable section is here*/
exception
    /*exception section is here*/
end[procedure_name];
--其中mode有IN、OUT、IN OUT
--删除存储过程
drop procedure proc_show_emp;
---------------------------------------------------------------------------------------------------------
--创建函数
create[or replace]function function_name
        (parameter1_name[mode]datatype[default|:=value]
                [,parameter2_name[mode]datatype[default|:=value],...])
    return return_datatype
as|is
/*declarative section is here*/
begin
/*executable section is here*/
exception
/*exception section is here*/
end[function_name];
--例 
create or replace function func_dept_maxsal(
            p_deptno hr.employees.department_id%type)
    return hr.employees.salary%type
as
    v_maxsal hr.employees.salary%type;
begin
        select max(salary)into v_maxsal from hr.employees
            where department_id=p_deptno;
    return v_maxsal;
exception
    when no_data_found then
    dbms_output.put_line('the deptno is invalid!');
end func_dept_maxsal;
--------------------------------------------------------------------------------------------------
--创建包声明
create or replace package package_name
    is|as
    [pragma serially_resuable]
    type_definition|
    variable_declaration|
    exception_declaration
    cursor_declaration|
    procedure_declaration|
    function_declaration
end[package_name];
--.创建包体
create or replace package body package_name
is|as
    [pragma serially_resuable]
    type_definition|
    variable_declaration|
    exception_declaration
    cursor_declaration|
    procedure_definition|
    function_definition
end[package_name];
-------------------------------------------------------------------------
--创建用户
create user user identified--用于指明用户身份认证方式。
    [by password]--指定用户采用数据库身份认证
    [externally--指定用户采用外部身份认证，如操作系统认证或第三方认证。
        [as 'certificate_dn|--指定用户只采用SSL外部身份认证
        kerberos_principal_name']--指定用户只采用kerberos外部身份认证。
    ]
    [globally[as 'directory_dn']]--指定用户采用全局身份认证。
    [default tablespace tablespace]--用于设置用户的默认表空间。
    [temporary tablespace tablesapce|tablespace_group_name]--用于设置用户的临时表空间
    [quota n k|m|unlimited on tablespace]--用于指定用户在特定表空间上的配额
    [profile profile]--用于为用户指定概要文件。默认值为DEFAULT，采用系统默认的概要文件。
    [password expire]--用于设置用户口令的初始状态为过期，用户在首次登录数据库时必须修改口令。
    [account lock|unlock];--用于设置用户初始状态为锁定，默认为不锁定。
--注意　创建一个用户后，如果不为该用户授权，则该用户不能做任何操作，即便是连接数据库。
--只有给用户授予了CREATE SESSION系统权限后，用户才可以连接数据库。
--如果用户要通过OEM访问数据库，还需要具有SELECT ANY DICTIONARY系统权限。
orapwd file=filename[password=password][entries=numusers][force=y|n]
[ignorecase=y|n]
--例 
orapwd file=E:PWDorcl.ora password=www123 entries=5 force=n ignorecase=y;
show parameter remote_login_passwordfile;
alter system set remote_login_passwordfile = execlusive scope = spfile;
--查询某个用户具有的角色：
select grantee,granted_role from dba_role_privs where grantee='SYS';
--查询某个用户具有的系统权限：
select grantee,privilege from dba_sys_privs where grantee='SYS';
--查询某个角色中具有什么系统权限：
select privilege from role_sys_privs where role='RESOURCE';
--查询某个角色中包含有什么角色
select granted_role from role_role_privs where role='SYSDBA';
--管理用户、角色和权限的DBA视图：

----------------------------------------------------------------------------------
--创建重做日志文件
alter database add logfile
('d:disk4redo04a.log','d:disk5redo04b.log')size 10m;
alter database add logfile group 5
('d:disk4redo05a.log','d:disk5redo05b.log')size 10m;
--注意：重做日志文件最小为4MB
select * from v$logfile order by group#;
alter database add logfile member
'd:disk5redo01b.log'to group 1,
'd:disk5redo02b.log'to group 2,
'd:disk5redo03b.log'to group 3;
alter database rename file…to--修改重做日志文件的名称与位置
ALTER DATABASE DROP LOGFILE MEMBER--删除重做日志文件组及其成员
--归档重做日志管理
archive log list--查看当前数据库的归档设置情况
alter database archivelog--设置归档
alter database noarchivelog--设置非归档
--将数据库从非归档模式转换为归档模式的步骤为：
--1.关闭数据库
shutdown
--2.备份数据库。
--3.设置归档目的地
select name,value,from v$parameter where name = 'db_recovery_file_dest';
--4.将数据库启动到加载状态。
startup mount
--5.改变数据库归档模式。
alter database archivelog;
--6.打开数据库。
alter database open;
--7.关闭数据库。
shutdown
--8.备份数据库。