oracle资料.docx

datafile E:oracleproduct10.2.0oradataorcldata1.dbf2授予用户kathy在表空间users上10m的限额。3cip:3导入和导出3检测一个数据表中有那些列：4分割数据表：4用一下语句清空oracle中的回收站：4向Oracle中加入日期：4,+ edit3.Text + , +4查看存储过程：5找一个表的外键：5删除所有表5查找重复的项7条件增量导出7创建表空间以及分区：75 分区表的扩容：8查看有那些表空间：8查找当前用户使用的表以及表空间信息8查看用户和表空间9查看表空间使用率9删除表空间9查看表空间有那些物理文件9Oracle设置成单机版：9如何让数据库运行在归档模式9如何让数据库运行在非归档模式10建立表、自动序列号10Hcip导入：10数据库备份11关闭实例后，如何登陆sqlplus，再启动实例11数据泵导出：12数据泵导入：13授权用户查询所有表14创建数据库连接14删除数据库连接14查询数据库连接14创建表空间 create tablespace data_test datafile G:oracleproduct10.2.0oradataorcldata1.dbf size 2000m;创建用户：create user hill identified by passhill default tablespace data_test;赋予权限： grant connect,dba to hill;grant create any table to hill;切换用户：connect hill/passhill;删除用户：drop user hill cascade;cascade 参数表明删除hill用户的所有对象；删除表空间：drop tablespace data_test ; /datafile E:oracleproduct10.2.0oradataorcldata1.dbf修改表空间： ALTER tablespace data_test add DATAFILE G:oracleproduct10.2.0oradataorcldata2.dbf size 4000m;create tablespace g_2000q4 datafile /home/oradata/oradata/test/g_2000q4.dbf size 50M default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1);授予用户kathy在表空间users上10m的限额。SQL alter user kathy quota 10m on users;select * from dba_ts_quotas where username=KATHY;授予用户kathy在表空间users上无限制的空间。1表示无限的使用空间alter user kathy quota unlimited on users;查询当前用户的表名：select table_name from user_tables;导入和导出：$exp 1、导出用户的结构和数据(假设用户名/密码 tempout/tempout)。 在dos窗口中使用exp命令 例如：c:$exp VOD_txs/VODtxspasshcip grants=y file=c:VOD20111109.dmp 其中的grants参数用来吧用户的权限也到处。 2、导入用户： 如果你要导入的数据库中没有tempout这个用户，你必须先创建改用户并授予connect和resource权限(最基本的权限)。 然后 c:imp tempout/tempoutserver ignore=y full=y grants=y file=d:tempout.dmp 如果数据库内已经有改用户了并且你想增量倒入数据，那就这样 c:$imp tempout/tempout ignore=y full=y file=d:tempout.dmp $imp chcip/chcip ignore=y grants=y file= D:dbexpmam20120222.dmp tables=(MCM_CONTROLCLASS,MCM_subclass,mcm_navcontrol,mcm_navclass) 这样会吧你的dmp文件你的数据都倒入到tempout用户下并且不影响tempout用户原来已有的数据。检测一个数据表中有那些列：desc test;desc tab_name select *from tab_name where rownum=1分割数据表：create table tableb asselect t.xx,t.yy,t.zz from tableat用一下语句清空oracle中的回收站：purge recyclebin;你需要创建这些删除语句，通过oracle的数据字典找到该用户下的所有表、视图等对象，拼接成语句。select drop table | table_name |;|chr(13)|chr(10) from user_tables; -delete tablesselect drop view | view_name|;|chr(13)|chr(10) from user_views; -delete viewsselect drop sequence | sequence_name|;|chr(13)|chr(10) from user_sequences;-delete seqsselect drop function | object_name|;|chr(13)|chr(10) from user_objects where object_type=FUNCTION;-delete functionsselect drop procedure | object_name|;|chr(13)|chr(10) from user_objects where object_type=PROCEDURE;-DELETE PROCEDUREselect drop package | object_name|;|chr(13)|chr(10) from user_objects where object_type=PACKAGE;-delete pags向Oracle中加入日期：procedure TDlgMYSQL.Button8Click(Sender: TObject);var sql:string; ADateAndTime: TDateTime;begin ADateAndTime := StrToDateTime(Edit4.Text); sql := insert into t0 values (+ edit2.Text + ,+ edit3.Text + , + TO_DATE(+ quotedstr(datetostr(ADateAndTime)+,+quotedstr(yyyy-mm-dd) +); showmessage(sql); oraquery1.SQL.Add(sql); oraquery1.ExecSQL; oraquery1.SQL.Clear ; oraquery1.Close; / showmessage(datetostr(ADateAndTime);end;查看存储过程：select text from user_source;找一个表的外键：删除所有表的存储过程create or replace procedure THM_DelAllTAbles isTYPE user_cur IS REF CURSOR;cur1 user_cur;cur2 user_cur;strTable Varchar2(256);strsql Varchar2(1024);s varchar2(256);begin - open cur1 for select drop table | table_name | |chr(13)|chr(10) from user_tables - where table_name not like THM_%; -删除所有表的外键 open cur1 for select table_name from user_tables where table_name not like THM_%; loop fetch cur1 into strTable; exit when cur1%notfound; if cur1%found then -查找外键 open cur2 for Select a.constraint_name from user_constraints a, user_cons_columns b where a.table_name like strTable and a.constraint_type= R and a.constraint_name=b.constraint_name; -删除外键 loop fetch cur2 into s ; exit when cur2%notfound; if cur2%found then strsql := ALTER TABLE | strTable | drop CONSTRAINT | s; execute immediate strsql; end if; end loop; close cur2; end if; end loop; close cur1; -删除所有表 open cur1 for select table_name from user_tables where table_name not like THM_%; loop fetch cur1 into strTable; exit when cur1%notfound; if cur1%found then -删除表 strsql := drop table |strTable; execute immediate strsql; end if; end loop; close cur1; -清空回收站 strsql := purge recyclebin; execute immediate strsql;end THM_DelAllTAbles;end THM_DelAllTAbles;查找数据库表含重复的项select ta.asset_id from THM_AMS_TAPEDATA ta group by ta.asset_id having count(ta.asset_id) 1条件增量导出$exp hill/passhill tables = thm_test query =where id = 1 file = d:df.dmp;$imp hill/passhill file = d:df.dmp tables= thm_test;先删除原来表，然后再导入创建表空间以及分区：1.导出数据到testtable.dmp(可以是单个表)2.修改原数据库中表testtable的名称,利用第三步创建testtable表的分区3.创建表空间，并对数据库表进行物理分区。create tablespace ORD_TS01 datafile G:oracleproduct10.2.0oradataorclTS01.dbf size 200m; create tablespace ORD_TS02 datafile G:oracleproduct10.2.0oradataorclTS02.dbf size 200m; create tablespace ORD_TS03 datafile G:oracleproduct10.2.0oradataorclTS03.dbf size 200m;CREATE TABLE thm_ORDER_ACTIVITIES ( ORDER_ID NUMBER(7) NOT NULL, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER(7), PAID CHAR(1) ) PARTITION BY RANGE (ORDER_DATE) ( PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE(01-02-2010,DD-MM-YYYY) TABLESPACE ORD_TS01, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE(01-03-2010,DD-MM-YYYY) TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE(01-04-2010,DD-MM-YYYY) TABLESPACE ORD_TS03 )4. IMPORT导入数据，参数ignore=y5 分区表的扩容：建立新的表空间：create tablespace ORD_TS04 datafile G:oracleproduct10.2.0oradataorclTS04.dbf size 200m;为表添加新分区和表空间：alter table thm_ORDER_ACTIVITIES add partition ORD_TS04 values less than (TO_DATE(01-05-2010,DD-MM-YYYY)6 删除不必要的分区alter table thm_ORDER_ACTIVITIES drop partion ORD_ACT_PART01 ;7删除物理文件 ORD_TS016、EXPORT 分区： % exp txs/passhill_password tables= thm_ORDER_ACTIVITIES: ORD_TS04 rows=Y file= ORD_TS04.dmp 7、IMPORT分区： 例如在2001 年，用户要查看2000 年的数据，先创建表空间 create tablespace ORD_TS04 datafile /home/oradata/oradata/test/ ORD_TS04.dbf size 50m default storage (initial 100k next 100k minextents 1 maxextents unlimited pctincrease 1); 为表添加新分区和表空间： alter table guestbook add partition ORD_TS04values less than (to_date(2001-01-01,yyyy-mm-dd) tablespace g_2001q3 storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0); 导入数据 %imp txs/passhill_ file= ORD_TS04.dmp tables=( thm_ORDER_ACTIVITIES: ORD_TS04) ignore=y (说明：如果不指明导入的分区，imp会自动按分区定义的范围装载数据)查看有那些表空间：select t.tablespace_name, round(sum(bytes/(1024*1024),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;查找当前用户使用的表以及表空间信息select * from user_all_tables查看用户和表空间select t.username,t.default_tablespace from dba_users t查看表空间使用率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_NAME;删除表空间drop tablespace . name . including contents and datafiles;查看表空间有那些物理文件select file_name, tablespace_namefrom dba_data_fileswhere tablespace_name =DATA_TEST;查看表空间有那些内容select owner,segment_name,segment_typefrom dba_segmentswhere tablespace_name=DATA_TEST1Oracle设置成单机版：打开路径: listener.ora、sqlnet.ora、tnsnames.ora 打开tnsnames中，取消网络监听就行了。网络版的，配置文件首先是ip，在udm中配置session和连接方式。如何让数据库运行在归档模式sqlplus / as sysdbaSQL shutdown immediate;SQL startup mount;SQL alter database archivelog;SQL alter database open;SQL exit;如何让数据库运行在非归档模式sqlplus / as sysdbaSQL shutdown immediate;SQL startup mount;SQL alter database noarchivelog;SQL alter database open;SQL exit;建立表、自动序列号create table t(pk number primary key,name varchar2(255);create sequence t_seq;create trigger t_trigger before insert on t for each rowbegin select t_seq.nextval into :new.pk from dual;end;（另外可以在pl/sql中设置最小值）数据库版本为oracle 11g R2 的版本，sql导出的方式是采用oracle 11g 客户端自带的Oracle Developer，exp导出的。导入脚本的话，报错应该是提示无 tablespace，需要新建两个表空间，TBS_JSNET 和IDX_JSNET。再创建一个新用户，赋予表空间TBS_JSNET 权限即可create user新用户 identified by password default tablespace TBS_JSNET;grant all privilege to 新用户;GRANT DBA TO 新用户;然后用新建用户登陆oracle ，一般这样就可以导入了。数据库备份关闭实例后，如何登陆sqlplus，再启动实例：打开cmd窗口。输入sqlplus /nolog 回车然后 conn / as sysdba回车然后 startup 回车 1. 冷备份：sqlplus /nolog sql;connect /as sysdba sql;shutdown normal;把select file_name,TAbleSpace_name from dba_data_files 查询到的数据文件，（F:oracleproduct10.2.0oradataorcl路径下的数据文件，控制文件，SELECT*FROMv$log;SELECT*FROMv$logfileORDERBYgroup#;2. 热备份3. RMAN备份方式系统要重新启动实例时候，首先启动F:oracleproduct10.2.0db_1databaseinitorcl.ora文件，读取spfile.ora文件路径，然后找到spfile.ora，再执行。A配置文件F:oracleproduct10.2.0db_1databaseinitSID.ora(初始化设置文件路径)log_archive_start=true #启动自动归档 log_archive_format=ARC%S_%R.%T #归档文件格式 log_archive_dest1=Location = D:/arch12/arch1 #归档路径log_archive_dest1=Location = D:/arch12/arch2B重建spfileSQLCREATE SPFILE = F:oracleproduct10.2.0db_1dbsspfileorcl.oraFROM PFILE = F:oracleproduct10.2.0adminorclpfileinit.ora.582011141241;(注意init.ora和spfileorcl.ora不一定在此路径下，F:oracleproduct10.2.0adminorclpfileinit.ora.582011141241;SPFILE=f:oracleproduct10.2.0db_1/dbs/spfileorcl.ora）SQL show parameter db_recovery_file_dest;NAME TYPE VALUE- - -db_recovery_file_dest string D:oracleflash_recovery_areadb_recovery_file_dest_size big integer 2G然后我SQL create pfile=d:initwhora.ora from spfile;已建立檔案.更改d:initwhora.ora文件的*.db_recovery_file_dest=D:archiveSQL create spfilefrom pfile=d:initwhora.ora;已建立檔案.SQLstartup;创建rman资料库：1 Create tablespace space_for_backup datafile F:oracle_backupspace_for_backup1.dbf size 50M;2 Create user rman identified by zero temporary tablespace temp default tablespace space_for_backup Quota unlimited on space_for_backup;3 grant recovery_catalog_owner to rman;4 Cmdrman5 connect catalog rman/6 rmancreate catalog tablespace space_for_backup;7 rmanregister database;查看数据库运营那个模式select name,log_mode from v$database;数据泵导出：1.创建目录：dmkdir dump2.登陆目录:dcd dump3.登陆数据库：sqlplus sys as sysdba 4、创建目录对象：create directory dmp_dir as D:dump;5、使用户对目录对象有读写权限：grant read,write on directory dmp_dir to VOD_txs;6、使用数据泵导出数据：exit;d: dump expdp VOD_txs/VODtxs directory = dmp_dir dumpfile = VOD20111108.dmp full = y;数据泵导入：1、建立指定目录： c:d:d:mkdir backupd:cd backup将解压缩后的文件放置该目录下d:backupsqlplus sys as sysdba /Txsorcl001Sqlcreate or replace directory DATA_EXPDP_DIR as d:backup;建立表空间create bigfile tablespace dhm datafile F:APPtxsORADATAORCLcoshipdata.dbf size 61140m; 2、重建coshipdhm用户-先把自建表导出来，因为以下删除用户的时候会把所有表删除-exit;c:backupsqlplus sys as sysdba /Txsorcl001Sql drop user coshipdhm cascade; -drop user hill cascade;Sqlcreate user COSHIPDHM identified by coship default tablespace dhm temporary tablespace TEMP profile DEFAULT;- Grant/Revoke role privileges Sqlgrant connect to COSHIPDHM; Sqlgrant dba to COSHIPDHM; -hill Sqlgrant wm_admin_role to COSHIPDHM; - Grant/Revoke system privileges Sqlgrant unlimited tablespace to COSHIPDHM;Sqlgrant read,write on directory data_expdp_dir to coshipdhm; d:backup impdp coshipdhm/coship directory=DATA_EXPDP_DIR dumpfile=coshipdhm_20111027 job_name=imp