今天要跟大家来聊一聊俺在使用Oracle数据库的这段时间里面学到的一些知识。 俺最近出来工作了,来到的公司是使用的Oracle数据库,之前一直使用的是SQL的数据库,在性质上二者前者是收费的,后者是开源的;在使用上主要还是靠自己探索,下面说说俺的一些收获
今天要跟大家来聊一聊俺在使用Oracle数据库的这段时间里面学到的一些知识。俺最近出来工作了,来到的公司是使用的Oracle数据库,之前一直使用的是SQL的数据库,在性质上二者前者是收费的,后者是开源的;在使用上主要还是靠自己探索,下面说说俺的一些收获。在前段时间俺一直在鼓捣组长分下来的一个任务,定时同步数据的一个小程序,俺的Oracle之旅也是从这里才算是正式开始的。
近期的学习中俺了解到Oracle数据库本身是自带三个默认用户的:sys(网络管理员)、system(本地普通管理员)、scott(本地普通用户)。目前俺只是了解了system这个用户,了解的也只是权限上的一些皮毛,其它两个还没有认知。我们在使用Oracle数据库时会创建不同的用户来对应不同的系统或者程序,不同的用户下面有自己对应的表和数据。下面这个就是创建用户的前一部分语句:
-- Create the user create user JCSYN <span style="white-space:pre"> </span>----创建JCSYN这个角色 identified by password<span style="white-space:pre"> </span>----给这个角色设置密码 default tablespace PMS<span style="white-space:pre"> </span>----默认的表空间 temporary tablespace TEMP<span style="white-space:pre"> </span>----临时的表空间 profile DEFAULT<span style="white-space:pre"> </span>----配置文件默认 quota unlimited on pms<span style="white-space:pre"> </span>----配置非限额在PMS这个表空间 quota unlimited on pms_lob;<span style="white-space:pre"> </span>----配置非限额在PMS_LOB这个表空间如果我们仅仅是创建这个角色的话就没有问题了,但是我们现在使用中还需要用到增上改查之类的操作,这就需要我们赋予角色特定的权限,这样才能实现我们的使用要求,下面就是一些语句:
赋予权限的语句:grant 权限 to 用户;
移除权限的语句:revoke 权限 from 用户;
如果我们不清楚角色都有什么权限,我们可以使用下面的语句来查找数据库中的所有角色权限信息:
select * from user_sys_privs ;
下面我将使用过的创建角色语句给大家看一下,仅供参考!
-- Create the user create user JCSYN identified by password default tablespace PMS temporary tablespace TEMP profile DEFAULT quota unlimited on pms quota unlimited on pms_lob; -- Grant/Revoke object privileges grant select, insert, update, delete on HORIZON.TOR_HORIZON_USER_DEPT to JCSYN; grant select, insert, update, delete on HORIZON.TO_HORIZON_DEPT to JCSYN; grant select, insert, update, delete on HORIZON.TO_HORIZON_USER to JCSYN; grant select, insert, update, delete on HORIZON.TPR_HORIZON_OBJ_ROLE to JCSYN; -- Grant/Revoke role privileges grant connect to JCSYN; grant scheduler_admin to JCSYN; -- Grant/Revoke system privileges grant alter any procedure to JCSYN; grant create any synonym to JCSYN; grant create database link to JCSYN; grant create job to JCSYN; grant create materialized view to JCSYN; grant create procedure to JCSYN; grant create public database link to JCSYN; grant create sequence to JCSYN; grant create synonym to JCSYN; grant create table to JCSYN; grant create trigger to JCSYN; grant create type to JCSYN; grant create view to JCSYN; grant debug any procedure to JCSYN; grant debug connect session to JCSYN; grant delete any table to JCSYN; grant drop public database link to JCSYN; grant execute any class to JCSYN; grant execute any library to JCSYN; grant execute any procedure to JCSYN with admin option; grant execute any program to JCSYN with admin option; grant execute any type to JCSYN; grant manage scheduler to JCSYN; grant select any dictionary to JCSYN; grant select any table to JCSYN;

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SublimeText3 English version
Recommended: Win version, supports code prompts!

SublimeText3 Linux new version
SublimeText3 Linux latest version

Notepad++7.3.1
Easy-to-use and free code editor
