Month: August 2017

Command Line Connection with MYSQL

by

If you are wanting to connect to a remote database using the command line with MySQL, you first need to download the MYSQL client available here Open a DOS shell and run the following: mysqlsh /sql -u aurora -p mypassword -h auroratest-1.cluster-xxxx.ap-southeast-2.rds.amazonaws.com AURORATEST Then run your query mysql-sql> select count(*) from AURORA.PERSON_AWS; +———-+ | count(*) … Read more

Lambda Function in Aurora

by

How to create a lambda function to insert some records in an Aurora Database in AWS. Here is some sample code. This will enable us to link to databases in AWS to perform some functions. import sys import logging import rds_config import pymysql #rds settings rds_host = “auroratest.xxxx.ap-southeast-2.rds.amazonaws.com” name = rds_config.db_username password = rds_config.db_password db_name … Read more

What is using TEMP

by

We need to resize temp, but trying to resize hangs. Lets see what is using TEMP SELECT tu.username, s.sid, s.serial#, S.MACHINE, s.inst_id, tu.tablespace FROM gv$tempseg_usage tu, gv$session s WHERE tu.session_addr = s.saddr; DBSNMP 1715 99 myhost 1 TEMP2 You can either wait for the sessions to complete or in my case, it was the agent … Read more

How to resize Undo

by

You can shrink the datafile of the UNDO tablespace on the primary database to 1G by using the following command: ALTER DATABASE DATAFILE ‘+DATA/datafile/undotbs01.369.942861921’ RESIZE 2048M; ASM needs the correct ASM filename. The undo datafile on the standby database will be automatically resized as well. If you get the following error: ORA-03297: file contains used … Read more

How to Create an Oracle SQL TUNING TASK manually with the SQL_ID

by

Step 1: Get the sql_id of the oracle session you would like to tune. There are a number of ways to get this, this is probably the easiest. select x.sid ,x.serial# ,x.username ,x.sql_id ,x.sql_child_number ,optimizer_mode ,hash_value ,address ,sql_text from v$sqlarea sqlarea ,v$session x where x.sql_hash_value = sqlarea.hash_value and x.sql_address = sqlarea.address and x.username is not … Read more

Kill user sessions in RAC 10g

by

Goal The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID,SERIAL#); as a local session on a local node. To kill from one node sessions across multiple nodes the following script has been created… Kill_Session_User(p_username VARCHAR2) – This takes the schema/user name and will loop through all sessions for this user in … Read more

Recover from missing UNDO Tablespace

by

Step 1. Symptoms when trying to drop a undo tablespace SQL> drop tablespace undotbs1; drop tablespace undotbs1 * ERROR at line 1: ORA-01548: active rollback segment ‘_SYSSMU1_1282527427$’ found, terminate dropping tablespace Step 2. File which rollback segments to remove SQL> SELECT segment_name, tablespace_name, status FROM sys.dba_rollback_segs; 2 SEGMENT_NAME TABLESPACE_NAME STATUS —————————— —————————— —————- SYSTEM SYSTEM … Read more