求oracle中建用户表空间, 如何在oracle中建用户表空间,建同义词;等常用oracle管理语句,email:[email protected];收到马上给分!! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 CREATE TABLESPACE TBS_TEST DATAFILE 'D:\ORADATA\ORA92\TEST.DBF' SIZE 50M; 日志管理 1.forcing log switches sql> alter system switch logfile; 2.forcing checkpoints sql> alter system checkpoint; 3.adding online redo log groups sql> alter database add logfile [group 4] sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m; 4.adding online redo log members sql> alter database add logfile member sql> '/disk3/log1b.rdo' to group 1, sql> '/disk4/log2b.rdo' to group 2; 5.changes the name of the online redo logfile sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log' sql> to 'c:/oracle/oradata/redo01.log'; 6.drop online redo log groups sql> alter database drop logfile group 3; 7.drop online redo log members sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log'; 8.clearing online redo log files sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo'; 9.using logminer analyzing redo logfiles a. in the init.ora specify utl_file_dir = ' ' b. sql> execute dbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log'); c. sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log', sql> dbms_logmnr.new); d. sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log', sql> dbms_logmnr.addfile); e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora'); f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters sql> v$logmnr_logs); g. sql> execute dbms_logmnr.end_logmnr;表空间管理 1.create tablespaces sql> create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf' size 100m, sql> 'c:\oracle\oradata\file2.dbf' size 100m minimum extent 550k [logging/nologging] sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0) sql> [online/offline] [permanent/temporary] [extent_management_clause] 2.locally managed tablespace sql> create tablespace user_data datafile 'c:\oracle\oradata\user_data01.dbf' sql> size 500m extent management local uniform size 10m; 3.temporary tablespace sql> create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf' sql> size 500m extent management local uniform size 10m; 4.change the storage setting sql> alter tablespace app_data minimum extent 2m; sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999); 5.taking tablespace offline or online sql> alter tablespace app_data offline; sql> alter tablespace app_data online; 6.read_only tablespace sql> alter tablespace app_data read only|write; 7.droping tablespace sql> drop tablespace app_data including contents; 8.enableing automatic extension of data files sql> alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf' size 200m sql> autoextend on next 10m maxsize 500m; 9.change the size fo data files manually sql> alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m; 10.Moving data files: alter tablespace sql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf' sql> to 'c:\oracle\app_data.dbf'; 11.moving data files:alter database sql> alter database rename file 'c:\oracle\oradata\app_data.dbf' sql> to 'c:\oracle\app_data.dbf';用户管理 1.create a user: database authentication sql> create user juncky identified by oracle default tablespace users sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default]; 2.change user quota on tablespace sql> alter user juncky quota 0 on users; 3.drop a user sql> drop user juncky [cascade]; 4. monitor user view: dba_users , dba_ts_quotas备份与恢复 1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat 2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size 3. Monitoring Parallel Rollback > v$fast_start_servers , v$fast_start_transactions 4.perform a closed database backup (noarchivelog) > shutdown immediate > cp files /backup/ > startup 5.restore to a different location > connect system/manager as sysdba > startup mount > alter database rename file '/disk1/../user.dbf' to '/disk2/../user.dbf'; > alter database open; 6.recover syntax --recover a mounted database >recover database; >recover datafile '/disk1/data/df2.dbf'; >alter database recover database; --recover an opened database >recover tablespace user_data; >recover datafile 2; >alter database recover datafile 2; 7.how to apply redo log files automatically >set autorecovery on >recover automatic datafile 4; 8.complete recovery: --method 1(mounted databae) >copy c:\backup\user.dbf c:\oradata\user.dbf >startup mount >recover datafile 'c:\oradata\user.dbf; >alter database open; --method 2(opened database,initially opened,not system or rollback datafile) >copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline) >recover datafile 'c:\oradata\user.dbf' or >recover tablespace user_data; >alter database datafile 'c:\oradata\user.dbf' online or >alter tablespace user_data online; --method 3(opened database,initially closed not system or rollback datafile) >startup mount >alter database datafile 'c:\oradata\user.dbf' offline; >alter database open >copy c:\backup\user.dbf d:\oradata\user.dbf >alter database rename file 'c:\oradata\user.dbf' to 'd:\oradata\user.dbf' >recover datafile 'e:\oradata\user.dbf' or recover tablespace user_data; >alter tablespace user_data online; --method 4(loss of data file with no backup and have all archive log) >alter tablespace user_data offline immediate; >alter database create datafile 'd:\oradata\user.dbf' as 'c:\oradata\user.dbf'' >recover tablespace user_data; >alter tablespace user_data online 5.perform an open database backup > alter tablespace user_data begin backup; > copy files /backup/ > alter database datafile '/c:/../data.dbf' end backup; > alter system switch logfile; 6.backup a control file > alter database backup controlfile to 'control1.bkp'; > alter database backup controlfile to trace; 7.recovery (noarchivelog mode) > shutdown abort > cp files > startup 8.recovery of file in backup mode >alter database datafile 2 end backup; 9.clearing redo log file >alter database clear unarchived logfile group 1; >alter database clear unarchived logfile group 1 unrecoverable datafile; 10.redo log recovery >alter database add logfile group 3 'c:\oradata\redo03.log' size 1000k; >alter database drop logfile group 1; >alter database open; or >cp c:\oradata\redo02.log' c:\oradata\redo01.log >alter database clear logfile 'c:\oradata\log01.log'; ▲创建表空间①查看现有表空间信息SELECT a.file_id,a.tablespace_name,a.bytes/1024/1024 as "Total(MB)", sum(nvl(b.bytes,0))/1024/1024 as "Free(MB)",sum(nvl(b.bytes,0))/a.bytes*100 as "%free"FROM dba_data_files a,dba_free_space bWHERE a.file_id=b.file_id(+)GROUP BY a.tablespace_name,a.file_id,a.bytesORDER BY a.tablespace_name;②手工创建表空间CREATE TABLESPACE 表空间名称DATAFILE '文件名' [SIZE 数目 [K|M] ] [REUSE] [AUTOEXTEND {OFF | ON MAXSIZE { UNLIMITED | 数目 [K|M] } ] } ] [, ...其它数据文件...] [ONLINE | OFFLINE] [PERMANENT | TEMPORARY] [MANAGEMENT LOCAL | DICTIONARY]/*详见《ORACLE 9I入门与提高》P182*/例:CREATE TABLESPACE USERNAME_DATADATAFILE 'C:\Oracle8\DATABASE\USERNAME_data.dbf' SIZE 256M REUSE --REUSE覆盖原数据文件 AUTOEXTEND ON NEXT 2M MAXSIZE 2048M --允许表空间自动扩允,每次增加2M,空间最大值2G DEFAULT STORAGE( --设置存储默认值 INITIAL 64K --分配给对象的初始空间大小 NEXT 64K --每次增加大小,通常与INITIAL值相同 MINEXTENTS 1 --多数情况取值为1 MAXEXTENTS UNLIMITED --目前设置为"对象大小无限制" PCTINCREASE 0 --通常情况取值为0 ) ONLINE PERMANENT;-------------------------------------------SQL> SELECT a.file_id,a.tablespace_name,a.bytes/1024/1024 as "Total(MB)", 2 sum(nvl(b.bytes,0))/1024/1024 as "Free(MB)",sum(nvl(b.bytes,0))/a.bytes*100 as "%free" 3 FROM dba_data_files a,dba_free_space b 4 WHERE a.file_id=b.file_id(+) 5 GROUP BY a.tablespace_name,a.file_id,a.bytes 6 ORDER BY a.tablespace_name; FILE_ID TABLESPACE_NAME Total(MB) Free(MB) %free---------- ------------------------------ ---------- ---------- ---------- 3 ROLLBACK_DATA 10 5.31054687 53.1054687 1 SYSTEM 60 6.203125 10.3385416 4 TEMPORARY_DATA 2 1.99804687 99.9023437 2 USER_DATA 3 1.5234375 50.78125SQL> SQL> CREATE TABLESPACE USERNAME_DATA 2 DATAFILE 3 'C:\Oracle8\DATABASE\USERNAME_data.dbf' SIZE 256M REUSE --REUSE覆盖原数据文件 4 AUTOEXTEND ON NEXT 2M MAXSIZE 2048M --允许表空间自动扩允,每次增加2M,空间最大值2G 5 DEFAULT STORAGE( 6 INITIAL 64K --分配给对象的初始空间大小 7 NEXT 64K --每次增加大小,通常与INITIAL值相同 8 MINEXTENTS 1 --多数情况取值为1 9 MAXEXTENTS UNLIMITED --目前设置为"对象大小无限制" 10 PCTINCREASE 0 --通常情况取值为0 11 ) 12 ONLINE 13 PERMANENT;表空间被创建SQL> SQL> SELECT a.file_id,a.tablespace_name,a.bytes/1024/1024 as "Total(MB)", 2 sum(nvl(b.bytes,0))/1024/1024 as "Free(MB)",sum(nvl(b.bytes,0))/a.bytes*100 as "%free" 3 FROM dba_data_files a,dba_free_space b 4 WHERE a.file_id=b.file_id(+) 5 GROUP BY a.tablespace_name,a.file_id,a.bytes 6 ORDER BY a.tablespace_name; FILE_ID TABLESPACE_NAME Total(MB) Free(MB) %free---------- ------------------------------ ---------- ---------- ---------- 5 USERNAME_DATA 256 255.998046 99.9992370 3 ROLLBACK_DATA 10 5.31054687 53.1054687 1 SYSTEM 60 6.19335937 10.3222656 4 TEMPORARY_DATA 2 1.99804687 99.9023437 2 USER_DATA 3 1.5234375 50.78125SQL>③修改表空间ALTER TABLESPACE 表空间名称 {[COALESCE] [PERMANENT | TEMPORARY] [READ ONLY | READ WRITE] [ONLINE] [OFFLINE [NORMAL | TEMPORARY |IMMEDIATE | FOR RECOVER]] [MINMUM EXTENT 数目 [K|M] ] [DEFAULT 存储参数语句] [数据文件语句] }/*详见《ORACLE 9I入门与提高》P186*/-------------------------------------------例:增加表空间大小ALTER TABLESPACE INDEX_RPT ADD DATAFILE '/dev/vg_ora5/rdata_1g_172' SIZE 1023M;例:改变表空间数据文件大小ALTER TABLESPACE USERNAME_DATADATAFILE 'C:\Oracle8\DATABASE\USERNAME_DATA.DBF' RESIZE 250K;④撤消空间ALTER TABLESPACE USERNAME_DATA OFFLINE;DROP TABLESPACE USERNAME_DATA; ApplicationContextAware 得不到Bean SQL分页与Oracle分页的疑问? 这个错误是什么意思????急急!!! 一个页面要显示多个模型(vo)的数据,action怎么设计? 在线等 急 关于 XML的编码问题 简单的JSP程序不知道哪错了 jsp中遇到一弱问题 怎么读取文件创建日期属性 整型转字符串型的函数是什么? 怎么保持出错前页面的表单信息? xmlsocket 与jsp 通讯的问题!(100分全给了) 求AD(Active Directory和ca安全认证相结合的方案
DATAFILE 'D:\ORADATA\ORA92\TEST.DBF' SIZE 50M;
用户管理 1.create a user: database authentication sql> create user juncky identified by oracle default tablespace users sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default]; 2.change user quota on tablespace sql> alter user juncky quota 0 on users; 3.drop a user sql> drop user juncky [cascade]; 4. monitor user view: dba_users , dba_ts_quotas备份与恢复 1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat 2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size 3. Monitoring Parallel Rollback > v$fast_start_servers , v$fast_start_transactions 4.perform a closed database backup (noarchivelog) > shutdown immediate > cp files /backup/ > startup 5.restore to a different location > connect system/manager as sysdba > startup mount > alter database rename file '/disk1/../user.dbf' to '/disk2/../user.dbf'; > alter database open; 6.recover syntax --recover a mounted database >recover database; >recover datafile '/disk1/data/df2.dbf'; >alter database recover database; --recover an opened database >recover tablespace user_data; >recover datafile 2; >alter database recover datafile 2; 7.how to apply redo log files automatically >set autorecovery on >recover automatic datafile 4; 8.complete recovery: --method 1(mounted databae) >copy c:\backup\user.dbf c:\oradata\user.dbf >startup mount >recover datafile 'c:\oradata\user.dbf; >alter database open; --method 2(opened database,initially opened,not system or rollback datafile) >copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline) >recover datafile 'c:\oradata\user.dbf' or >recover tablespace user_data; >alter database datafile 'c:\oradata\user.dbf' online or >alter tablespace user_data online; --method 3(opened database,initially closed not system or rollback datafile) >startup mount >alter database datafile 'c:\oradata\user.dbf' offline; >alter database open >copy c:\backup\user.dbf d:\oradata\user.dbf >alter database rename file 'c:\oradata\user.dbf' to 'd:\oradata\user.dbf' >recover datafile 'e:\oradata\user.dbf' or recover tablespace user_data; >alter tablespace user_data online; --method 4(loss of data file with no backup and have all archive log) >alter tablespace user_data offline immediate; >alter database create datafile 'd:\oradata\user.dbf' as 'c:\oradata\user.dbf'' >recover tablespace user_data; >alter tablespace user_data online 5.perform an open database backup > alter tablespace user_data begin backup; > copy files /backup/ > alter database datafile '/c:/../data.dbf' end backup; > alter system switch logfile; 6.backup a control file > alter database backup controlfile to 'control1.bkp'; > alter database backup controlfile to trace; 7.recovery (noarchivelog mode) > shutdown abort > cp files > startup 8.recovery of file in backup mode >alter database datafile 2 end backup; 9.clearing redo log file >alter database clear unarchived logfile group 1; >alter database clear unarchived logfile group 1 unrecoverable datafile; 10.redo log recovery >alter database add logfile group 3 'c:\oradata\redo03.log' size 1000k; >alter database drop logfile group 1; >alter database open; or >cp c:\oradata\redo02.log' c:\oradata\redo01.log >alter database clear logfile 'c:\oradata\log01.log';
①查看现有表空间信息
SELECT a.file_id,a.tablespace_name,a.bytes/1024/1024 as "Total(MB)",
sum(nvl(b.bytes,0))/1024/1024 as "Free(MB)",sum(nvl(b.bytes,0))/a.bytes*100 as "%free"
FROM dba_data_files a,dba_free_space b
WHERE a.file_id=b.file_id(+)
GROUP BY a.tablespace_name,a.file_id,a.bytes
ORDER BY a.tablespace_name;
②手工创建表空间
CREATE TABLESPACE 表空间名称
DATAFILE
'文件名' [SIZE 数目 [K|M] ] [REUSE]
[AUTOEXTEND
{OFF | ON
MAXSIZE { UNLIMITED | 数目 [K|M] } ] } ]
[, ...其它数据文件...]
[ONLINE | OFFLINE]
[PERMANENT | TEMPORARY]
[MANAGEMENT LOCAL | DICTIONARY]
/*详见《ORACLE 9I入门与提高》P182*/
例:
CREATE TABLESPACE USERNAME_DATA
DATAFILE
'C:\Oracle8\DATABASE\USERNAME_data.dbf' SIZE 256M REUSE --REUSE覆盖原数据文件
AUTOEXTEND ON NEXT 2M MAXSIZE 2048M --允许表空间自动扩允,每次增加2M,空间最大值2G
DEFAULT STORAGE( --设置存储默认值
INITIAL 64K --分配给对象的初始空间大小
NEXT 64K --每次增加大小,通常与INITIAL值相同
MINEXTENTS 1 --多数情况取值为1
MAXEXTENTS UNLIMITED --目前设置为"对象大小无限制"
PCTINCREASE 0 --通常情况取值为0
)
ONLINE
PERMANENT;
-------------------------------------------
SQL> SELECT a.file_id,a.tablespace_name,a.bytes/1024/1024 as "Total(MB)",
2 sum(nvl(b.bytes,0))/1024/1024 as "Free(MB)",sum(nvl(b.bytes,0))/a.bytes*100 as "%free"
3 FROM dba_data_files a,dba_free_space b
4 WHERE a.file_id=b.file_id(+)
5 GROUP BY a.tablespace_name,a.file_id,a.bytes
6 ORDER BY a.tablespace_name; FILE_ID TABLESPACE_NAME Total(MB) Free(MB) %free
---------- ------------------------------ ---------- ---------- ----------
3 ROLLBACK_DATA 10 5.31054687 53.1054687
1 SYSTEM 60 6.203125 10.3385416
4 TEMPORARY_DATA 2 1.99804687 99.9023437
2 USER_DATA 3 1.5234375 50.78125SQL>
SQL> CREATE TABLESPACE USERNAME_DATA
2 DATAFILE
3 'C:\Oracle8\DATABASE\USERNAME_data.dbf' SIZE 256M REUSE --REUSE覆盖原数据文件
4 AUTOEXTEND ON NEXT 2M MAXSIZE 2048M --允许表空间自动扩允,每次增加2M,空间最大值2G
5 DEFAULT STORAGE(
6 INITIAL 64K --分配给对象的初始空间大小
7 NEXT 64K --每次增加大小,通常与INITIAL值相同
8 MINEXTENTS 1 --多数情况取值为1
9 MAXEXTENTS UNLIMITED --目前设置为"对象大小无限制"
10 PCTINCREASE 0 --通常情况取值为0
11 )
12 ONLINE
13 PERMANENT;表空间被创建SQL>
SQL> SELECT a.file_id,a.tablespace_name,a.bytes/1024/1024 as "Total(MB)",
2 sum(nvl(b.bytes,0))/1024/1024 as "Free(MB)",sum(nvl(b.bytes,0))/a.bytes*100 as "%free"
3 FROM dba_data_files a,dba_free_space b
4 WHERE a.file_id=b.file_id(+)
5 GROUP BY a.tablespace_name,a.file_id,a.bytes
6 ORDER BY a.tablespace_name; FILE_ID TABLESPACE_NAME Total(MB) Free(MB) %free
---------- ------------------------------ ---------- ---------- ----------
5 USERNAME_DATA 256 255.998046 99.9992370
3 ROLLBACK_DATA 10 5.31054687 53.1054687
1 SYSTEM 60 6.19335937 10.3222656
4 TEMPORARY_DATA 2 1.99804687 99.9023437
2 USER_DATA 3 1.5234375 50.78125SQL>
③修改表空间
ALTER TABLESPACE 表空间名称
{[COALESCE]
[PERMANENT | TEMPORARY]
[READ ONLY | READ WRITE]
[ONLINE]
[OFFLINE [NORMAL | TEMPORARY |IMMEDIATE | FOR RECOVER]]
[MINMUM EXTENT 数目 [K|M] ]
[DEFAULT 存储参数语句]
[数据文件语句]
}
/*详见《ORACLE 9I入门与提高》P186*/
-------------------------------------------
例:增加表空间大小
ALTER TABLESPACE INDEX_RPT ADD
DATAFILE '/dev/vg_ora5/rdata_1g_172' SIZE 1023M;
例:改变表空间数据文件大小
ALTER TABLESPACE USERNAME_DATA
DATAFILE 'C:\Oracle8\DATABASE\USERNAME_DATA.DBF' RESIZE 250K;④撤消空间
ALTER TABLESPACE USERNAME_DATA OFFLINE;
DROP TABLESPACE USERNAME_DATA;