Bash Script

Bash script create new file

Leave a comment

In this tutorial, I use a bash script to create a new file. Trick and tips create folder and file with arguments in the bash script. Bash script the essential for DevOps Roles.

Bash script create new file

If “no such folder” then create folder and file.

My bash script

#!/bin/bash
function create-touch() {
  local FILE
  local FOLDER
  FILE=${@:$#}
  FOLDER="$(dirname "$FILE")"
  mkdir -p "$FOLDER" && \touch "$@"
}
create-touch $1 # Call Function in bash script

The screen output terminal:

Conclusion

Thought the article, you can use a Bash script to create a new file as above. I hope will this your helpful. More details refer to Bash script.

Ansible

Ansible read remote file

Leave a comment

In this tutorial, How to use Ansible to read a remote file? Ansible the essential for DevOps Roles.

Ansible read remote file using slurp module

I use the slurp module to read a remote file.

- hosts: server1
  tasks:
  - name: slurp file
    slurp:
     src: /home/vagrant/devopsroles
    register: slurp_remote_file

  - name: Read file
    debug:
     msg: "{{ slurp_remote_file['content'] | b64decode }}"

The terminal output as below

Ansible shell module

Using the shell module cat command to read a remote file

- hosts: server1
  tasks:
    - name: cat file
      shell: cat /home/vagrant/devopsroles
      register: cat_content_file

    - name: echo file
      debug:
        msg: "{{ cat_content_file.stdout }}"

The terminal output as below

Ansible fetch module

To read a remote file using Ansible, you can use the fetch module. The fetch module allows you to retrieve files from remote hosts and store them locally on the Ansible control machine.

Here’s an example task to read a remote file:

- name: Read remote file
  hosts: your_host
  gather_facts: false
  tasks:
    - name: Fetch remote file
      fetch:
        src: /path/to/remote/file.txt
        dest: /path/to/local/directory/

In this example, replace your_host with the target host or group of hosts where the remote file is located. Set the src parameter to the path of the remote file you want to read. Set the dest parameter to the local directory path where you want to store the fetched file.

Conclusion

Make sure you have proper SSH access and permissions to read the remote file on the target host(s) before running this playbook.

Through the article, you can use Ansible read remote file. I hope will this your helpful. For more details refer to Ansible tutorial.

Ansible

Ansible task type of state

Leave a comment

In this tutorial, find out the ansible task type of state. In Ansible, the state attribute is used to define the desired state of a resource or module. It is commonly used in tasks that involve package management, service management, configuration file management, and more.

The state the attribute accepts different values depending on the module being used. Ansible the essential for DevOps Roles.

Ansible task type of state

skip

– The skip task and nothing. using when: false

do –> ok

– The task is successful.

do -> changed

– The status when a change occurred in the task. Using “changed_when: yes”

do -> failed -> exit

– Using “failed_when: yes”. This task returned failed and exited.

Ansible playbook task

- hosts:
    - localhost
  tasks:
    - name: skip
      debug:
        msg: skipped
      when: false

    - name: do -> ok
      debug:
        msg: task done and return ok

    - name: do -> changed
      debug:
        msg: task has done and return changed
      changed_when: yes

    - name: do -> failed -> exit
      debug:
        msg: return failed, and exit
      failed_when: yes

The terminal output as below

These are just a few examples of the different states that can be used with Ansible modules.

The available states can vary depending on the module being used. It’s important to refer to the documentation of the specific module you’re working with to understand the available states and their behaviors.

Conclusion

Through the article, you can use the Ansible task type of state. I hope will this your helpful. For more details refer to Ansible tutorial.

Database Administrator

How to Drop User Cascade Oracle: A Comprehensive Guide

Leave a comment

Introduction

Managing users and their associated objects in an Oracle database is a crucial aspect of database administration. Occasionally, a user account may become obsolete, requiring removal from the system. Oracle provides the DROP USER CASCADE command, allowing administrators to drop a user and all objects owned by that user in one step.

However, executing this command demands careful consideration due to its irreversible nature. This guide will walk you through the entire process, ensuring you understand each aspect, from prerequisites to practical examples.

Understanding the Drop User Cascade Command in Oracle

What Is the Drop User Cascade Command?

The DROP USER CASCADE command is an Oracle SQL statement that allows the removal of a user account from the database. Unlike a simple user deletion, the cascade option extends its functionality by deleting all objects owned by that user, such as tables, indexes, views, and more.

Why Use the DROP USER CASCADE Command?

There are several scenarios where using the DROP USER CASCADE command is necessary:

  1. Cleaning Up Unused Accounts: When a user account is no longer needed, and you want to remove it along with all its objects to free up resources.
  2. Managing Security: Eliminating obsolete user accounts reduces security risks by ensuring that no abandoned user objects remain in the database.
  3. Database Maintenance: Regular maintenance may involve dropping outdated or redundant users to maintain an organized and efficient database environment.

Prerequisites for Using DROP USER CASCADE

Before executing the DROP USER CASCADE command, certain conditions must be met:

  1. System Privileges: You must have the DROP USER system privilege to perform this operation. Without this privilege, the command cannot be executed.
  2. Understanding the Impact: Since the command is irreversible, thoroughly assess the impact of dropping a user and its objects. Ensure you have appropriate backups if needed.

Basic Syntax of DROP USER CASCADE

The basic syntax for the DROP USER CASCADE the command is straightforward:

DROP USER username CASCADE;

Here, username represents the name of the user you wish to drop. The CASCADE option ensures that all objects owned by the user are also removed.

How to Drop a User with the CASCADE Option

Step-by-Step Guide to Dropping a User

Step 1: Verify User Existence

Before dropping a user, ensure that the user exists in the database. You can check this using the following query:

SELECT username FROM dba_users WHERE username = 'HUUPV';

If the user exists, the query will return the username.

Step 2: Review Owned Objects

It’s crucial to review the objects owned by the user before dropping them. Use the following query to list all objects:

SELECT object_name, object_type FROM dba_objects WHERE owner = 'HUUPV';

This step helps you understand what will be deleted.

Step 3: Execute the DROP USER CASCADE Command

Once you’ve verified the user and reviewed the objects, you can execute the command:

DROP USER HUUPV CASCADE;

This command will remove the user HUUPV and all associated objects.

Handling Common Issues and Errors

What Happens if the User Owns No Objects?

If the user owns no objects, the CASCADE option will still work, but it won’t have any additional effect. The user will simply be dropped.

Can the Command Be Rolled Back?

No, the DROP USER CASCADE the command is irreversible. Once executed, the user and all owned objects are permanently deleted.

Error: ORA-01940: Cannot Drop a User That Is Currently Connected

This error occurs if the user is currently connected to the database. To resolve it, you must disconnect the user before executing the command.

ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' IMMEDIATE;

Replace SID,SERIAL# with the specific values from the user’s session.

FAQs on Dropping Users in Oracle

What does the CASCADE keyword do?

The CASCADE keyword in the DROP USER command ensures that all objects owned by the user are also dropped. This is useful for thoroughly cleaning up the database when removing a user.

Can I drop multiple users simultaneously with DROP USER CASCADE?

No, the DROP USER CASCADE command is designed to drop one user at a time. If you need to drop multiple users, you must issue separate commands for each user.

Is it possible to recover a user dropped with CASCADE?

Once a user is dropped with the CASCADE option, it is not possible to recover them or their associated objects. Ensure that dropping the user is the correct action before proceeding.

Do I need to back up the database before dropping a user?

While not mandatory, it is highly recommended to back up the database before dropping a user, especially if the user owns important objects. This provides a safety net in case of accidental data loss.

What system privilege is required to drop a user in Oracle?

The DROP USER system privilege is required to execute the DROP USER CASCADE command. Without this privilege, you will not be able to perform the operation.

Best Practices for Using DROP USER CASCADE

Always Review the Impact

Before dropping a user with the CASCADE option, carefully review the potential impact. Understand what objects will be deleted and consider whether they are still needed.

Implement Database Auditing

Consider implementing database auditing to track user activities and understand what a user has done before you drop them. This can help in assessing whether it’s safe to remove the user.

Schedule User Drops During Low-Traffic Periods

Dropping a user, especially one with many associated objects, can be resource-intensive. Schedule the operation during low-traffic periods to minimize the impact on database performance.

Document the Process

Maintain thorough documentation of the process, including why the user was dropped and what objects were affected. This is useful for future reference and accountability.

Conclusion

Dropping a user and their associated objects in Oracle is a significant operation that requires careful consideration and appropriate privileges. The DROP USER CASCADE command is a powerful tool for database administrators to manage users efficiently and maintain a clean database environment. By following the steps and best practices outlined in this guide, you can confidently perform this operation while minimizing the risk of unintended consequences.

Thank you for reading this comprehensive guide. We hope it provides the clarity and guidance you need to manage your Oracle database effectively. If you have any further questions, feel free to explore our other resources or reach out to a professional Oracle expert. Thank you for reading the DevopsRoles page!

Database Administrator

How to Oracle import dmp file: A Comprehensive Guide

Leave a comment

Introduction

In this tutorial, you will learn how to Oracle import dmp file. This process involves creating a new schema and then importing the data from the DMP file into the new schema. This guide will provide step-by-step instructions to help you accomplish this task efficiently.

Step 1: Create a New Schema

Before importing the DMP file, you need to create a new schema where the data will be imported.

Connect to SQL*Plus

Navigate to the directory where your schema creation script is located and connect to SQL*Plus with DBA privileges:

cd /tmp
sqlplus / as sysdba

Run Schema Creation Script

Run the script to create the new schema. In this example, the script is named CRT_SCHEMA_newhuupv:

SQL> @/home/oracle11g/HuuPV2/CRT_SCHEMA_newhuupv

Exit SQL*Plus

After running the script, exit SQL*Plus:

SQL> exit;

Example of Schema Creation Script

Here’s an example of what your schema creation script (CRT_SCHEMA_newhuupv.sql) might look like:

CREATE USER newhuupv IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO newhuupv;
ALTER USER newhuupv DEFAULT TABLESPACE users;
ALTER USER newhuupv TEMPORARY TABLESPACE temp;

Step 2: Import the DMP File

With the new schema created, you can now import the data from the DMP file.

Set NLS_LANG Environment Variable

Set the NLS_LANG environment variable to ensure proper character set handling:

export NLS_LANG=American_America.UTF8

Run the Import Command

Use the imp utility to import the data from the DMP file. Replace oldhuupv with the source schema name and newhuupv with the target schema name:

imp userid=oldhuupv/oldhuupv@DevopsRoles fromuser=oldhuupv touser=newhuupv BUFFER=100000 file=./exp_ora.dmp log=./exp_ora.log

Explanation of Parameters

  • userid: Specifies the user credentials for the import process.
  • fromuser: Specifies the source schema from which data is exported.
  • touser: Specifies the target schema to which data will be imported.
  • BUFFER: Sets the buffer size for the import.
  • file: Specifies the path to the DMP file.
  • log: Specifies the path to the log file where the import process will be logged.

Note

  • File dump: exp_ora.dmp
  • Service Name: DevopsRoles
  • To schema: newhuupv

Conclusion

In this tutorial, we covered the steps to create a new schema and import a DMP file into Oracle. By following these instructions, you should be able to efficiently import data into a new schema. For more Oracle tutorials and tips, continue following our page!

Jenkins

Jenkins how to start service Postfix mail server

Leave a comment

In this tutorial, I using Jenkins how to start/stop/ status service Postfix mail server. Jenkins the essential for DevOps Roles. Now, let’s go use jenkins start service Postfix mail server.

Note:  To start or stop service Postfix then account Jenkins setting in sudoers option “NOPASSWD” as the picture below

How to start service Postfix mail server use Jenkins

Create job “start_stop_postfix” “Freestyle project” as picture below

In General tab, Select “This project is parameterized” with “Choice Parameter” as the picture below

In Build tab, you select  “Execute shell” to written bash script “start/stop/status postfix” as the picture below

My code bash script

set +e
f_postfix() {
  sudo postfix $1
  echo $?
}
whoami
if [ $COMMAND == "status" ]
then
  f_postfix $COMMAND

  if [ $? -ne 0 ]
  then
    echo "Postfix not running"
  else
    echo "Postfix Running"
  fi
fi
if [ $COMMAND == "start" ]
then
  f_postfix $COMMAND
  if [ $? -ne 0 ]
  then
    echo "Postfix start Not Success"
  else
    echo "Postfix start Success"
  fi
fi
if [ $COMMAND == "stop" ]
then
  f_postfix $COMMAND
  if [ $? -ne 0 ]
  then
    echo "Postfix stop Not Success"
  else
    echo "Postfix stop Success"
  fi
fi

The finish, you click “Build with Parameters” to build job. You can select COMMAND “status or start or stop” to Build as the picture below

Conclusion

Thought the article, you can use Jenkins how to start service Postfix mail server as above. I hope will this your helpful. Thank you for reading the DevopsRoles page!

Bash Script

Bash for loop range

Leave a comment

In this tutorial, How do I use bash for loop range step N? The seq method is simple. Bash script the essential for DevOps Roles.

Bash function for loop range

f_step_n() {
for i in $(seq 2 3 10)
do 
  RESULT+=$i;
done
echo $RESULT;
}

For example

  • Numbers: ‘123456789’
  • Position: 2
  • Step: 4
  • Result: 37

My Bash for loop range step N

#!/bin/bash
_NUMBERS='123456789'
#Length of NUMBERS
_LENG=${#_NUMBERS}
#Substring (position, length)
_POSIT=${_NUMBERS:$1:1}
_NUMBER=$2
f_step_n() {
local P=$1;
local N=$2;
local L=$3;
for i in $(seq $P $N $L)
do 
  RESULT+=$i;
done
echo $RESULT;
}
echo -e "Numbers: $_NUMBERS\t Length: $_LENG\t Position: $_POSIT\t Step: $_NUMBER";
f_step_n ${_POSIT} ${_NUMBER} ${_LENG}

The screen output terminal:

Conclusion

Thought the article, you can use Bash for loop range as above. I hope will this your helpful. More details refer to Bash script.

Vagrant

Vagrant create multiple VMs loop

1 Comment

In this tutorial, How do I use Vagrant create multiple VMs loop? Multiple Vagrant Virtual Machine on Vagrantfile. How to create Multiple VMs with multiple IP loop. Vagrant the essential for DevOps Roles.

Vagrant create multiple VMs loops

For example, I creating two servers with Vagrantfile as below

Vagrant.configure("2") do |config|
config.ssh.insert_key = false
config.vm.provider :virtualbox do |vb|
  vb.memory = 256
  vb.cpus = 1
end
# Application server 1.
config.vm.define "app1" do |app1|
  app1.vm.hostname = "app1.dev"
  app1.vm.box = "centos/7"
  app1.vm.network :private_network, ip: "192.168.3.4"
end
# Database server.
config.vm.define "db" do |db|
  db.vm.hostname = "db.dev"
  db.vm.box = "centos/7"
  db.vm.network :private_network, ip: "192.168.3.5"
end
end

Now, How to Vagrant create N Virtual Machine with different IPs in a loop.

Vagrant.configure("2") do |config|

config.ssh.insert_key = false
config.vm.provider :virtualbox do |vb|
  vb.memory = 256
  vb.cpus = 1
end

#Disabling the default /vagrant share
config.vm.synced_folder ".", "/vagrant", disabled: true
MACHINE = ["app1.dev","db.dev"]
N = 1

(0..N).each do |i|
config.vm.define "server#{i}" do |node|
  node.vm.hostname = MACHINE[i]
  node.vm.box = "centos/7"
  node.vm.network :private_network, ip: "192.168.3.#{10+i}"
end
end

Note: Change N and MACHINE variable is right for you

To run the first vagrant up a Virtual Machine, to starts vagrant environment

[huupv@huupv example01]$ vagrant up

The result, Vagrant create multiple VMs loops

Conclusion

Thought the article, you can use Vagrant create multiple VMs loops as above. I hope will this your helpful. Thank you for reading the DevopsRoles page!

Ansible

Vagrant Ansible example 01

Leave a comment

In this tutorial. You can Build infrastructure with Vagrant Ansible for testing. How to set up a local server using Vagrant Ansible.

Ansible the essential for DevOps Roles. Now, let’s go to Vagrant Ansible example 01.

My local server

[huupv@huupv my_ansible]$ cat /etc/redhat-release 
Fedora release 28 (Twenty Eight)
[huupv@huupv my_ansible]$ vagrant --version
Vagrant 2.0.2
[huupv@huupv my_ansible]$ ansible --version
ansible 2.6.4
config file = /etc/ansible/ansible.cfg
configured module search path = [u'/home/huupv/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python2.7/site-packages/ansible
executable location = /usr/bin/ansible
python version = 2.7.15 (default, May 16 2018, 17:50:09) [GCC 8.1.1 20180502 (Red Hat 8.1.1-1)]

Vagrant Ansible example

Step 1: You have to install Vagrant for your OS. If you have not to install Vagrant then ref to install Vagrant on Centos and install Vagrant on Ubuntu.

Step 2: Creating a folder and file your project.

example01/
├── ansible.cfg
├── playbooks
│   ├── inventory
│   │   └── hosts
│   ├── ntp.yml
│   └── templates
│   └── ntp.conf.j2
└── Vagrantfile

3 directories, 5 files

Step 3: Build infrastructure with Vagrant for testing. Define two servers: an app server and a database server in the Vagrantfiles file.

Vagrant init creates Vagrantfiles minimal file

[huupv@huupv example01]$ vagrant init --minimal

The terminal output as below

==> vagrant: A new version of Vagrant is available: 2.1.5!
==> vagrant: To upgrade visit: https://www.vagrantup.com/downloads.html

A `Vagrantfile` has been placed in this directory. You are now
ready to `vagrant up` your first virtual environment! Please read
the comments in the Vagrantfile as well as documentation on
`vagrantup.com` for more information on using Vagrant.

The content in Vagrantfile for the app server and DB server as below

[huupv@huupv example01]$ cat Vagrantfile

The terminal output as below

Vagrant.configure("2") do |config|
config.ssh.insert_key = false
config.vm.provider :virtualbox do |vb|
   vb.customize ["modifyvm", :id, "--memory", "256"]
end
# Application server 1.
config.vm.define "app1" do |app1|
  app1.vm.hostname = "app1.dev"
  app1.vm.box = "centos/7"
  app1.vm.network :private_network, ip: "192.168.3.4"
end
# Database server.
config.vm.define "db" do |db|
  db.vm.hostname = "db.dev"
  db.vm.box = "centos/7"
  db.vm.network :private_network, ip: "192.168.3.5"
end

config.vm.provision "ansible" do |ansible|
  ansible.playbook = "playbooks/ntp.yml"
  ansible.verbose  = true
  ansible.limit = "all" # or only "nodes" group, etc.
  # Run commands as root.
  ansible.sudo = true
end
end

Ansible playbook for NTP server

Creating an inventory file for multiple servers

# Application servers

[app]

192.168.3.4 # Database server

[db]

192.168.3.5 # Group ‘multi’ with all servers

[multi:children]

app db # Variables that will be applied to all servers

[multi:vars]

ansible_ssh_user=vagrant ansible_ssh_private_key_file=~/.vagrant.d/insecure_private_key

Create a ntp.yml file in the folder Playbooks

- hosts: all
  tasks:
  - name: Ensure NTP (for time synchronization) is installed.
    yum: name=ntp state=installed
  - name: Configure NTP
    template: src=ntp.conf.j2 dest=/etc/ntp.conf
    notify:
           - restart ntpd
  - name: Ensure NTP is running.
    service: name=ntpd state=started enabled=yes

Create ntp.conf.j2 file template

statistics loopstats peerstats clockstats
filegen loopstats file loopsstats type day enable
filegen peerstats file peerstats type day enable
filegen clockstats file clockstats type day enable

#server your_NTP_server_IP
server 0.rhel.pool.ntp.org iburst
server 1.rhel.pool.ntp.org iburst
server 2.rhel.pool.ntp.org iburst
server 3.rhel.pool.ntp.org iburst

restrict -4 default kod notrap nomodify nopeer noquery
restrict -6 default kod notrap nomodify nopeer noquery
restrict 127.0.0.1
restrict ::1

Vagrant run ansible local

Running vagrant up the first time. Vagrant automatically provisions the newly-minted VM. Run “vagrant provision” again after the VM has been created.

The first run vagrant up

[huupv@huupv example01]$ vagrant up

The terminal output as below

Run vagrant provision again.

[huupv@huupv example01]$ vagrant provision

The terminal output as below

The result Vagrant Ansible example, The terminal output as below

[huupv@huupv example01]$ vagrant ssh app1
Last login: Sat Sep 15 06:26:42 2018 from 10.0.2.2
[vagrant@app1 ~]$ cat /etc/ntp.conf 
statistics loopstats peerstats clockstats
filegen loopstats file loopsstats type day enable
filegen peerstats file peerstats type day enable
filegen clockstats file clockstats type day enable

#server your_NTP_server_IP
server 0.rhel.pool.ntp.org iburst
server 1.rhel.pool.ntp.org iburst
server 2.rhel.pool.ntp.org iburst
server 3.rhel.pool.ntp.org iburst

restrict -4 default kod notrap nomodify nopeer noquery
restrict -6 default kod notrap nomodify nopeer noquery
restrict 127.0.0.1
restrict ::1
[vagrant@app1 ~]$ exit
logout
Connection to 127.0.0.1 closed.
[huupv@huupv example01]$ vagrant ssh db
Last login: Sat Sep 15 06:26:42 2018 from 10.0.2.2
[vagrant@db ~]$ cat /etc/ntp.conf 
statistics loopstats peerstats clockstats
filegen loopstats file loopsstats type day enable
filegen peerstats file peerstats type day enable
filegen clockstats file clockstats type day enable

#server your_NTP_server_IP
server 0.rhel.pool.ntp.org iburst
server 1.rhel.pool.ntp.org iburst
server 2.rhel.pool.ntp.org iburst
server 3.rhel.pool.ntp.org iburst

restrict -4 default kod notrap nomodify nopeer noquery
restrict -6 default kod notrap nomodify nopeer noquery
restrict 127.0.0.1
restrict ::1
[vagrant@db ~]$ exit
logout
Connection to 127.0.0.1 closed.
[huupv@huupv example01]$

Conclusion

Through the article, you can use Vagrant Ansible example of best practice. I hope will this your helpful. For more details refer to Ansible tutorial.

Database Administrator

How to Oracle create tablespace: A Comprehensive Guide

Leave a comment

Introduction

In this tutorial, you will learn how to Oracle create tablespace, including creating permanent and temporary tablespaces, as well as creating multiple datafiles with a single tablespace creation command. Understanding how to manage tablespaces is crucial for effective database administration.

Creating a Permanent Tablespace

Permanent tablespaces store user data and schema objects. Here’s how to create a bigfile permanent tablespace:

SQL Command

CREATE BIGFILE TABLESPACE USERDATA 
LOGGING 
DATAFILE '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/USERDATA.dbf' 
SIZE 2048MB 
EXTENT MANAGEMENT LOCAL 
SEGMENT SPACE MANAGEMENT AUTO;

Explanation

  • USERDATA: Name of the tablespace.
  • LOGGING: Enables logging for the tablespace.
  • DATAFILE: Specifies the location and name of the datafile.
  • SIZE 2048MB: Sets the size of the datafile.
  • EXTENT MANAGEMENT LOCAL: Manages extents locally.
  • SEGMENT SPACE MANAGEMENT AUTO: Automatically manages segment space.

Datafile Details

  • Named datafile: USERDATA
  • Size: 2048MB
  • Location: /mnt_nfs/oradata/oracle11g/DEVOPSROLES/USERDATA.dbf

Creating a Temporary Tablespace

Temporary tablespaces are used for sorting operations and temporary data storage.

SQL Command

CREATE TEMPORARY TABLESPACE TEMP_DATA 
TEMPFILE '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/TEMP_DATA.dbf' 
SIZE 32M 
AUTOEXTEND ON 
NEXT 32M 
MAXSIZE 1048M 
EXTENT MANAGEMENT LOCAL;

Explanation

  • TEMP_DATA: Name of the temporary tablespace.
  • TEMPFILE: Specifies the location and name of the tempfile.
  • SIZE 32M: Initial size of the tempfile.
  • AUTOEXTEND ON: Enables auto-extension of the tempfile.
  • NEXT 32M: Amount of space added on each extension.
  • MAXSIZE 1048M: Maximum size the tempfile can grow to.
  • EXTENT MANAGEMENT LOCAL: Manages extents locally.

Note

  • A temporary tablespace uses tempfiles, not datafiles.

Creating Multiple Datafiles with a Single Tablespace

You can create a tablespace with multiple datafiles in a single command.

SQL Command

CREATE TABLESPACE DATA 
DATAFILE '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/DATA_01.dbf' SIZE 4M AUTOEXTEND OFF,
          '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/DATA_02.dbf' SIZE 4M AUTOEXTEND OFF,
          '/mnt_nfs/oradata/oracle11g/DEVOPSROLES/DATA_03.dbf' SIZE 4M AUTOEXTEND OFF 
LOGGING 
EXTENT MANAGEMENT LOCAL;

Explanation

  • DATA: Name of the tablespace.
  • DATAFILE: Specifies multiple datafiles with their respective sizes.
  • AUTOEXTEND OFF: Disables auto-extension for these datafiles.
  • LOGGING: Enables logging for the tablespace.
  • EXTENT MANAGEMENT LOCAL: Manages extents locally.

Conclusion

In this tutorial, we covered how to create permanent and temporary tablespaces in Oracle, as well as how to create a tablespace with multiple datafiles. Understanding these processes is essential for effective database management and optimization. For further topics, such as “Install Oracle Database 12c on Centos 7,” keep following our page! Thank you for reading the DevopsRoles page!

Devops Tutorial

Exit mobile version