Tag Archives: move

Oracle, Oracle 10g, oracle 9i, SQL

Create Oracle Public Synonyms

Leave a comment

Oracle uses synonyms to point users to other schema objects when the user does not have direct access to that schema. This is generally accomplished by creating a synonym owned by public to a private schema object. Once the public synonym is created all users have access to the private schema object. In this article we will recreate the DDL for public synonyms referencing one private schema’s objects.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 15 02:57:41 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create public synonyms for all of the user SCOTT’s private tables.

Select ‘create public synonym ‘||table_name||’ for scott.’
||table_name||’;’ from dba_tables where owner=’SCOTT’;

SQL> Select ‘create public synonym ‘||table_name||’ for scott.’
2 ||table_name||’;’ from dba_tables where owner=’SCOTT’;

create public synonym DEPT for scott.DEPT;
create public synonym EMP for scott.EMP;
create public synonym BONUS for scott.BONUS;
create public synonym SALGRADE for scott.SALGRADE;

SQL> create public synonym DEPT for scott.DEPT;
Synonym created.

SQL> create public synonym EMP for scott.EMP;
Synonym created.

SQL> create public synonym BONUS for scott.BONUS;
Synonym created.

SQL> create public synonym SALGRADE for scott.SALGRADE;
Synonym created.

SQL>

3. Execute the following SQL statement to regenerate the DDL for public synonyms referring to private tables owned by the user SCOTT.

select ‘create public synonym ‘||synonym_name||’ for ‘||table_owner||’.’||table_name||’;’
from dba_synonyms where table_owner=’SCOTT’;

SQL> select ‘create public synonym ‘||synonym_name||’ for ‘||table_owner||’.’||table_name||’;’
2 from dba_synonyms where table_owner=’SCOTT’;

create public synonym DEPT for SCOTT.DEPT;
create public synonym EMP for SCOTT.EMP;
create public synonym BONUS for SCOTT.BONUS;
create public synonym SALGRADE for SCOTT.SALGRADE;

SQL>

4. This DDL can now be used to rebuild the public synonyms of a give schema or move the synonyms to another database.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Oracle Temporary Tablespace Movement

Leave a comment

Temporary tablespace’s in the Oracle environment are used for sorting and other non-permanent data operations. Although the space in a temporary tablespace is not permanent, a database of any size cannot properly function without the use of very large amounts of temporary tablespace. This procedure covers the movement of a temporary tablespace after space on the original storage device becomes insufficient. We will cover the movement of the TEMPORARY tablespace from the mount point data1 to mount point data3.

1. Find all tablespaces defined as TEMPORARY

SQL> select tablespace_name from dba_tablespaces where contents=’TEMPORARY’;

TABLESPACE_NAME
——————————
TEMP
TEMPORARY

SQL>

2. Locate the temp_files in the temporary tablespace.

SQL> select file_name from dba_temp_files where tablespace_name = ‘TEMPORARY’;

FILE_NAME
/data1/temporary01.dbf
/data1/temporary02.dbf
/data1/temporary03.dbf

SQL>

3. Find size of current files

SQL> select file_name, bytes/1024/1024 from dba_temp_files where tablespace_name = ‘TEMPORARY’;
FILE_NAME BYTES/1024/1024
—————— —————
/data1/temporary01.dbf 4096
/data1/temporary02.dbf 2048
/data1/temporary03.dbf 2048

SQL>

4. Add new temp files on your new mount point to the tablespace temporary with the command ‘alter tablespace’.

SQL> alter tablespace temporary add tempfile ‘/data3/temporary01.dbf’
size 4096m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary02.dbf’
size 2048m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary03.dbf’
size 2048m;
Tablespace altered.

5. Shutdown database to free all in-use segments of the temporary tablespace TEMPORARY with the command ‘shutdown immediate’.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

6. Startup the database in restricted mode using the command ‘startup restict’ so no user activity can pickup segments from the tablespace TEMPORARY.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 159386500 bytes
Database Buffers 444596224 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

7. Drop the old datafiles using the command ‘alter database’

SQL> alter database tempfile ‘/data1/temprary01.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary02.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary03.dbf’
drop including datafiles;
Database altered.

8. View the new temp files with the following command.

SQL> select file_name from dba_temp_files where tablespace_name=’TEMPORARY’;
FILE_NAME
———————–
/data3/temprary01.dbf
/data3/temprary02.dbf
/data3/temprary03.dbf

SQL>

9. Open the database for normal use with ‘alter system’ command.

SQL> alter system disable restricted session;

System altered.

SQL>

This completes the movement of temporary tablespaces tempfiles.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Movement of Oracle datafiles

Leave a comment

Oracle allows for the movement of datafiles from one storage device to another when you exhaust space on the original device without having to recreate your database. In this procedure we will move the datafiles located on LUN data4 to a new LUN on data1.

1. Find all datafiles located on mount point /data4 and view size in MB.

SQL> select tablespace_name, bytes/1024/1024, file_name from dba_data_files where
file_name like ‘%data3%’;

