Oracle 利用函数将时间戳转换成日期格式的方法。数据库中有三个字段，其中关于 时间的是采用 时间戳的格式存储的，如下图 SIM BILL_NUM STARTTIME 18307135480 9001445665 1598016414 18707204757 9001445665 1598016417 15207169407 9003664475 1598251705 18827672101 9003664475 1598251707 13437294319 99270014770 1598343236 13554194307 99270014770 1598343237 13437294316 99270014770 1598343238 13554194616 99270014770 1598343238   如果要查询一段时间内的数据，每次都要转换时间格式，很不方面。但是可以作了 时间戳转换日期的函数，这样每次只需要调用就可以了。/* 格式化对象 2020/9/2 16:47:52 (QP5 v5.287) */CREATE OR REPLACE FUNCTION FUN_UTC2DATE (UTCDATE NUMBER, PATTERN VARCHAR2)   RETURN DATEIS   Result   DATE;BEGIN   Result :=      TO_DATE (         TO_CHAR (              TO_DATE (19700101000000, 'YYYYMMDDHH24MISS')  &n...

oracle PLSQL,游标,loop循环删除部分表方法。使用Toad for Oracle时，使用新建用户连接时，连接身份 搞成了 as sysdba，导致执行添加表与表数据时，都变成sys账号下了。表比较多，删除时使用游标与loop循环，执行不成功。不清楚哪里不对(主要是水平太次，)。如下：declare  CURSOR d_cursor is    SELECT OBJECT_NAME      FROM sys.DBA_objects     WHERE OBJECT_TYPE = 'TABLE'       AND owner = 'STUDENT';  d_record d_cursor%ROWTYPE;begin  OPEN d_cursor;  LOOP    FETCH d_cursor      INTO d_record;    EXIT WHEN d_cursor%NOTFOUND;    execute immediate 'DROP TABLE ' || d_record.object_name || ' cascade constraints';    Dbms_Output.put_line(d_record.object_name || ' 表删除成功');  end loop;  close d_cursor;  commit;end;/行 2 出错ORA-00942: 表或视图不存在ORA-06512: 在 line 19脚本已在第 2行终止。（搞了很久才清楚，在sys或system账号会话连接中，只要添加 execute im...

下载备份脚本文件windows: 下载http://downinfo.myhostadmin.net/vps/bkupmysql.bat 保存到d盘根目录linux:  wget  http://downinfo.myhostadmin.net/vps/bkupmysql.sh 保存到/root目录3.编辑脚本文件windows:用记事本打开bkupmysql.bat     set dbuser=root                        #设置数据库用户,一般不需要修改set dbpass=passwd                      #设置数据库超管密码,需要修改为实际的密码set DaysAgo=7                          #设置保留天数set mysqlpath=d:\mysqlbak              #设置备份文件保存路径,一般不需要修改set mysql=D:\SOFT_PHP_PACKAGE\mysql\bin\mysql.exe   #设置备份文件保存路径,5.6版本需要修改路径set mysqldump=D:\SOFT_PHP_PACKAGE\mysql\bin\mysqldump.exe #设置mysql执行文件路径,5.6版本需要修改路径set logfile=d:\bkinfo.log     &n...

Redis 安全我们可以通过 redis 的配置文件设置密码参数，这样客户端连接到 redis 服务就需要密码验证，这样可以让你的 redis 服务更安全。实例我们可以通过以下命令查看是否设置了密码验证：127.0.0.1:6379> CONFIG get requirepass 1) "requirepass" 2) ""默认情况下 requirepass 参数是空的，这就意味着你无需通过密码验证就可以连接到 redis 服务。你可以通过以下命令来修改该参数：127.0.0.1:6379> CONFIG set requirepass "runoob" OK 127.0.0.1:6379> CONFIG get requirepass 1) "requirepass" 2) "runoob"设置密码后，客户端连接 redis 服务就需要密码验证，否则无法执行命令。语法AUTH 命令基本语法格式如下：127.0.0.1:6379> AUTH password实例127.0.0.1:6379> AUTH "runoob" OK 127.0.0.1:6379> SET mykey "Test value" OK 127.0.0.1:6379> GET mykey "Test value"

Redis 数据备份与恢复Redis SAVE 命令用于创建当前数据库的备份。语法redis Save 命令基本语法如下：redis 127.0.0.1:6379> SAVE 实例redis 127.0.0.1:6379> SAVE OK该命令将在 redis 安装目录中创建dump.rdb文件。恢复数据如果需要恢复数据，只需将备份文件 (dump.rdb) 移动到 redis 安装目录并启动服务即可。获取 redis 目录可以使用 CONFIG 命令，如下所示： redis 127.0.0.1:6379> CONFIG GET dir 1) "dir" 2) "/usr/local/redis/bin" 以上命令 CONFIG GET dir 输出的 redis 安装目录为 /usr/local/redis/bin。Bgsave创建 redis 备份文件也可以使用命令 BGSAVE，该命令在后台执行。实例127.0.0.1:6379> BGSAVE Background saving started

