select count(*) from v$process --当前的连接数 select value from v$parameter where name = 'processes'--数据库允许的最大连接数
查看版本
select * from v$version;
批量将表名改为大写,方便写查询语句,方法如下:在PL/SQL中执行下面sql
select 'alter table "'||table_name||'" rename to '||upper(table_name)||';' from user_tables where table_name<>upper(table_name);
生成修改表名语句
然后在执行这些alter语句就可以了。
当使用powerdesigner创建数据库时要注意大小写。
注:以下脚本在oracle 10g,11g上正确执行
批量将表名变为大写
begin for c in (select table_name tn from user_tables where table_name <> upper(table_name)) loop begin execute immediate 'alter table "'||c.tn||'" rename to '||c.tn; exception when others then dbms_output.put_line(c.tn||'已存在'); end; end loop; end;
批量将空间内所有表的所有字段名变成大写
begin for t in (select table_name tn from user_tables) loop begin for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop begin execute immediate 'alter table "'||t.tn||'" rename column "'||c.cn||'" to '||c.cn; exception when others then dbms_output.put_line(t.tn||'.'||c.cn||'已经存在'); end; end loop; end; end loop; end;
将用户空间的所有表名及所有字段变为大写
begin for t in (select table_name tn from user_tables where table_name <> upper(table_name)) loop begin for c in (select column_name cn from user_tab_columns where table_name=t.tn) loop begin execute immediate 'alter table "'||t.tn||'" rename column "'||c.cn||'" to '||c.cn; exception when others then dbms_output.put_line(t.tn||'.'||c.cn||'已经存在'); end; end loop; execute immediate 'alter table "'||t.tn||'" rename to '||t.tn; exception when others then dbms_output.put_line(t.tn||'已存在'); end; end loop; end;
创建序列
create sequence HELP_ID_SEQ minvalue 1 maxvalue 999999999 start with 1 increment by 1 cache 20;
查询实例名称
select instance_name from v$instance
查询版本
select * from v$version
查看所有表空间
方法一:
select dbf.tablespace_name, dbf.totalspace "总量(M)", dbf.totalblocks as 总块数, dfs.freespace "剩余总量(M)", dfs.freeblocks "剩余块数", (dfs.freespace / dbf.totalspace) * 100 "空闲比例" from (select t.tablespace_name, sum(t.bytes) / 1024 / 1024 totalspace, sum(t.blocks) totalblocks from dba_data_files t group by t.tablespace_name) dbf, (select tt.tablespace_name, sum(tt.bytes) / 1024 / 1024 freespace, sum(tt.blocks) freeblocks from dba_free_space tt group by tt.tablespace_name) dfs where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
方法二:
SELECT Total.name "Tablespace Name", Free_space, (total_space-Free_space) Used_space, total_space FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space group by tablespace_name ) Free, (select b.name, sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name ) Total WHERE Free.Tablespace_name = Total.name
密码错误次数过多账号锁定
一般数据库默认是10次尝试失败后锁住用户 1、查看FAILED_LOGIN_ATTEMPTS的值 select * from dba_profiles 2、修改为30次 alter profile default limit FAILED_LOGIN_ATTEMPTS 30; 3、修改为无限次(为安全起见,不建议使用) alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited; 解锁: alter user user_name account unlock;
发表评论(对文章涉及的知识点还有疑问,可以在这里留言,老高看到后会及时回复的。)