SYSTEM /data4/oradata/system01.dbf
DRSYS /data4/oradata/drsys01.dbf
TOOLS /data4/oradata/tools01.dbf
OEM /data4/oradata/oem01.dbf
USERDATA /data4/oradata/userdata_01.dbf
USERDATA /data4/oradata/userdata_02.dbf
USERDATA /data4/oradata/userdata_03.dbf
USERDATA /data4/oradata/userdata_04.dbf

SQL> select SUM(bytes)/1024/1024 from dba_data_files
13:15:46 2 where file_name like ‘%data4%’;

SUM(BYTES)/1024/1024
——————–
32546

2. The data files in tablespace USERDATA amount to 32GB of space,
we will move these data files to the new mount point /data1.

3. Shutdown the oracle database with the command ‘shutdown immediate’.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

4. Startup the oracle database in restricted mode, this will prevent non-DBA users from accessing the database while you moving the data files. Use the command ‘startup restrict’.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

5. Take the tablespace USERDATA offline.

SQL> alter tablespace USERDATA offline;

Tablespace altered.

SQL>

6. View the status of the tablespace USERDATA to ensure that it is in an offline state.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name =
‘USERDATA’;

TABLESPACE_NAME STATUS
—————————— ———
USERDATA OFFLINE

SQL>

7. Shutdown the database with the command ‘shutdown immediate’.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

8. Move the data files of tablespace USERDATA to their new locations with OS command ‘cp’. One could use the command mv, which is normally faster, however if anything goes wrong you do not have a backup of your datafile.

cp /data4/oradata/userdata_01.dbf /data1/oradata/userdata_01.dbf
cp /data4/oradata/userdata_02.dbf /data1/oradata/userdata_02.dbf
cp /data4/oradata/userdata_03.dbf /data1/oradata/userdata_03.dbf
cp /data4/oradata/userdata_04.dbf /data1/oradata/userdata_04.dbf

9. Open the database in a nomount state with ‘startup nomount’.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
SQL>

10. Change the location of the data files moved in the control file with the command ‘alter database rename datafile’.

SQL> alter database rename datafile ‘/data4/oradata/userdata_01.dbf’ to
‘/data1/oradata/userdata_01.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_02.dbf’ to
‘/data1/oradata/userdata_02.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_03.dbf’ to
‘/data1/oradata/userdata_03.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_04.dbf’ to
‘/data1/oradata/userdata_04.dbf’;
Database alerted.

11. Mount the database with the command ‘alter database mount’.

SQL> alter database mount;

Database altered.

SQL>

12. Open the database with command ‘alter database open’.

SQL> alter database open;

Database altered.

SQL>

13. Bring the tablespaces back online.

SQL> alter tablespace USERDATA online;

Tablespace altered.

SQL>

14. Validate that the tablespace is online with the following SQL.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name
2 ‘USERDATA’;

TABLESPACE_NAME STATUS
—————————— ———
USERDATA ONLINE

SQL>

Completes movement of Oracle data files.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreate the Oracle DDL for a PROFILE

Leave a comment

Oracle uses PROFILES to limit the access and resources a user has within an Oracle database. In a properly secured Oracle environment care must be taken in defining resource limits of your users. This article covers the creation of the DDL to define an Oracle profile already established in one database and recreate it in another.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 19 13:30:13 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create the profile TEST_PROFILE to demonstrate the recreation of the DDL to generate the profile in another database.

SQL> create profile test_profile limit IDLE_TIME 15
2 CONNECT_TIME 30
3 FAILED_LOGIN_ATTEMPTS 3
4 PASSWORD_LIFE_TIME 5
5 PASSWORD_REUSE_TIME 3
6 PASSWORD_REUSE_MAX 3
7 PASSWORD_LOCK_TIME 30
8 PASSWORD_GRACE_TIME 30
9 SESSIONS_PER_USER 2;

Profile created.

SQL>

3. Execute the following PL/SQL block to recreate the DDL for your profile.

set serveroutput on
spool ./create_profile.lst

declare

v_ct number;
v_profile varchar2(30):=’TEST_PROFILE’;
begin

dbms_output.put_line(‘create profile ‘||v_profile||’ limit ‘);

for v_limit in(select resource_name, limit from dba_profiles
where profile = v_profile)
loop

dbms_output.put_line(v_limit.resource_name||’ ‘||v_limit.limit);

end loop;
end;
/

SQL> set serveroutput on
SQL>
SQL> spool ./create_profile.lst
SQL>
SQL> declare
2
3 v_ct number;
4 v_profile varchar2(30):=’TEST_PROFILE’;
5 begin
6
7 dbms_output.put_line(‘create profile ‘||v_profile||’ limit ‘);
8
9 for v_limit in(select resource_name, limit from dba_profiles
10 where profile = v_profile)
11 loop
12
13 dbms_output.put_line(v_limit.resource_name||’ ‘||v_limit.limit);
14
15 end loop;
16 end;
17 /
create profile TEST_PROFILE limit
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 2
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME 15
CONNECT_TIME 30
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 5
PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX 3
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 30

PL/SQL procedure successfully completed.

SQL>

4. With the DDL generated you can recreate the TEST_PROFILE in any other Oracle database environment.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com