Home » RDBMS Server » Server Administration » Auto extend
| Auto extend [message #50857] |
Thu, 11 April 2002 05:37  |
rajesh
Messages: 173
Registered: November 1998
|
Senior Member |
|
|
hi,
The size of my exportfile (full) is 3.5 GB
Now I want to create a new database and import this data. Now I have got a doubt
If I give the size of tablespace as 100MB, autoextend on inital extend 100MB and next 100MB, will it cause any problem. If so what?? should I give the inital size to accomodate the current data.
thanks
|
|
|
|
| Re: Auto extend [message #50858 is a reply to message #50857] |
Thu, 11 April 2002 07:02  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Your FULL export contains all users in the current database. So the 3.5 GB is the size for the whole thing.
Make all the tablespaces like in the current one with all the tablespaces. If you want to change the structure with diff storage param, then this is the time to do so.
|
|
|
|
| Re: Auto extend [message #50862 is a reply to message #50857] |
Thu, 11 April 2002 10:02 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Extents should not cause you problems. You could export with COMPRESS=Y (if not using partions) and when you do the import and your objects would be in one extent. You could then sum them up to see the full size and do the import again. You can also shrink the tablespace/datafile after your are done. I am sure there are scripts that will do this for you before the export so you can see how much space you will need. Here is one that will give you accurate space usage. This doesn't sum it up but you could modify it to your needs:
accept ts_name prompt "Enter the tablespace name that you wish to check: "
set serveroutput on feedback off echo off veri off
spool unused_space.lst
declare
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_file_id number;
v_block_id number;
v_last_block number;
v_extents number;
v_next number;
v_used number;
v_owner varchar2(30);
v_segment varchar2(81);
v_partition varchar2(80);
cursor index_c is
select a.owner, a.index_name, b.extents, b.next_extent
from sys.dba_indexes a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.index_name = b.segment_name
order by a.index_name;
cursor table_c is
select a.owner, a.table_name, b.extents, b.next_extent
from sys.dba_tables a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.table_name = b.segment_name
order by a.table_name;
cursor cluster_c is
select a.owner, a.cluster_name, b.extents, b.next_extent
from sys.dba_clusters a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.cluster_name = b.segment_name
order by a.cluster_name;
cursor tab_partition_c is
select a.table_owner, a.table_name, a.partition_name, b.extents, b.next_extent
from sys.dba_tab_partitions a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.table_name = b.segment_name
and a.partition_name = b.partition_name
order by table_name;
cursor ind_partition_c is
select a.index_owner, a.index_name, a.partition_name, b.extents, b.next_extent
from sys.dba_ind_partitions a, sys.dba_segments b
where a.tablespace_name = upper('&ts_name')
and a.index_name = b.segment_name
and a.partition_name = b.partition_name
order by index_name;
begin
dbms_output.enable(6400000);
open index_c;
fetch index_c into v_owner, v_segment, v_extents, v_next;
while index_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'INDEX', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
dbms_output.put_line(CHR(10));
dbms_output.put_line('INDEX NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Total Blocks = '||v_total_blocks);
dbms_output.put_line('Total Bytes = '||v_total_bytes);
dbms_output.put_line('. KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused Blocks = '||v_unused_blocks);
dbms_output.put_line('Unused Bytes = '||v_unused_bytes);
dbms_output.put_line('. KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_blocks - v_unused_blocks;
dbms_output.put_line('Used Blocks = '||v_used);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used Bytes = '||v_used);
dbms_output.put_line('. KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Last used extents file id = '||v_file_id);
dbms_output.put_line('Last used extents block id = '||v_block_id);
dbms_output.put_line('Last used block = '||v_last_block);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch index_c into v_owner, v_segment, v_extents, v_next;
end loop;
close index_c;
open table_c;
fetch table_c into v_owner, v_segment, v_extents, v_next;
while table_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'TABLE', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line(CHR(10));
dbms_output.put_line('TABLE NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Total Blocks = '||v_total_blocks);
dbms_output.put_line('Total Bytes = '||v_total_bytes);
dbms_output.put_line('. KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused Blocks = '||v_unused_blocks);
dbms_output.put_line('Unused Bytes = '||v_unused_bytes);
dbms_output.put_line('. KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_blocks - v_unused_blocks;
dbms_output.put_line('Used Blocks = '||v_used);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used Bytes = '||v_used);
dbms_output.put_line('. KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Last used extents file id = '||v_file_id);
dbms_output.put_line('Last used extents block id = '||v_block_id);
dbms_output.put_line('Last used block = '||v_last_block);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch table_c into v_owner, v_segment, v_extents, v_next;
end loop;
close table_c;
open cluster_c;
fetch cluster_c into v_owner, v_segment, v_extents, v_next;
while cluster_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'CLUSTER', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line(CHR(10));
dbms_output.put_line('CLUSTER NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Total Blocks = '||v_total_blocks);
dbms_output.put_line('Total Bytes = '||v_total_bytes);
dbms_output.put_line('. KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused Blocks = '||v_unused_blocks);
dbms_output.put_line('Unused Bytes = '||v_unused_bytes);
dbms_output.put_line('. KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_blocks - v_unused_blocks;
dbms_output.put_line('Used Blocks = '||v_used);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used Bytes = '||v_used);
dbms_output.put_line('. KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Last used extents file id = '||v_file_id);
dbms_output.put_line('Last used extents block id = '||v_block_id);
dbms_output.put_line('Last used block = '||v_last_block);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch cluster_c into v_owner, v_segment, v_extents, v_next;
end loop;
close cluster_c;
open tab_partition_c;
fetch tab_partition_c into v_owner, v_segment, v_partition, v_extents, v_next;
while tab_partition_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'TABLE PARTITION', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block, v_partition);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line(CHR(10));
dbms_output.put_line('TABLE NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Partition Name = '||v_partition);
dbms_output.put_line('Total KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch tab_partition_c into v_owner, v_segment, v_partition, v_extents, v_next;
end loop;
close tab_partition_c;
open ind_partition_c;
fetch ind_partition_c into v_owner, v_segment, v_partition, v_extents, v_next;
while ind_partition_c%FOUND loop
dbms_space.unused_space(v_owner, v_segment, 'INDEX PARTITION', v_total_blocks, v_total_bytes,
v_unused_blocks, v_unused_bytes, v_file_id, v_block_id, v_last_block, v_partition);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line(CHR(10));
dbms_output.put_line('INDEX NAME = '||v_owner||'.'||v_segment);
dbms_output.put_line('Partition Name = '||v_partition);
dbms_output.put_line('Total KBytes = '||v_total_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_total_bytes/1024)/1024);
dbms_output.put_line('Unused KBytes = '||v_unused_bytes/1024);
dbms_output.put_line('. MBytes = '||(v_unused_bytes/1024)/1024);
v_used := v_total_bytes - v_unused_bytes;
dbms_output.put_line('Used KBytes = '||v_used/1024);
dbms_output.put_line('. MBytes = '||(v_used/1024)/1024);
dbms_output.put_line('Number of Extents = '||v_extents);
dbms_output.put_line('Next Extent (MBytes) = '||(v_next/1024)/1024);
fetch ind_partition_c into v_owner, v_segment, v_partition, v_extents, v_next;
end loop;
close ind_partition_c;
end;
/
spool off
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Sep 08 13:30:46 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
