空白数据库优化

  1. ---空表命令
  2. alter system set deferred_segment_creation=false;
  3. ---口令无期限
  4. ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
  5. ---修改最大连接数
  6. alter system set processes = 500 scope = spfile;
  7. ---修改权限
  8. grant dba to sde;
  9. ---修改密码
  10. alter user sde identified by hz123
  13. -----查看表空间大小和使用率
  14. SELECT D.TABLESPACE_NAME,
  15. SPACE || 'M' "SUM_SPACE(M)",  
  16. BLOCKS "SUM_BLOCKS",  
  17. SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",  
  18. ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",  
  19. FREE_SPACE || 'M' "FREE_SPACE(M)"  
  20. FROM (  SELECT TABLESPACE_NAME,  
  21.       ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
  22.       SUM (BLOCKS) BLOCKS  
  23.       FROM DBA_DATA_FILES  
  24.       GROUP BY TABLESPACE_NAME) D,  
  25.       (  SELECT TABLESPACE_NAME,  
  26.        ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  
  27.        FROM DBA_FREE_SPACE  
  28.        GROUP BY TABLESPACE_NAME) F  
  29.        WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)   
  30.        AND D.TABLESPACE_NAME like '%%'  
  32.        ---Oracle现有表空间 /数据文件信息
  33.        select * from dba_data_files
  34.        ---扩充表空间
  35.        alter tablespace SDE add datafile 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SDE001.DBF' size 100M autoextend on next 50M maxsize 20G;
  36.        alter tablespace USERS add datafile 'D:\APP\ADMINISTRATOR\ORADATA\ZRZYYTH\USERS001.DBF' size 100M autoextend on next 50M maxsize 20G;
  37.        ---优化SDE(必须sde用户登陆)
  38.        SELECT a.*,a.rowid FROM SERVER_CONFIG a where a.prop_name in('CONNECTIONS','TCPKEEPALIVE');
  39.        CONNECTIONS表  NUM_PROP_VALUE  修改为300
  40.        TCPKEEPALIVE表 CHAR_PROP_VALUE 修改为 TRUE
  42.        注册表\HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\TCPIP\Parameters\KeepAliveTime.设置值为300000
  43.        HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\SubSystems\Windows 项目中的SharedSection=1024,3072,512修改为1024,20480,1024
  46.        ---授权
  47.        grant 
  48.        CREATE SESSION,CREATE TABLE ,CREATE PROCEDURE,CREATE SEQUENCE,CREATE TRIGGER,CREATE TYPE,CREATE LIBRARY,CREATE PUBLIC SYNONYM,
  49.        CREATE OPERATOR,CREATE INDEXTYPE,DROP PUBLIC SYNONYM,UNLIMITED TABLESPACE,CREATE VIEW,SELECT ANY TABLE to HSDE;
  50.        ---Oracle中，SDE用户的最小系统权限设置要求是：
  51.        Create procedure / Create table / Create sequence / Create trigger / Create session 
  52.        ---撤权
  53.        revoke  Create table  from  SDE
  55.        ---显示所有用户
  56.        select * from dba_users
  59.        ---创建用户
  60.        create user PBASE  identified by czpbase
  61.        default tablespace CZSDE 
  62.        temporary tablespace temp
  64. ALTER USER 用户名 ACCOUNT UNLOCK