Info Imply@Ravi: ORACLE DATABASE

Showing posts with label ORACLE DATABASE. Show all posts

Saturday, August 25, 2018

Sql query for calculating the size of the current database, schema and tables

The size of the database is the space the files physically consume on disk.

Below is the query to get the size:

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;

We can get the total space used with the below query:

select sum(bytes)/1024/1024 size_in_mb from dba_segments;

We can get the space used by individual schema with the below query:

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner;

SQL query to check table size in Oracle database:

SELECT segment_name, segment_type, bytes/1024/1024 MB

FROM user_segments WHERE segment_type='TABLE' AND segment_name='Your Table Name';

SQL query to get top 10 tables by size in Oracle database:

select * from (select owner, segment_name, bytes/1024/1024 MB from dba_segments where

segment_type = 'TABLE' order by bytes/1024/1024 desc) where rownum <= 10;

Exporting and importing dmp file in oracle database 12c

Exporting dmp file

Exporting dmp file of a schema using system user:

Syntax:

expdp system/Admin123@pdborcl schemas=HR directory=DPUMP_DIR dumpfile=hr.dmp logfile=expdpHR.log

Creating a directory object for oracle dump:

Syntax:

SQL> CREATE or REPLACE DIRECTORY dpump_dir as ' C:\app\BIASADMIN\oradata\orcl\pdborcl’;

To enable user hr to have access to these directory objects, you would assign the necessary privileges, for example:

SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir TO hr;

Use the following Data Pump Export command

Exporting dmp file of a schema using itself:

Syntax:

expdp hr/hr@pdborcl directory=DPUMP_DIR dumpfile=HR1.dmp logfile=HR1.log full=yes

Importing dmp file

Importing dmp file of a schema using system user:

Syntax:

impdp system/Admin123@pdborcl schemas=HR directory=DPUMP_DIR dumpfile=HR.dmp logfile=impdpHR.log

Importing dmp file of a schema using itself:

Syntax:

impdp hr/hr@pdborcl directory=DPUMP_DIR dumpfile=HR2.dmp logfile=HR2.log

Wednesday, July 25, 2018

ORA-01031 INSUFFICIENT PRIVILEGES

When trying to connect to the database as sys from command prompt getting the below error

The issue is due to the password file got corrupted

Creating a new password file will resolve the issue

Back up the password file under ORACLE_HOME/database (PWDorcl.ora)

Create new password file with the command below:

Note: set the ORACLE_HOME, ORACLE_SID and PATH variables

before running the below command

Try to connect again

Tuesday, July 3, 2018

Database crashed, unable to connect through sqlplus ; ORA-01012: not logged on ; Oracle Instance alive for sid "orcl"

When trying to start the database getting the below error:

ORA-01012: not logged on

Process ID: 0

Session ID: 0 Serial number: 0

To resolve this exit from the database first

Run SYSRESV Utility as below:

Remove damaged Semaphores and shared memory as below:

Try to connect the database now

Tuesday, June 26, 2018

PRVG-0449 : Proper soft limit for maximum stack size was not found on node

Getting the below error when installing oracle database 12c on linux machine

Soft Limit: maximum stack size - This is a prerequisite condition to test whether the soft limit for maximum stack size is set correctly.Details:

PRVG-0449 : Proper soft limit for maximum stack size was not found on node "obia" [Expected >= "10240" ; Found = "8192"]. - Cause: The Cluster Verification Utility determined that the setting for the indicated soft limit did not meet Oracle''s recommendations for proper operation on the indicated nodes. - Action: Modify the resource limits to meet the requirement and take operating system specific measures to ensure that the corrected value takes effect for the current user before retrying this check