TutorialDBA - Support | Training | Consultant

Scenario: If Postgresql data partition is  running out of disk space How to solve this issue Ans: Option 1: Create a new tablespace on a separate drive and move existing tables to it. ALTER TABLE schema.table SET TABLESPACE tbs1; Or using following steps you can migrating data from old tablespace to new tablespace https://www.tutorialdba.com/2017/01/moving-database-objects-or-all-tables.html?m=1 You can also move all tables (or indexes) in one tablespace into another: postgres=# alter table all in tablespace tbs3 set tablespace pg_default; ALTER TABLE Note: Downtime is required for alter table moving. For “online” moving one can though use tools like pg_repack or pg_squeeze, Affected tables are locked while being relocated Option 2: create a new tablespace and use for future tables: PostgreSQL provides many options for setting the default location of newly created tables You can default location based on the database using: ALTER DATABASE dbname SET default_tablespace...

There are two type of tablespace management LMT and DMT. LMT: (local managed tablespace) It came from 9i. the block information(free or used) will maintain in datafile header as 0's & 1's. DMT: (dictionary managed tablespace) The extent information will maintain in data dictionary as FET$ and UET$ DIFFERENCE BETWEEN LMT AND DMT: No DMT                                                              LMT 1 2 3 4 free list maintain in data dictionary as FET$,UET$ high  I/O  process  Manual Coalesce more undo generated free list maintain in data file header as 0's & 1's   Less I/O process  Aut...

A database is divided  into One or more logical storage units called tablespace.  collection of datafiles is called as tablespace. TYPES OF TABLESPACE : Permanent  Tablespaces Undo Tablespaces Temporary Tablespaces PERMANENT TABLESPACE : (Users data files and normally contains the system (data dictionary) and users data)   System   Is  a permanent tablespace and contains the vital data dictionary (metadata about the database). It created when database is created. It always in online. It stores tables that support the core functionality of the database such as the data dictionary tables. Cannot rename or drop the system tablespace Sysaux  Its introduce in oracle 10g. Is an auxiliary tablespace to the system tablespace and contains performance statistics collected by the database.(ex:logminar,oracle streams) It cannot be dropped or renamed. Sysaux tablespace may be taken offline for performing ...

Moving datafiles of a database: The datafiles reside under /home/oracle/OraHome1/databases/ora9 and have go to /home/oracle/databases/ora9. SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files; TABLESPACE_NAME                SUBSTR(FILE_NAME,1,70) ------------------------------ ---------------------------------------------------------------------- SYSTEM                         /home/oracle/OraHome1/databases/ora9/system.dbf UNDO                           /home/oracle/OraHome1/databases/ora9/undo.dbf DATA                           /home/oracle/OraHome1/databases/ora9/data.dbf SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /home/oracle/OraHome1/databases/ora9/red...

Moving Datafiles with shutting down the database $ sqlplus "/ as sysdba" SQL> shutdown immediate SQL> !mv /u03/oradata/PROD/devl_PROD_01.dbf /u04/oradata/PROD SQL> startup mount SQL> alter database rename file '/u03/oradata/PROD/devl_PROD_01.dbf'             to '/u04/oradata/PROD/devl_PROD_01.dbf'; SQL> alter database open; SQL> select * from v$datafile Moving Datafiles without shutting down the databa $ sqlplus "/ as sysdba" SQL> alter tablespace development offline; SQL> !mv /u03/oradata/PROD/devl_PROD_01.dbf /u04/oradata/PROD SQL> alter database rename file '/u03/oradata/PROD/devl_PROD_01.dbf'             to '/u04/oradata/PROD/devl_PROD_01.dbf'; SQL> alter tablespace development online; SQL> select * from v$datafile;

