0

MySQL backup

Posted by admin on Aug 14, 2009 in Database

A good fast backup mysqldump –all-databases –single-transaction –flush-logs –quick –user=xxx –password=yyy | gzip -4 >backup.gz

 
0

Insert on a table reports: ORA-01536.

Posted by admin on Jul 3, 2009 in Database

The following query will report dependent objects which may be causing quotas to be exceeded. select /*+ rule */ distinct owner, object_type from dba_objects where OBJECT_NAME in (select distinct NAME from dba_dependencies where REFERENCED_NAME in (select table_name from dba_tables where tablespace_name = ‘ZZTSPACE’)) order by owner, object_type;

Tags:

 
0

Create standby database script

Posted by admin on Jun 22, 2009 in Database

#!/bin/sh # ora_create_standby.sh # Script to create a standby database # Howard Brinsmead # # Assumptions: # Oracle is installed on the destination server # TNS entry for the standby database # TNS entry for the myprod database on the destination server # Listener configured on the destination server # Mount points exists on the […]

 
0

Useful SQL Plus formats

Posted by admin on Jun 18, 2009 in Database

set echo off — suppress showing sql in result set set feedback off — eliminate row count message set linesize 100 — make line long enough to hold data set pagesize 0 — suppress headings and page breaks set sqlprompt ” — eliminate SQL*Plus prompt from output set trimspool on — eliminate trailing blanks set […]

Tags:

 
0

Oracle TNS less connections

Posted by admin on Jun 12, 2009 in Database

C:\>sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 – Production on Sat Sep 15 23:04:00 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> connect  scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=testsrv)(Port=1521))(CONNECT_DATA=(SID=TEST))) Connected. SQL> If you think about above connection, it is more like specifying complete address in the connect string as you have in the tnsnames.ora file. For 10g, you can use […]

Tags:

 
0

Adding an Oracle user – quick summary

Posted by admin on Jun 10, 2009 in Database

SQL> create user bob identified by password default tablespace users temporary tablespace temp User created. SQL> grant select any table to bob; Grant succeeded. SQL> grant create table to bob; Grant succeeded. SQL> grant create session to bob; Grant succeeded. SQL> grant create view to bob; Grant succeeded. SQL> grant create snapshot to bob; Grant […]

Tags:

 
0

Oracle tracing summary

Posted by admin on Jun 8, 2009 in Database

Create plan table @?/rdbms/admin/utlxplan.sql Start Trace 1. Enable trace at instance level Put the following line in init.ora. It will enable trace for all sessions and the background processes sql_trace = TRUE to disable trace: sql_trace = FALSE – or – to enable tracing without restarting database run the following command in sqlplus SQLPLUS> ALTER […]

 
0

Standby database creation summary

Posted by admin on Jun 7, 2009 in Database

On Primary rman target / configure channel device type disk format ‘/data/backup/ora_df%t_s%s_s%p’; backup database include current controlfile for standby; sql “alter system archive log current”; backup filesperset 10 archivelog all delete input; exit; scp -Cp /data/backup/ora* <standby>:/data/backup On Standby (Fix init.ora if necessary) sqlplus “/ as sysdba” startup nomount !lsnrctl start exit; On Primary rman […]

 
0

ssh port fowarding for Toad or MySQL client

Posted by admin on Jun 7, 2009 in Database, Unix

10.1.16.8 is MySQL server 10.1.5.234 is an intermediate host Setup a tunnel bash-3.2$ ssh  -L3306:10.1.16.8:3306 howardb@10.1.5.234 Connect Toad to localhost instead of 10.1.16.8 using port 3306 and connection is made. Intermediate host has “AllowTcpForwarding yes” in /etc/ssh/sshd_config Restart server after modifying sshd_config

 
1

Send email from MySQL

Posted by admin on Jun 7, 2009 in Database

Dump the mail message into the pickup directory and it should be sent. select “To: email@address.somewhere”,”From: triggers@mysql”,”Subject: Trigger”,””,”Hello World” into outfile “/inetpub/mailroot/pickup/mail.eml” fields terminated by ‘\r\n’; DELIMITER | CREATE TRIGGER sometable_after_update AFTER UPDATE ON some_table FOR EACH ROW BEGIN SELECT  concat(“To: “,NEW.mail), “From: triggers@mysql”, concat(“Subject: “,NEW.subject), “”, NEW.body INTO OUTFILE “/inetpub/mailroot/pickup/mail.eml” FIELDS TERMINATED by ‘\r\n’; […]

Copyright © 2008-2017 Brinsmead Data Services All rights reserved.