In this tutorial, How to find the latest Oracle database patchset: Patch Set Release (PSR). Sometimes you check the latest patch name.

find latest Oracle database patchset

Which oracle patch has installed your system?

The easiest check Patch Set Release (PSR）as command below

$ sudo su - oracle
$ opatch lsinventory | grep "^Oracle Client"

More verbose list on the installed Oracle patches:

$ sudo su - oracle
$ opatch lsinventory -details

Conclusion

Thought the article, You can “How to find latest Oracle database patchset” as above. I hope will this your helpful. Thank you for reading the DevopsRoles page!

In this tutorial, we will show you How to check dump file size in Oracle. Sometimes, you need to export the entire database and may not know the exact space required for the dump file. Oracle provides the expdp command to perform this export.

Check dump file size in Oracle

The query calculates how much dump file table data each schema on your databases.

SELECT owner, segment_type, SUM(bytes)/1024/1024/1024 GB
FROM dba_segments
WHERE owner IN ('huupv') AND segment_type NOT LIKE '%INDEX'
GROUP BY owner, segment_type
ORDER BY 1, 2;

Note:

• Replace [USER_NAME] with the specific username for which you want to get the dump size.
• For example, if [USER_NAME] is huupv, the query becomes:

SELECT owner, segment_type, SUM(bytes)/1024/1024/1024 GB
FROM dba_segments
WHERE owner IN ('huupv') AND segment_type NOT LIKE '%INDEX'
GROUP BY owner, segment_type
ORDER BY 1, 2;

Conclusion

Throughout the article, you can learn how to “check dump file size in Oracle” as described above. I hope you find this information helpful. Thank you for reading the DevopsRoles page!

Introduction

In this tutorial, we will address how to solve the issue of “Oracle 12c unable to access port 5500”. This is an essential skill for DevOps roles involved in Oracle database management. By following these steps, you will be able to resolve the port access issue and ensure smooth connectivity to your Oracle database.

Accessing the Oracle Database with Required Privileges

First, access the Oracle database with the necessary permissions:

sqlplus / as sysdba

Checking Port 5500 from SYSDBA Oracle

Execute the following SQL command to check the current HTTPS port configuration:

SQL> select dbms_xdb_config.gethttpsport from dual;

Terminal Output

markdownCopy codeGETHTTPSPORT
------------
5500

If the port is not set to 5500, you can set it using the following command:

Setting HTTPS Port to 5500

Execute the following PL/SQL command to set the HTTPS port to 5500:

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

Terminal Output

PL/SQL procedure successfully completed.

Starting the Listener Service

To ensure that the listener service is running, use the lsnrctl command from the command line:

[oracle@DBOracle ~]$ lsnrctl start LISTENER

Checking Port 5500 Listening in Linux

Verify that port 5500 is listening by using the netstat command:

$ netstat -nplt | grep 5500

Accessing the Oracle Database Web Interface

Now, you should be able to access the Oracle database web interface. Open your web browser and navigate to:

https://192.168.1.114:5500/em/

Conclusion

In this article, we covered how to solve the “Oracle 12c unable to access port 5500” issue. By following these steps, you should now be able to configure and access port 5500 for your Oracle database. This ensures efficient management and connectivity for your database applications. Thank you for reading the DevopsRoles page!

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!

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!