Tablespace  : is logical storage unit in Oracle Database. ii) Tablespace connsit of one or more datafiles (check below) iii) Information about Tablespace can be obtained from view  DBA_TABLESPACES, USER_TABLESPACES, DBA_TEMP_FILES  (for Temporary Tablespaces) iv) Tablespace is further divided in to logical units Segments (Segment is divided in to Extent and Extent in to Block) . To know more about Segment, Extents and Blocks  click here v) Various type of tablespace are BIGFILE, SYSTEM, SYSAUX, UNDO Datafiles  : is physical structure to store oracle data ii) One or more physical datafile are logically grouped together to make a tablespace iii) Information about Datafile can be obtained from view  DBA_DATA_FILES iv) A Datafile can be associated with only one tablespace Adding Tablespace in database Use CREATE TABLESPACE command to add tablespace in Database like CREATE TABLESPACE <tablespace_name> DATAFILE <location_of_dataf...

AUTOEXTEND ON Next Size UPDATE 28-Feb-11 : Apparently, in 11gR1, Bug 8318050 affects the behaviour of Autoextend On datafiles such that the NEXT size specified may not be honoured. See Oracle Support Article#8318050.8 In a CREATE TABLESPACE command, the DATAFILE clause is the Physical Specification component. In pre- 9i/10g OMF manner, the DATAFILE must be specified. A (initial) filesize must also be specified. However, Autoextend's NEXT size is not mandatory and Oracle can "default". Very unfortunately, the default AUTOEXTEND ON NEXT size is 1 Database block (based on the blocksize of the tablespace). But if you create your Tablespace using OMF (i.e. where "db_create_file_dest" is configured), then Oracle defaults the initial size to 100MB and *also* defaults the AUTOEXTEND to ON with a of 100MB ! That is much neater. Why is the default 1 Database block bad ? Because when the datafile is full, Oracle will extend it 1 block at-a-time, making a call to the OS to ...

Creating Temporary Tablespace From Oracle 9i, we can specify a default temporary tablespace when you create a  database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement. e.g. SQL> CREATE DATABASE oracular ..... DEFAULT TEMPORARY TABLESPACE temp_ts .....; Oracle provides various ways of creating TEMPORARY tablespaces. Prior to Oracle 7.3 - CREATE TABLESPACE temp DATAFILE ...; Example: SQL> CREATE TABLESPACE TEMPTBS DATAFILE '/path/temp.dbf' SIZE 2048M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING DEFAULT NOCOMPRESS ONLINE EXTENT MANAGEMENT DICTIONARY; Oracle 7.3 & 8.0 - CREATE TABLESPACE temp DATAFILE ... TEMPORARY; Example: SQL> CREATE TABLESPACE TEMPTBS DATAFILE '/path/temp.dbf' SIZE 2048M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING DEFAULT NOCOMPRESS ONLINE TEMPORARY EXTENT MANAGEMENT DICTIONARY; Oracle 8i and above - CREATE TEMPORARY TABLESPACE temp TEMPFILE ...; Examples: SQL> CREATE TEMPORARY TABLESPACE TEMPTB...

PRE-REQUISITE: RMAN BACKUP and database in ARCHIVELOG mode. If there is a requirement to do a USERS RECOVERY test using RMAN backup. Copy loop.sql from  /opt/oracle/scripts on ITLINUXDEVBLADE07 to $SCRPT on new machine. cd $SCRPT sql SQL> create tablespace users datafile '/opt/oracle/oradata/dgtest9i/users01.dbf' size 10m; Tablespace created. SQL> create table test (col_1 number(10), col_2 date) tablespace users; Table created. SQL> @loop PL/SQL procedure successfully completed. SQL> select count(*) from test;   COUNT(*) ----------      50000 Simulate users by removing datafile belonging to USERS tablespace cd /opt/oracle/oradata/dgtest9i [oracle@itlinuxdevblade07 dgtest9i]$ ls -lrt users* -rw-r-----    1 oracle   dba      10493952 May  7 10:09 users01.dbf [oracle@itlinuxdevblade07 dgtest9i]$ rm users01.dbf [oracle@itlinuxdevblade07 dgtest9i]$cd $SCRPT SQL> @loop declare * ERROR at line 1...