1、查看表空间的名称及大小 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; 2、查看表空间物理文件的名称及大小 select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 3、查看回滚段名称及大小 select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ; 4、查看控制文件 select name from v$controlfile; 5、查看日志文件 select member from v$logfile; 6、查看表空间的使用情况 IXDBA.NET社区论坛 select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name; 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; 7、查看数据库库对象 select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status; 8、查看数据库的版本 Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)=‘Oracle’; 9、查看数据库的创建日期和归档方式 Select Created, Log_Mode, Log_Mode From V$Database; 10、查看当前所有对象 SQL> select * from tab; 11、建一个和a表结构一样的空表 SQL> create table b as select * from a where 1=2; SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2; 12、察看数据库的大小,和空间使用情况 SQL> col tablespace format a20 SQL> select b.file_id 文件ID, b.tablespace_name 表空间, b.file_name 物理文件名, b.bytes 总字节数, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余, sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name / dba_free_space –表空间剩余空间状况 dba_data_files –数据文件空间占用情况 13、查看现有回滚段及其状态 SQL> col segment format a30 SQL> Select SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS; 14、查看数据文件放置的路径 SQL> col file_name format a50 SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 15、显示当前连接用户 SQL> show user 16、把SQL*Plus当计算器 SQL> select 100*20 from dual; 17、连接字符串 SQL> select 列1||列2 from 表1; SQL> select concat(列1,列2) from 表1; 18、查询当前日期 SQL> select to_char(sysdate,‘yyyy-mm-dd,hh24:mi:ss’) from dual; 19、用户间复制数据 SQL> copy from user1 to user2 create table2 using select * from table1; 20、视图中不能使用order by,但可用group by代替来达到排序目的 SQL> create view a as select b1,b2 from b group by b1,b2; 21、通过授权的方式来创建用户 SQL> grant connect,resource to test identified by test; SQL> conn test/test ORACLE 常用脚本(2) 一、ORACLE的表的分类: 1、REGULAR TABLE:普通表,ORACLE推荐的表,使用很方便,人为控制少。 2、PARTITIONED TABLE:分区表,人为控制记录的分布,将表的存储空间分为若干独立的分区,记录按一定的规则存储在分区里。适用于大型的表。 二、建表 1 Create TABLE 表名 (EMPNO NUMBER(2),NAME VARCHAR2(20)) PCTFREE 20 PCTUSED 50 STORAGE (INITIAL 200K NEXT 200K MAXEXTENTS 200 PCTINCREASE 0) TABLESPACE 表空间名称 [LOGGING|NOLOGGING]所有的对表的操作都要记入REDOLOG,ORACLE建议使用NOLOGGING; [CACHE|NOCACHE]:是否将数据按照一定的算法写入内存。 2、关于PCTFREE 和PCTUSED A、行迁移和行链接 B、PCTFREE:制止Insert,为 Update留FREE 空间 C、PCTUSED:为恢复Insert操作,而设定的。 三、拷贝一个已经存在的表: Create TABLE 新表名 STORAGE(。。) TABLESPACE 表空间 AS Select * FROM 老表名 ; 当老表存在约束,触发的时候,不会拷过去。 四、修改表的参数 Alter TABLE 名称 PCTFREE 20 PCTUSED 50 STOAGE(MAXEXTENTS 1000); 五、手工分配空间: Alter TABLE 名称 ALLOCATE EXTENT(SIZE 500K DATAFILE ‘。。’); 1、SIZE选项,按照NEXT分配 2、表所在表空间与所分配的数据文件所在的表空间必须一样。 六、水线 1、水线定义了表的数据在一个BLOCK中所达到的最高的位置。 2、当有新的记录插入,水线增高 3、当删除记录时,水线不回落 4、减少查询量 七、如何回收空间: Alter TABLE 名称 DEALLOCATE UNUSED [KEEP 4[M|K]] 1、当空间分配过大时,可以使用本命令 2、如果没有加KEEP,回收到水线 3、如果水线《MINEXTENTS的大小回收到MINEXTENTS所指定的大小 八、TRUNCATE 一个表 TRUNCATE TABLE 表名,表空间截取MINEXTENT,同时水线重置。 九、Drop 一个表 Drop TABLE 表名 [CASCADE CONSTRAINTS] 当一个表含有外键的时候,是不可以直接Drop的,加CASCADE CONSRIANTS将外键等约束一并删掉。 十、信息获取 1、dba_object 2 dba_tables:建表的参数 3 DBA_SEGMENTS: 组合查询的连接字段:DBA_TABLES的table_name+dba_ojbect的object_name+dba_segments的SEGMENT_NAME ORACLE 常用脚本(3) 一、ORACLE的安全域 1、TABLESPACE QUOTAS:表空间的使用定额 2、DEFAULT TABLESPACE:默认表空间 3、TEMPORARY TABLESPACE:指定临时表空间。 4、ACCOUNT LOCKING:用户锁 5、RESOURCE LIMITE:资源限制 6、DIRECT PRIVILEGES:直接授权 7、ROLE PRIVILEGES:角色授权先将应用中的用户划为不同的角色, 二、创建用户时的清单: 1、选择一个用户名称和检验机制:A,看到用户名,实际操作者是谁,业务中角色。 2、选择合适的表空间: 3、决定定额: 4、口令的选择: 5、临时表空间的选择:先建立一个临时表空间,然后在分配。不分配,使用SYSTEM表空间 6、Create USER 7、授权:A,用户的工作职能 B,用户的级别 三、用户的创建: 1、命令: Create USER 名称 IDENTIFIED BY 口令 DEFAULT TABLESPACE 默认表空间名 TEMPOARAY TABLESPACE 临时表空间名 QUOTA 15M ON 表空间名 [PASSWORD EXPIRE]:当用户第一次登陆到ORACLE,创建时所指定的口令过期失效,强迫用户自己定义一个新口令。 [ACCOUNT LOCK]:加用户锁 QUOTA UNLIMITED ON TABLESPACE:不限制,有多少有多少。 [PROFILE 名称]:受PROFILE文件的限制。 四、如何控制用户口令和用户锁 1、强迫用户修改口令:Alter USER 名称 IDENTIFIED BY 新口令 PASSWORD EXPIRE; 2、给用户加锁:Alter USER 名称 ACCOUNT [LOCK|UNLOCK] 3、注意事项: A、所有操作对当前连接无效 B、1的操作适用于当用户忘记口令时。 五、更改定额 1、命令:Alter USER 名称 QUOTA 0 ON 表空间名 Alter USER 名字 QUOTA (数值)K|M|UNLIMITED ON 表空间名; 2、使用方法: A、控制用户数据增长 B、当用户拥有一定的数据,而管理员不想让他在增加新的数据的时候。 C、当将用户定额设为零的时候,用户不能创建新的数据,但原有数据仍可访问。 六、Drop一个USER 1、Drop USER 名称 适合于删除一个新的用户 2、Drop USER 名称 CASCADE: 删除一个用户,将用户的表,索引等都删除。 3、对连接中的用户不好用。 七、信息获取: 1、DBA_USERS:用户名,状态,加锁日期,默认表空间,临时表空间 2、DBA_TS_QUOTAS:用户名,表空间名,定额。 两个表的连接字段:USERNAME GRANT Create SESSION TO 用户名 PROFILE的管理(资源 文件) 一、PROFILE的管理内容: 1、CPU的时间 2、I/O的使用 3、IDLE TIME(空闲时间) 4、CONNECT TIME(连接时间) 5、并发会话数量 6、口令机制: 二、DEFAULT PROFILE: 1、所有的用户创建时都会被指定这个PROFILE 2、DEFAULT PROFILE的内容为空,无限制 三、PROFILE的划分: 1、CALL级LIMITE: 对象是语句: 当该语句资源使用溢出时: A、该语句终止 B、事物回退 C、SESSION连接保持 2、SESSION级LIMITE: 对象是:整个会话过程 溢出时:连接终止 四、如何管理一个PROFILE 1、Create PROFILE 2、分配给一个用户 3、象开关一样打开限制。 五、如何创建一个PROFILE: 1、命令:Create PROFILE 名称 LIMIT SESSION_PER_USER 2 CPU_PER_SESSION 1000 IDLE_TIME 60 CONNECT_TIME 480 六、限制参数: 1、SESSION级LIMITE: CPU_PER_SESSION:定义了每个SESSION占用的CPU的时间: (1/100 秒) 2、SESSION_PER_USER:每个用户的并发连接数 3、CONNECT_TIME:一个连接的最长连接时间(分钟) 4、LOGICAL_READS_PER_SESSION: 一次读写的逻辑块的数量 5、CALL级LIMITE CPU_PER_CALL:每个语句占用的CPU时间 LOGICAL_READS_PER_CALL: 七、分配给一个用户: Create USER 名称。。。。。。 PROFILE 名称 Alter USER 名称 PROFILE 名称 八、打开资源限制: 1、RESOURCE_LIMT:资源文件中含有 2、Alter SYSTEM SET RESOURCE_LIMIT=TRUE; 3、默认不打开 九、修改PROFIE的内容: 1、Alter PROFILE 名称参数 新值 2、对于当前连接修改不生效。 Drop一个PROFILE 1、Drop PROFILE 名称 删除一个新的尚未分配给用户的PROFILE, 2、Drop PROFILE 名称 CASCADE 3、注意事项 A、一旦PROFILE被删除,用户被自动加载DEFAULT PROFILE B、对于当前连接无影响 C、DEFAULT PROFILE不可以被删除 信息获取: 1、DBA_USERS: 用户名,PROFILE 2、DBA_PROFILES: PROFILE及各种限制参数的值 每个用户的限制:PROFILE(关键字段) PROFILE的口令机制限制 1、限制内容 A、限制连续多少次登录失败,用户被加锁 B、限制口令的生命周期 C、限制口令的使用间隔 2、限制生效的前提: A、RESOURCE_LIMIT:=TRUE B orACLE\RDBMS\ADMIN\UTLPWDMG.SQL 3、如何创建口令机制: Create PROFILE 名称 SESSIONS_PER_USER ….. password_life_time 30 failed_log_attempts 3 password_reuse_time 3 4、参数的含义: A FAILED_LOGIN_ATTEMPTS: 当连续登陆失败次数达到该参数指定值时,用户加锁 B PASSWORD_LOCK_TIME:加锁天数 C PASSWORD_LIFE_TIME:口令的有效期(天) D PASSWORD_GRACE_TIME:口令修改的间隔期(天) E PASSWORD_REUSE_TIME:口令被修改后原有口令隔多少天被重新使用。 F PASSWORD_REUSE_MAX:口令被修改后原有口令被修改多少次被重新使用。