How To Shrink A Temporary Tablespace in Oracle DatabaseBy adminQuestion: How to resize the TEMPFILE(s) for a temporary tablespace after they have grown larger than needed?Large sort operations can cause temporary tablespaces to grow very large and as such there may be a need to ‘downsize’ after such operations. Until Oracle 11g there was no SQL command to release the unused allocated temporary space. One workaround for this problem is to create a new empty temporary tablespace with a smaller size, assign this new tablespace to the users and then drop the old tablespace. The disadvantage of this procedure is that it requires that no active sort operations are happening within the old temporary tablespace while it is being dropped.In Oracle 11g, a new feature was added that can be used to shrink temporary tablespaces. This command can be used to shrink only locally managed temporary tablespaces :ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];The&nbs...

What is MySQL Query CacheMySQL server features Query Cache Feature for a long time. When in use, the query cache stores the text of a SELECT query together with the corresponding result that is sent to a client. If another identical query is received, the server can then retrieve the results from the query cache rather than parsing and executing the same query again.It caches the full result set produced from a SELECT query:Queries executed must be identical.Cache is stored in system memory.Cache is checked before the query is parsed.Associated table updates invalidate query cache results. Prepared statements can be cached, but limitations exist. Some statements that do not use the query cache are:Queries that use nondeterministic functions.Queries that are a subquery of an outer queryQueries that are executed within the body of a stored function, trigger, or eventMySQL Query Cache SettingsThe query cache adds a few MySQL system variables for mysqld which may be set in a configuration...

Windows下Oracle因主机名或IP变动，导致EM无法启动的问题。错误信息：WIN的事件查看器：An error occured while trying to initialize the service.手工启动：emctl start dbconsoleEnvironment variable ORACLE_SID not defined. Please define it.当执行set OARCLE_SID=orcl,继续报错Unable to determine local host from URL REPOSITORY_URL= http://localhost:%EM_UPLOAD_PORT%/em/upload/注意以下地方：1: 主机名：C:/WINDOWS/system32/drivers/etc/hosts添加IP与主机名信息。Windows系统中打开 数据库配置助手，配置数据库选件，根据提示配置账号与密码，会重新生成dbcosole相关数据。完成后，最好重启下系统，看下自己的监听配置。2: EM链接JDBC：$ORACLE_HOME/oc4j/j2eeOC4J_DBConsole_fox-kook_kookOC4J_DBConsole_localhost_kook (从fox-kook主机名复制这个目录为localhost)3: 监听修改为主机名的连接方式，不要以IP方式。$ORACLE_HOME/NETWORK/ADMIN/listener.oraSID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (GLOBAL_DBNAME = kook.glfsoft.com)     ...

oracle 12C（12.1.0.2） 自动化静默安装脚本脚本使用安装前配置点击打开链接需要使用root用户执行(尽量安装纯净的OS环境) 下载脚本：https://github.com/domdanrtsey/Oracle12c_autoinstall.git点击打开链接下载-安装脚本安装前请将Oracle 12C安装包（linuxamd64_12102_database_1of2.zip、 linuxamd64_12102_database_2of2.zip ）放置在 /opt/ 目录下（脚本提示是/opt,实际可随意存放）系统需要具备512MB的swap交换分区OS可连通互联网(如果不通外网，可以使用如下方法，将依赖包下载下来，再上传到目标服务器安装，以解决依赖问题)安装插件 #　yum -y install yum-plugin-downloadonly 创建目录 # mkdir /root/mypackages/ 下载依赖 # yum install --downloadonly --downloaddir=/root/mypackages/ yum install -y binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33.i686 glibc glibc.i686 \ glibc-devel glibc-devel.i686 ksh libaio libaio.i686 libaio-devel libaio-devel.i686 libX11 libX11.i686 \ libXau libXau.i686 libXi libXi.i686 libXtst libXtst.i686 libgcc libgcc.i686 libstdc++ libstdc++.i686 \ libstdc++-devel libstdc++...

使用sqldeveloper将MySQL5迁移到Oracle11g一、环境和需求1、环境  mysql Server version: 5.7 oracle version：oracle 11g r22、需求       把MYSQL库中的表数据迁移到ORACLE中,Mysql的备文件是sql文件。二、mysql数据恢复 1，新建一个mysql数据库aemp，我这里就直接使用root账号,将sql文件导入到对应的aemp库中。     我这里使用phpstudy建了一个mysql数据库,相当于恢复好aemp库数据。2，在oracle数据库中建好表空间aemp与账号aemp,给添加上RESOUCE，CONNECT，DBA角色。用来将mysql数据迁移到aemp用户下,建用户与角色信息如下：CREATE USER AEMP IDENTIFIED BY "123456" DEFAULT TABLESPACE AEMP TEMPORARY TABLESPACE TEMP PROFILE "DEFAULT" QUOTA UNLIMITED ON AEMP;GRANT "CONNECT" TO AEMP;GRANT "RESOURCE" TO AEMP;GRANT "DBA" TO AEMP;ALTER USER AEMP DEFAULT ROLE "CONNECT", "RESOURCE";再建个oracle账号mysql,用来创建档案资料库，当然可以使用上面的aemp账号，只是资料都在一起了。CREATE...