AWS Oracle RDS - increasing tablespace maxsize

Oracle RDS Maintenance 

Check table space and file sizes

select tablespace_name, file_name ,AUTOEXTENSIBLE,MAXBYTES,INCREMENT_BY from dba_data_files order by 1,2;

Check available space and space used

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;


Set autoextend table space 
The Oracle RDS in AWS uses a different command as follows,

Bigfile Type Tablespaces versus Smallfile Type

http://www.databasejournal.com/features/oracle/article.php/3646226/Bigfile-Type-Tablespaces-versus-Smallfile-Type.htm

-- checking the sizes of your datafiles to see what's using all that space
select tablespace_name, bytes from dba_data_files;
-- what's allocated by segments in each of your tablespaces?
select tablespace_name, sum(bytes) bytes
from dba_segments
group by tablespace_name order by 2;

-- how much free space you still have inside each tablespace?
select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name order by 2;

-- shrinking tablespace with lots of free space
alter tablespace XXX resize 100G;
-- addming a maxsize to tablespace for growth
alter tablespace xxxx autoextend on maxsize 100G;

Comments

Post a Comment

Popular posts from this blog

API design best practices

API Design summary 1. Use two base URLs per resource 2. Keep verbs out of your base URLs 3. Use HTTP verbs to operate on the collections and elements 4. Be consistent either use plural nouns or singular nouns 5. Use concreate names rather than abstract names     An intuitive API uses plural rather than singular nouns, and concrete rather than abstract names 6. keep your API intuitive by simplifying the associations between resources, and sweeping parameters and other complexities under the rug of the HTTP question mark. Handling errors Errors boil down to Everything worked - ok The application did something wrong – client error The API did something wrong – server error In summary, be verbose and use plain language descriptions. Add as many hints as your API team can think of about what's causing an error. eg: HTTP Status Code: 401 {"status" : "401", "message":"Authenticate","code": 20003, "...
Read more

DB Connection Issues

1. Application gives "1c86bdf0 SharedPool I J2CA0086W: Shareable connection MCWrapper id 686bbdf9 Managed connection com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl@6156bdf9 State:STATE_TRAN_WRAPPER_INUSE" message when trying to do a db update within loop which is querying a view and carrying out some operations. In IBM this problem is documented under the topic " Application hangs with prior J2CA0086W warning messages" The solutions are  1. The application must be modified to use serial reuse or 2. The connection pool can be changed to unshareable. Shareable connections are not allowed within a local transaction containment boundary. The above scenario suggests a possible problem in the application logic and how it is using the connection code. The J2CA0086 message is telling you that your application is using a shared connection in a LocalTransaction. Since the connection is enlisted in a Local Transaction, and not a Global Transaction, di...
Read more

Reading Excel Sheet on client side and processing it

Image
If you ever want to process excel sheets on the client side (on the browser) then this will be useful for you.  This uses  http://oss.sheetjs.com  coed and the source files are in ( https://github.com/SheetJS/js-xls ,  https://github.com/SheetJS/js-xlsx ) it is licensed under Apache Licence, Version 2.0 (refer attached licence in the libraries in github) Steps to Follow, JS Files required: Copy the code for following files and make corresponding files locally, or download them from github,                 https://raw.github.com/SheetJS/js-xlsx/master/xlsx.js                 https://raw.github.com/SheetJS/js-xls/master/xls.js                 http://stuk.github.io/jszip/ We will be taking some javascript already in oss.sheetjs.com for our example particularly on the main page of oss.sheetjs.com. Sample Excel Sheet: Our exce...
Read more