How to manage disk space allocation for materialized views?

Summary: I have materialized views in oracle 11g that seem to hog disk space, unlike normal tables that mark rows as deleted and stats eventually show them as free space (allocated to the table, allowing reuse). Tablespace usage only grows for materialized views unlike stats for origin tables. Tested in Oracle 12c with same results. How to ensure MV reuse space from deleted rows?

What have I done? I have these partitioned materialized views set up in a separate schema, separate tablespace from the origin tables (i know they could have partitions created dynamically, call it technical debt).

CREATE MATERIALIZED VIEW replication_schema.origin_table PARTITION BY RANGE(tbl_timestamp)  (     PARTITION tbl_before_2016 VALUES LESS THAN (TO_TIMESTAMP('2016-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),     PARTITION tbl_2016_01 VALUES LESS THAN (TO_TIMESTAMP('2016-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),     PARTITION tbl_2016_02 VALUES LESS THAN (TO_TIMESTAMP('2016-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')), ...  PARTITION tbl_after_2025 VALUES LESS THAN (MAXVALUE) ) REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT sysdate+1/1440 AS SELECT * FROM origin_schema.table; 

And they have some indexes on them as well, some global and some are local.

CREATE INDEX tbl_account_index ON replication_schema.origin_table (tbl_account DESC) LOCAL; CREATE INDEX tbl_column1_index ON replication_schema.origin_table (tbl_column1 DESC) LOCAL; CREATE INDEX tbl_column2_index ON replication_schema.origin_table (tbl_column2 DESC) LOCAL; CREATE INDEX tbl_column3_index ON replication_schema.origin_table (tbl_column3 DESC); CREATE INDEX tbl_column4_index ON replication_schema.origin_table (tbl_column4 DESC); 

Most of the time they get new rows (about 4M/mo) but users have set up a process to delete old rows from the origin table every two weeks. They can delete up to 500K/1M rows from each replicated table, every time.

There are seven materialized views in this schema. Each one extract data from one origin table.

What we see is that, contrary to what happens with the origin table, the space reported as free in dba_ tables does not change over time and tablespace usage only grows from these materialized views.

If I wait a while after deleting rows and run this query:

select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files  group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments  group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name and df.totalspace <>0 ; 

It shows an increase in the Free MB column (space in dba_data_files minus allocation declared in dba_segment) for origin tablespace but the used MB for replication never decrease, only increase on new rows (over three years now)

Tablespace      Used MB    Free MB  Total MB   Pct. Free SYSTEM          491        9        500        2 SYSAUX          1628       162      1790       9 UNDOTBS1        0          9645     9645       100 ORIGIN_DATA     2705       1391     4096       34 ORIGIN_REP_DATA **1975**   2121     4096       52 

That tablespace only holds these materialized views. There’s no other object there being used.

I tried the advisor to see what can I do:

variable id number; begin   declare   name varchar2(100);   descr varchar2(500);   obj_id number;   begin   name:='REPCHECK';   descr:='Replication advisory';    dbms_advisor.create_task (     advisor_name     => 'Segment Advisor',     task_id          => :id,     task_name        => name,     task_desc        => descr);    dbms_advisor.create_object (     task_name        => name,     object_type      => 'TABLE',     attr1            => 'REPLICATION_SCHEMA',     attr2            => 'ORIGIN_TABLE',     attr3            => NULL,     attr4            => NULL,     attr5            => NULL,     object_id        => obj_id);    dbms_advisor.set_task_parameter(     task_name        => name,     parameter        => 'recommend_all',     value            => 'TRUE');    dbms_advisor.execute_task(name);   end; end;  / 

And it says

Perform re-org on the origin_table object, estimated savings is xxx bytes

If I try querying recommendations through procedure:

select    tablespace_name,    allocated_space,    used_space reclaimable_space from    table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL')) 

It returns

ORIGIN_REP_DATA 100663296   38419844 

But I only get errors when trying to run SHRINK SPACE or COMPRESS options

ORA-10635: Invalid segment or tablespace type 10635. 00000 – “Invalid segment or tablespace type” *Cause: Cannot shrink the segment because it is not in auto segment space managed tablespace or it is not a data, index or lob segment. *Action: Check the tablespace and segment type and reissue the statement

Long story short: What can I do to avoiding disk space wasting in this materialized views? How to perform maintenance on them? Shall I drop them and recreate them? Datafiles usage in tablespace is growing about 10GB per month and I’m running out of time (and space). Thanks.

What should I do if I find a suspicious device in public space?

This question is partially inspired by this video.

In case I find a suspicious device somewhere in a public space. What should I do with the device?

  • Take the device I’ve found to my home/lab to analyze it (I’d like to)
  • Hand it to the authority responsible for the area
  • Leave it alone and report to the authority

I’d assume it’s best to hand it to the authority, but again, should I take the device to them?

**From $0.50/month Linux shared hosting with instant setup,Unlimited Web Space.

Simple, Easy, Trusted, Reliable Web Hosting Service..Just Sign up and be online within 2 minutes with our instant & free setup!
Hostpoco.com is a Budget and affordable web hosting provider.Our main aim to provide our clients with the Ultimate Shared Hosting experience possible, our servers are hosted in Premium locations offering High Quality Bandwidth, Pure SSD Storage and Solid Uptime.Our technicians are monitoring the server in the background 24/7, so you can expect that your site is never down.

If you are a beginner, don’t hesitate to try our service. We provide 30-day money back if you are not happy with our service,so you have nothing to loose when you try our service. You are absolutely safe when ordering with us…

Linux shared hosting Plan:

*For Beginner @ only $0.50 per month:
~Single Domain Hosting
~10 Email Accounts
~2 Parked Domains
~2 MySQL Databases
~10 Sub Domains
~Softacolous Supported
~Tier 1 Technical Support

*For Pro @ only $ 1 per month:
~Double Domain Hosting
~1 Addon Domains
~4 MySQL Databases
~Unlimited Sub Domains
~REE cPanel Control Panel
~REE AwStats
~FREE Virus Scanner
~Softacolous Supported
~Tier 2 Technical Support

*For Premium @ only $2.5 per month:
~Ten Domain Hosting
~Unlimited Email Accounts
~Unlimited Parked Domains
~9 Addon Domains
~Unlimited MySQL Databases
~Unlimited Sub Domains
~FREE cPanel Control Panel
~FREE Virus Scanner
~Softacolous Supported
~Tier 3 Technical Support

*For Elite @ only $ 5 per month:
~Free Domain
~Unlimited Domain Hosting
~Unlimited Web Space
~Unlimited Bandwidth
~Unlimited Email Accounts
~Unlimited Parked Domains
~Unlimited Addon Domains
~Unlimited MySQL Databases
~Unlimited Sub Domains
~FREE Virus Scanner
~Softacolous Supported
~Tier 4 Technical Support

For more Details:https://hostpoco.com/half-dollar-linux-shared-hosting.php

Thank you.

Can you do anything after moving into an occupied space?

This is inspired by and is a branching off of the question “Move into nonhostile space attack and move”


The section on “Moving Around Other Creatures” states:

[…] Whether a creature is a friend or an enemy, you can’t willingly end your move in its space […]

The is also the following unofficial ruling (tweet) from lead game designer Jeremy Crawford:

Q. Can a character move into a space occupied by their ally, make an attack from that space, and then move away?

A. You can’t willingly stop moving in another creature’s space.

Both the question mentioned at the beginning and the Crawford quote explain that you cannot move into an occupied space, make an attack, and move away; however, perhaps there are other things you could do instead? Basically, what counts as “ending your move”?

Can you do any of the following after moving into an occupied space but before leaving it?

  • Take any sort of action
  • Take any sort of bonus action
  • Take any sort of reaction
  • Do something that requires no action whatsoever, such as talking, or dropping concentration

[Hostpoco.com]*shared Hosting @ only $6/year*AUTO SSL| Unlimited Web Space| MYSQL DAT

Hostpoco is a Simple, Easy, Trusted, Reliable Web Hosting Service..Just Sign up and be online within 2 minutes with our instant & free setup!

Hostpoco deals with cheap high resourced and hugely demanded web hosting services starting from $0.5 Per month. All our hosting plans comes with cpanel as control panel included with some smart features like no limit space, unmetered bandwidth, free auto ssl, free site builder, free migration service, free dedicated IP,own email address,single click installer and much more. Get a move on!

Plan:

*shared Hosting plan for Beginner $0.5 /Monthly.

*shared Hosting plan for Pro $1 /Monthly.

*shared Hosting plan for Premium $2.5 /Monthly.

*shared Hosting plan for Elite $5 /Monthly.

For more details :https://hostpoco.com/half-dollar-linux-s…osting.php

Feature:

shared Hosting Premium plan $2.5 /Monthly:
~Ten Domain Hosting
~Unlimited Web Space
~Unlimited Bandwidth
~Unlimited Email Accounts
~Unlimited Parked Domains
~9 Addon Domains
~Unlimited MySQL Databases
~Unlimited Sub Domains
~FREE cPanel Control Panel
~FREE PHP MyAdmin
~FREE AwStats
~FREE Auto SSL
~FREE Virus Scanner
~DDOS Protection
~99.99% uptime
~Softacolous Supported
~ier 3 Technical Support

Hostpoco.com:Budget Hosting Provider | Cheap Hosting Provider | Cheap cPanel Hosting | $1 Unlimited hosting | Fast SSD Hosting | Money back Hosting | Best Seller hosting | ideal free hosting | free web hosting 

Thank you.

Rearrange items in order reduce fragmentation and reduce wasted space

I have a segment with some offsets at irregular intervals

There are items of various length inside. Items cannot be placed randomly. Instead, their left side must match some offset.

Items are free to go past offests.

As you can see in this image, item number 3 is long enough that it goes through an offset, shown with a dotted line.

I also have some special type of offset that I’ll call “barrier” that items cannot go past and cannot be placed on:

One last constraint is that items cannot overlap each other:

Items can be moved one at a time. So I can pick up an item and place it somewhere else as long as no constraint is violated.

I’m trying to come up with an algorithm/solver/optimizer which would find some good enough sequence of steps to reduce fragmentation and compact up these items. It follows that this procedure will reduce empty space between items and offsets:

Can you give some suggstion on how you would tackle a problem like this or point me in the right direction, give some ideas, name algorithms to take inspiration from, etc..?

Best non-horror RPGs for space zombies?

My players and I have been playing a DnD 5e campaign for a few months now and are already thinking about the next campaign, we want to do something involving zombies in space (both on space ships/stations and alien planets if possible) but since none of us have any experience playing before the current campaign, we don’t know which systems are best suited for that. I’d also like it to be more focused on action and maybe strategy than horror and we have a general light and humorous tone at the table that I’d like to keep. Does anyone have experience with this or is otherwise able to recommend a system or systems? The more recommendations, the better. Bonus points if there’s rules for zero G. Double bonus points if the core book(s) are available in German as one of my players isn’t that good at English and I don’t want to have to translate everything.