How to rename datafiles in Oracle 11g

[Problem]  I want to rename datafile E:\ORACLE\VNPT\DATAFILE\O1_MF_EXAMPLE_CVNONK37_.DBF to E:\ORACLE\VNPT\DATAFILE\EXAMPLE.DBF [FIX] D:\Users\Administrator>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 22 09:48:56 2016 Copyright...

How to fix the Account Locked (Timed) Oracle

Step 1: Check status of Account: select username, account_status,profile from dba_users where username='APPUSER'; Step 2: Create an profile CREATE PROFILE APP_DEFAULT LIMIT COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED IDLE_TIME UNLIMITED CONNECT_TIME...

Install or upgrade Oracle APEX 5.0.3 for ORACLE DATABASE 12C on Linux

Step 1: Create tablespace create tablespace apex  datafile '/u01/app/oracle/oradata/PTUD/datafile/apex.dbf' size 100M autoextend on next 10M; Step 2: Download Oracle Apex 5.0.3.00.03 from Oracle or My Google Drive http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html or...

How to select columns with null values

Connect with your  schema and run the sql scripts SET LINESIZE 32000; SET PAGESIZE 40000; SET LONG 50000; SELECT t.table_name, t.column_name FROM user_tab_columns t WHERE t.nullable = 'Y' AND t.num_distinct = 0 AND t.table_name='TABLE_NAME'; For example: SET LINESIZE...

How to connect PENTAHO with Oracle Database (12c/11g)

Step 1: Set JAVA environment for PENTAHO My Computer --> Properties( Click right mouse My Computer) --> Advanced system settings --> Environment Variables --> User Variables --> New PENTAHO_JAVA=C:\Program Files\Java\jdk1.7.0_79\jre\bin\java.exe PENTAHO_JAVA_HOME=C:\Program...

Example Creating tablespace in Oracle Database.

Create undo tablespace: create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ORCL/datafile/undotbs2.dbf' size 100M autoextend on next 10M; Create tablespace: create tablespace BOC datafile '/u01/app/oracle/oradata/ORCL/datafile/BOC.dbf'...

Displaying Backup Information Oracle Database

[oracle@VTGDB-Server app]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Thu Feb 25 13:56:15 2016 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select session_key,...

Clone a Pluggable Database (PDB) Manually

[oracle@VTGDB.BLOGSPOT.COM ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 2 08:34:44 2016 Copyright (c) 1982, 2014, Oracle.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With...

How to create a role and grant the role for user/schema in Oracle Database

create role "DEV" NOT IDENTIFIED;         grant CREATE TRIGGER to "DEV"; grant CREATE SEQUENCE to "DEV"; grant CREATE TABLE to "DEV"; grant CREATE TYPE to "DEV"; grant CREATE SESSION to "DEV"; grant "CONNECT" to "DEV"; grant CREATE JOB to "DEV"; grant CREATE LIBRARY to "DEV"; grant CREATE INDEXTYPE to "DEV"; grant CREATE ASSEMBLY to "DEV"; grant CREATE PROCEDURE...

SQL ORACLE select column name of a table

SET LINESIZE 32000; SET PAGESIZE 40000; SET LONG 50000; SELECT  column_name FROM USER_TAB_COLUMNS WHERE table_name = 'TABLE_NAME' order by COLUMN_NA...

Backup Full Database 12c Script

Task 1: Preparing to Use the Oracle Suggested Backup Strategy To use the Oracle suggested backup strategy, ensure that:   The database is ARCHIVELOG mode The fast recovery area size is configured, or a default device for storing backups is configured. You have added a database host user to the OSBACKUPUPDBA operating system group, for operating system authentication. Task...

How to drop or truncade all partitions Oracle Database

[oracle@PTUD-VM02-2 oradata]$ more drop_partition_table.sql  SET LINESIZE 32000;SET PAGESIZE 40000;SET LONG 50000; select 'ALTER TABLE '||TABLE_OWNER||'.'||table_name||' DROP PARTITION '||partition_name||';' from dba_tab_partitions; [oracle@PTUD-VM02-2 oradata]$ sqlplus / as sysdba @drop_partition_table.sql >> a.sql & [oracle@PTUD-VM02-2 oradata]$ cat a.sql |...

Granting select privilege for owner' all tables to others

[oracle@PTUD-S07 ~]$ cat a.sql select 'GRANT SELECT ON OCDM_SYS.'||Table_Name||' TO OCDM_REPORT;' from All_Tables where owner='OCDM_SYS'; [oracle@PTUD-S07 ~]$ sqlplus / as sysdba @a.sql >> b.sql & [3] 28402 [oracle@PTUD-S07 ~]$ cat b.sql | grep 'GRANT SELECT ON' >> c.sql [oracle@PTUD-S07 ~]$ sqlplus / as sysdba @c.sql ...

Resize your Oracle datafiles down to the minimum without ORA-03297

Read it: http://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297/ set linesize 1000 pagesize 0 feedback off trimspool on with  hwm as (   -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )   select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1)...

Find users belongs to tablespace

select distinct owner from dba_segments where tablespace_name='DATA201502'; OWNER ------------------------------ CC_OWNER CM_POS2 PAYMENT VT_IT_TC_KM PRIVILEGE_OWNER MEDIATION BCCS_GW QLKD_BD 8 rows selected. ...

TABLESPACE USAGE

select a.TABLESPACE_NAME, a.BYTES bytes_used, b.BYTES bytes_free, b.largest, round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used from ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME ) a, ( select TABLESPACE_NAME, sum(BYTES) BYTES , max(BYTES) largest from dba_free_space group by TABLESPACE_NAME ...

How to dump file ( export and import) on pluggable database

1. Check listener [oracle@PTUD-SingleSignOn ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-SEP-2015 17:57:36 Copyright (c) 1991, 2013, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS...