ORACLE学习之常用数据库系统表语句

Oracle数据库中，系统表存储了数据库对象的元数据，例如表、视图、索引等，以及用户权限、统计信息等关键信息。掌握常用系统表语句对于数据库的管理和日常维护至关重要。接下来将详细介绍一些常用的Oracle系统表语句及其应用场景。 对于统计分析，`ANALYZE TABLE`语句用于收集表或索引的统计信息，这有助于优化器更好地制定执行计划，从而提升查询效率。例如： ```sql ANALYZE TABLE table_name COMPUTE STATISTICS; ``` 查询当前数据库的默认日期格式，可通过以下语句： ```sql SELECT SYSDATE FROM DUAL; ``` 若需修改日期格式，需要具备DBA权限，并重启数据库才能使改动生效，如下： ```sql ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' SCOPE=SPFILE; SHUTDOWN IMMEDIATE; STARTUP; ``` 重启后可以通过`SHOW PARAMETERS NLS`查看参数变化。 查询表空间文件位置，可使用： ```sql SELECT * FROM DBA_DATA_FILES; ``` 要查看当前用户拥有的表，可以使用： ```sql SELECT * FROM USER_TABLES; ``` 若想查看当前用户能够访问的所有表（包括其他用户的），则： ```sql SELECT * FROM ALL_TABLES; ``` 需要DBA权限才能查看数据库中的所有表： ```sql SELECT * FROM DBA_TABLES; ``` 查看当前用户下的所有对象（表、视图、存储过程等）： ```sql SELECT * FROM TAB; ``` 创建用户时，需要指定用户名和密码： ```sql CREATE USER user_name IDENTIFIED BY pass_word; ``` 授权给用户连接数据库、使用资源以及DBA权限： ```sql GRANT CONNECT, RESOURCE, DBA TO user_name; ``` 如果用户需要查询数据库中所有数据，可以授权： ```sql GRANT SELECT ANY TABLE TO user_name; ``` 用户要读取数据字典，使用OEM工具等，可授权： ```sql GRANT SELECT ANY DICTIONARY TO user_name; ``` 授权给表或存储过程： ```sql GRANT SELECT ON table_name TO user_name; GRANT UPDATE ON table_name TO user_name WITH GRANT OPTION; GRANT EXECUTE ON procedure_name TO user_name; ``` 授权存储过程时，加上WITH GRANT OPTION权限会在授予者失去权限时被回收，而WITH ADMIN OPTION则会永久保留。 ```sql GRANT UPDATE ON table_name TO user_name WITH ADMIN OPTION; ``` 给予所有用户向特定表插入记录的权限： ```sql GRANT ALL ON table_name TO PUBLIC; ``` 如果只赋予用户部分权限，例如创建会话、查询任意表、DBA权限等： ```sql GRANT CREATE SESSION, SELECT ANY TABLE, DBA TO user_name; ``` 清空数据时，使用TRUNCATE命令可以快速移除表中的所有数据： ```sql TRUNCATE TABLE table_name; ``` 查看表结构： ```sql SELECT * FROM USER_TABLES; ``` 查看视图： ```sql SELECT view_name FROM USER_VIEWS; ``` 查询包体： ```sql SELECT s.name, s.text, s.line FROM ALL_SOURCES s WHERE s.type = 'PACKAGE BODY' AND s.owner = 'user_name' GROUP BY s.name, s.text, s.line; ``` 删除用户时，加上CASCADE参数可以删除用户及其所有对象： ```sql DROP USER user_name CASCADE; ``` 创建用户表空间： ```sql CREATE TABLESPACE ods DATAFILE 'D:\app\huyingzhao\oradata\huyingzhao\ods.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1024M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; ``` 若希望用户在没有DBA修改密码的情况下连接数据库，则可以通过SYS用户连接： ```sql CONNECT / AS SYSDBA ``` 检查字段是否有重复数据，可以使用： ```*** ***ment, COUNT(*) FROM table_*** ***ment HAVING COUNT(*) > 1; ``` 获取指定表的创建语句，但受限制只能显示100条： ```sql SELECT DBMS_METADATA.GET-DDL('TABLE', table_name, USER) FROM USER_TABLES WHERE rownum <= 100; ``` 最高效率的分页查询方法如下： ```sql SELECT * FROM ( SELECT a.*, ROWNUM rn FROM ( SELECT * FROM table_name ) a WHERE ROWNUM <= end ) WHERE rn >= start; ``` 查看密码到期时间： ```sql SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME'; ``` 对于某些数据库管理功能，如密码到期时间，可能需要DBA权限来查看。 需要注意的是，由于文档的OCR识别问题，以上SQL语句中可能存在部分词汇错误或遗漏，具体操作时应结合实际环境进行调整。在实际应用中，还需关注安全性和性能优化，合理授权，确保数据库的安全稳定运行。